Editing text file using vba

Learn more. How to edit a text file using excel vba Ask Question. Asked 8 years, 2 months ago. Active 7 years, 9 months ago. Viewed 13k times. For example, a record in text file looks like: ABCDNew Delhi India The mapping excel sheet for this is as below: Name 4 City 10 Country 10 Which means that the 1st four characters in record give the name, next 10 give city and next 10 give the country of a particular person and so on.

Any Ideas on how can I do this in simplest way? Improve this question. Community Bot 1 1 1 silver badge. I have tried it using Microsoft access database. I loaded the data in access DB and edited the record there. That worked fine as per the requirements but access DB consumes some Mbs of space. Do I want to make the changes directly into the file.

Add a comment. Active Oldest Votes. Improve this answer. Thanks for your help. I really appreciate your support but i have a concern here. For Output - When you are opening the text file with this command, you are wanting to create or modify the text file.

You will not be able to pull anything from the text file while opening with this mode. For Input - When you are opening the text file with this command, you are wanting to extract information from the text file. You will not be able to modify the text file while opening it with this mode. FreeFile - Is used to supply a file number that is not already in use. This is similar to referencing Workbook 1 vs.

Workbook 2. By using FreeFile, the function will automatically return the next available reference number for your text file. I found examples of storing the whole content of the text file in a variable and then writing it to a new text file and replacing the current one, but I would rather like to make those 2 small changes if possible. Local time Today, Joined Feb 28, Messages 21, What you are describing would require you to learn how to create an application object so that you could that application's facilities to do the editing.

Your most likely method would be to open the file with a WORD app object. Look up the topic in this forum using search phrases such as "Word Application Object" and "References" to see how to get started. The app object is what does the work, but the references part is what tells Access how to use the specific app object. This is not a trivial topic but it exactly meets the description of your desired procedure.

I suggested using WORD because it is the most versatile way to edit text files. Local time Today, Joined Oct 29, Messages 17, I agree. Using only VBA functionalities, I think you can only read and create text files. For editing existing text files, you may have to use VBA to control an external application, as already suggested.

Cronk Registered User. Local time Today, Joined Jul 4, Messages 2, If the code is creating the text file, I wonder why the generated file has any "errors" that need correcting. Could they be the cause? If you suspect issues are resulting from nulls, replace any nulls in your generating code with zero length strings by wrapping Nz's around your variables eg nz YourData, "".

Mode: Mode is the control we can have over opening text files. We can have three types of control over the text file. File Number: This will count the text file number of all the opened text files.

This will recognize the opened file numbers in integer values from 1 to Assigning the file number is the tricky one and leads to a lot of confusion. For this, we can use the free File function. Free File returns the unique number for the opened files. This way, we can assign the unique file number without any kind of duplicate values. To determine which file number we are referring to, declare one more variable as Integer Variable As Integer In VBA, an integer is a data type that may be assigned to any variable and used to hold integer values.

In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages.