Adding cell value to existing txt file...

[RobbieC]

Hi there, I have a text file (data.txt) which I'm looking to add data to (either at the bottom or the top, doesn't matter)

Basically, I have a userform which, when the update button is clicked, adds the value of cell A1 to the txt file on a new line.

I don't want the text file to open up, nor do I really want to copy the existing text in the file to a sheet, add the new value and re-save as this operation could take a long time to complete depending on the amount of data in the txt file

Is it possible to just append the cell value to the txt file without the kafuffle of the above and without the user seeing the operation???

If you have any advice for me, I'd be very happy to hear from you

Thanks

 

[RobbieC]

I found this which does the trick... just in case anyone else needs to do something similar

[COLOR=#0000FF][FONT=&quot]Sub [/FONT][/COLOR][COLOR=#444444][FONT=&quot]AddToTXT()[/FONT][/COLOR]
[COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'the final string to print in the text file [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Dim [/FONT][/COLOR]strData [COLOR=#0000FF][FONT=inherit]As [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]String [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'each line in the original text file [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Dim [/FONT][/COLOR]strLine [COLOR=#0000FF][FONT=inherit]As [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]String [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot]strData = ""
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'open the original text file to read the lines [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Open [/FONT][/COLOR]"D:TempTest.txt" [COLOR=#0000FF][FONT=inherit]For [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]Input [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]As [/FONT][/COLOR]#1
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'continue until the end of the file [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]While [/FONT][/COLOR]EOF(1) = [COLOR=#0000FF][FONT=inherit]False[/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'read the current line of text [/FONT][/COLOR][/FONT][/COLOR]
[COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Line [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]Input [/FONT][/COLOR]#1, strLine [/FONT][/COLOR]
[COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'add the current line to strData [/FONT][/COLOR][/FONT][/COLOR]
[COLOR=#444444][FONT=&quot]strData = strData + strLine & vbCrLf [/FONT][/COLOR]
[COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Wend [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'add the new line [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot]strData = strData + "Data to be appended"
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Close [/FONT][/COLOR]#1
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#00B400][FONT=inherit]'reopen the file for output [/FONT][/COLOR]
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Open [/FONT][/COLOR]"D:TempTest.txt" [COLOR=#0000FF][FONT=inherit]For [/FONT][/COLOR]Output [COLOR=#0000FF][FONT=inherit]As [/FONT][/COLOR]#1
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Print [/FONT][/COLOR]#1, strData
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]Close [/FONT][/COLOR]#1
[/FONT][/COLOR][COLOR=#444444][FONT=&quot][COLOR=#0000FF][FONT=inherit]End [/FONT][/COLOR][COLOR=#0000FF][FONT=inherit]Sub[/FONT][/COLOR][/FONT][/COLOR]

 

[Joe4]

OK, but you do realize that it DOES, in fact, open your text file.
So it doesn't appear to quite meet the requirement you laid out in your original question.

Quote from original question:

I don't want the text file to open up,

Quite frankly, I do not know of a way to do it without opening the text file (I am not sure that is even possible).

 

[Rick Rothstein]

I found this which does the trick... just in case anyone else needs to do something similar

Sub AddToTXT()
'the final string to print in the text file 
Dim strData As String 
'each line in the original text file 
Dim strLine As String 
strData = ""
'open the original text file to read the lines 
Open "D:TempTest.txt" For Input As #1
'continue until the end of the file 
While EOF(1) = False
'read the current line of text 
Line Input #1, strLine 
'add the current line to strData 
strData = strData + strLine & vbCrLf 
Wend 
'add the new line 
strData = strData + "Data to be appended"
Close #1
'reopen the file for output 
Open "D:TempTest.txt" For Output As #1
Print #1, strData
Close #1
End Sub

Don't open and loop to the end... open the file in Append mode instead. For example....

Sub AddToTextFile()
  Dim strData As String
  strData = [B][COLOR="#FF0000"]"Test Line of Text"[/COLOR][/B]
  Open [B][COLOR="#0000FF"]"c:\temp\test.txt"[/COLOR][/B] For Append As #1
  Print #1, strData
  Close #1
End Sub

 

[RobbieC]

Thanks Rick, that's a much cleaner method!

Thanks for your input Joe4. I realise that it does open the txt file, but it does so momentarily, behind the scenes, without the users knowledge, which is what I really was after...

Thanks for your help guys