Text Files

[Guest]

How can I store a value in a text file from (1) a cell reference (2) a text
box on a form?

Thanks in advance for your help

 

[NickHK]

Look the Open Statement in the help.

NickHK

 

[Guest]

(1)


Sub SaveCellToTXT()

Set fso = CreateObject _
("Scripting.FileSystemObject")

Set txtFile = fso.CreateTextFile _
("C:\test1.txt", True)

txtFile.WriteLine _
ActiveWorkbook.Sheets(1).Cells(1, 1)

txtFile.Close

End Sub

(2)

Sub SaveTextBoxToTXT()

Set fso = CreateObject _
("Scripting.FileSystemObject")

Set txtFile = fso.CreateTextFile _
("C:\test2.txt", True)

'UserForm1.TextBox1.Value = "test"

txtFile.WriteLine _
UserForm1.TextBox1.Value

txtFile.Close


End Sub

Hope that helps.

 

[Guest]

Thanks very much. That seems to work fine.
I also need some help to read these stored values back into a cell.
Can you help with this>

 

[Guest]

Sub LoadTXTToCell(filePath As String)

ForReading = 1

Set fso = CreateObject _
("Scripting.FileSystemObject")

If fso.FileExists(filePath) Then
Set txtFile = fso.OpenTextFile _
(filePath, ForReading)
Else
Exit Sub
End If

ActiveWorkbook.Sheets(1).Cells(1, 1) = _
txtFile.ReadLine
'or
'txtFile.ReadAll

txtFile.Close

End Sub

 

[Guest]

Hi

Thanks for the code. I seem to be missing something. I am trying to save
ithe text file value to a named range but can't seem to make it work. I am
still learning and appreciate the help given

 

[Guest]

Are you getting any errors or it just doesn't work?

 

[Guest]

It doesn't work at all. When I copy the code to a module exactly as written
it does not appear on the list of macros to run. If I remove the "filePath As
String" from the brackets it does appear as a macro to run but does not
appear to function. I don't really understand all the code (I am a new to
VBA) Should there be some reference to the particular text file to read from?

What I want to do is allow users to create multiple values(unique to their
pc and save them as text files and then read the values back into the master
file on startup. This allows me to send updates to the master file without
affecting their stored values. I am running Excel 2007 (compatibility mode)
and Excel 2003

 

[Guest]

You can hardcode the name of the text file.

If you write multiple lines (txtFile.Writeline) to the file from cells with
you need to read the file line by line (txtFile.Readline) and store each line
back in the cell you want.

Sub LoadTXTToCell()

Dim filePath As String
Const ForReading = 1

'the file path you want to read from
filePath = "C:\myFile.txt"

'file system object
Set fso = CreateObject _
("Scripting.FileSystemObject")

'if the file exists open it for reading
'else exit the sub
If fso.FileExists(filePath) Then
Set txtFile = fso.OpenTextFile _
(filePath, ForReading)
Else
Exit Sub
End If

i = 1

'read all the lines one by one
'into cells
Do While Not txtFile.AtEndOfStream
ActiveWorkbook.Sheets(1).Cells(i, 1) = _
txtFile.ReadLine
i = i + 1
Loop

'close the text file
txtFile.Close

End Sub


Hope this helps some.

 

[Guest]

Sorry to be a bit slow replying. I got a bit side tracked with another
project. Thanks very much for your assistance. I now have all the code
working fine.