Edit a text file with VBA

[Hello1]

Hi there,

I use some VBA code to create a text file with some data in it which is used to be opened in another program. The program checks its content and alerts for the errors in it. When I open the text file many errors show up, however when I open the text file in notepad and do some minor changes, like hitting enter and then backspace and save the file, it works normally in the program, without errors.
I was thinking to add some additional VBA code which would open the text file after it gets created and then make some changes like the ones mentioned before and save the file.
Would that be of any help, and how could I do that? 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.

Thanks

 

[The_Doc_Man]

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. I am not sure that I know of cases using NOTEPAD or WORDPAD for editing, because to use them you would need to be able to add a proper "reference library" for their respective functions.

 

[theDBguy]

Hi. 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]

If the code is creating the text file, I wonder why the generated file has any "errors" that need correcting.

 

[Hello1]

When I open the created file in Notepad++ I see some Null values. When I open it in usual Notepad and just save it without doing any changed and then open again in Notepad++ the Null values disappear. Could they be the cause?

 

[Cronk]

Maybe. 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, "")

 

[Hello1]

Alright, seems like I found the cause but need some testing still.
I had a Variable which was supposed to create some space

Dim User As String
User = String(80, Chr(0))

That were the Null values in the text file causing the error. I change it now to

User = Space(80)

Looks like it works, but have to do some more testing

 

[Cronk]

You could have also usedstring(80, chr(32)) or string(80," ")

 

[Hello1]

Is there a difference? Whats better to use?

 

[Cronk]

No difference. Both give a string of 80 blank characters, same as hitting the space bar 80 times.

 

[The_Doc_Man]

There is also a warning to consider. You can have different end-of-line delimiters depending on exactly which kind of text file you are building, and they are NOT the same for all cases.

Some utilities end a line with a null character (ASCII NUL = a byte of 0). Some utilities end a line with a line-feed (ASCII LF). Some end it with a carriage-return (ASCII CR). Some end with the CR LF combination, which in VBA is the constant vbCRLF. And some use the C/C++ standard of the ASCIC (counted ASCII) string, where the first byte is the size of the line (0 to 255 bytes) and that number of bytes that follow the header line are the line itself. There is no special EOL character. THEN if you get a text file from a source not based on Windows, you might also see the line end with the ASCII "record mark" character.

Therefore, be careful when you open some text file, because if you intend to edit it and save it, there will be a lot of confusion if you choose the wrong option.