How to handle text files using vba?

[plopband]

If open a txt file using excel, some special symbols, like "--", may be read as "#NAME?". When save it back to .txt file, the original text may be messed up with these Excel strings. Is it a good way to edit a plain text file using VBA programming?

What I need is to open a text file and make some changes by a micro, such as adding a string in a line or adding a line. Then save the modified file back to txt format. Can anybody give some tips on this?

Thanks,

 

[ettienne]

Excel mashes up text files if you open them in Excel and save as text again. Use notepad or some text editor if you want to avoid this. You can read the text file in VBA, but you cannot write to the same file. The normal steps with a text file is to open the source file for reading, then read a line into a string, make changes to the string, write the string to a second text file opened for writing, repeat for each line in the source file and close both text files when done. This is the simplest form of IO programming.

 

[MarkWalsh]

Hard to say without seeing what code you are using, but I'm guessing that you recorded a macro, which will use the current selection (which is not available in slide show mode)

Try this:

activepresentation.Slides(x).Shapes.ZOrder msoBringToFront


Replace the values x & y with the appropriate slide number and shape index.

 

[strongm]

Um - posted to wrong thread, Mark?

 

[fumei]

Yes, that one threw me. This is not - apparently - a Powerpoint question, nor anything to do with Powerpoint.

ettienne: "You can read the text file in VBA, but you cannot write to the same file."

Really? VBAS can Write to a text file it has opened,a s far as I know.

I would agree that actual text files in Excell itself is not such a great idea, because Excel does handle text well. But straight text I/O can be done through VBA, regardless of the application (Word, Excell, Powerpoint).

Try googling VBA + Freefile.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[ettienne]

You can open a text file for input or for output, but not for both. What I meant is that you cannot write to a file you have opened for input, read it in context.

 

[fumei]

You can open a text file for input or for output, but not for both. What I meant is that you cannot write to a file you have opened for input, read it in context.

My bolding.


I know perfectly well what you meant; plus I believe I did read it in context, and the above quote is simply inaccurate.


I will repeat. You CAN write to a file you have opened for "input".

Sub YaddaYadda()
Dim MyIndex, FileNumber
Dim strWhatever
strWhatever = "This is sample # "
For MyIndex = 1 To 5 
    FileNumber = FreeFile
    Open "c:\start.txt" [b]For Append[/b] As #FileNumber
    [b]Write[/b] #FileNumber, strWhatever & MyIndex & "."  
    Close #FileNumber
Next MyIndex
End Sub

will Write

"This is a sample # 1."
"This is a sample # 2."
"This is a sample # 3."
"This is a sample # 4."
"This is a sample # 5."

into the opened text file c:\start.txt, then close (save) the file.

Perhaps there is some misunderstanding about the words "input" and "write"? For example:

Sub YaddaYadda_2()
Dim MyIndex, FileNumber
Dim strWhatever
strWhatever = "This is sample # "
For MyIndex = 1 To 5    ' Loop 5 times.
    FileNumber = FreeFile
    Open "c:\start.txt" [b]For Input[/b] As #FileNumber
    [b]Input[/b] #FileNumber, strWhatever   
    Close #FileNumber    ' Close file.
Next MyIndex
End Sub

does not work.

In any case, I believe you are incorrect. Regardless of the hosting application (Word, Excel etc. etc.) VBA can open a text file and Write to it. As you put it:

This is the simplest form of IO programming.

You can use either Write OR Print to "write" to an opened text file.

Print does NOT put quotation marks.
Write does. Say the file cL:\start.txt is:

This is a line.

   Dim lOutputFile As Long
   lOutputFile = FreeFile
   Open "C:\Start.txt" For Append As #lOutputFile
      Print #1, "This is a second line."
      Write #1, "This is a third line."
   Close lOutputFile

will end up with the following:

This is a line.
This is a second line.
"This is a third line."

Thus, if you want to - ahem - write to a text file you have opened.

Sub YaddaYadda_C()
Dim MyIndex, FileNumber
Dim strWhatever
strWhatever = "This is sample # "
For MyIndex = 1 To 5
    FileNumber = FreeFile
    Open "c:\start.txt" [b]For Append[/b] As #FileNumber
    [b]Print[/b] #FileNumber, strWhatever & MyIndex & "."
    Close #FileNumber
Next MyIndex
End Sub

changes:

This is a line.

into:

This is a line.
This is sample # 1.
This is sample # 2.
This is sample # 3.
This is sample # 4.
This is sample # 5.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[MarkWalsh]

Yes, I did post that into the wrong thread. Sorry, my brain doesn't seem to be working today.

 

[ettienne]

OK, big misunderstanding there. I was not referring to the Write() function but to write or output to a file. I meant you cannot write data to the same file that you have opened for Input.
To simplify, if you open a file for Input then you cannot Write/Print to it. Correct?
If you open a file for Output then you can Write/Print to it. This creates a new text file if it does not exist and it overwrites an existing file. Correct?
If you open a file for Append then you can Write/Print to it. This creates a new file if it does not exist and appends to an existing file. Correct?

I've been doing this for 20+ years, I know how to read/write text files.

 

[fumei]

I've been doing this for 20+ years, I know how to read/write text files. "

Sorry, really, I am not trying to step on your toes. I am simply trying to answer the OP.

The OP asked: "What I need is to open a text file and make some changes by a micro, such as adding a string in a line or adding a line. Then save the modified file back to txt format. "

If you open a file for Append then you can Write/Print to it. This creates a new file if it does not exist and appends to an existing file. Correct?

Correct. And is this not what the OP is asking for?

So, the answer is for the OP is, yes you can do that. Although, technically you are not, of course, saving the file back to txt format. You are opening it as text format, "adding a string in a line or adding a line", and closing it as a text file. The closed file has those added strings.

I really do not know what you mean by: "I was not referring to the Write() function but to write or output to a file."

The Write() function writes to the file. And if I understand the OP correctly (and I could be wrong!), they probably want to use Print not Write, as it does not add the quotation marks.

Anyway, I am sorry if you feel I offended you in any way; I was simply trying to answer the OP.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[ettienne]

What you did do is take things out of context and make a big huff about it.
The OP does not state whether the file is CSV or continuous, neither did I assume either format.

Back to the OP question:

What I need is to open a text file and make some changes by a micro(sic), such as adding a string in a line or adding a line.

...adding a string in a line...

Append will not work in this case since any new text written to the file will be appended to the end of the file, you cannot append to a line in the file.

...or adding a line.

Append will only work if the line is to be added to the end of the file, you cannot insert lines before the end of the file.

 

[fumei]

make a big huff "

I am sorry you feel I made any huff at all, never mind a "big" one. I shall retreat.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry