Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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 -- AJM1949 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
Look the Open Statement in the help.
NickHK "AJM1949" wrote in message ... 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 -- AJM1949 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
(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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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 -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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 -- AJM1949 "urkec" wrote: 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 -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
Are you getting any errors or it just doesn't work?
-- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: 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 -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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 -- AJM1949 "urkec" wrote: Are you getting any errors or it just doesn't work? -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: 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 -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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. -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: Are you getting any errors or it just doesn't work? -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: 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 -- urkec "AJM1949" wrote: 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 -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Files
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. -- AJM1949 "urkec" wrote: (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. -- urkec "AJM1949" wrote: 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 -- AJM1949 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (xls) files spontaneously converting to text files | Excel Discussion (Misc queries) | |||
how do i convert text files into CSV files? | Excel Discussion (Misc queries) | |||
Macro to open *.dat files and save as .txt (comma delimited text files) | Excel Programming | |||
copy subfolders, replace text in files and save files in copied subfolders | Excel Programming | |||
open some txt files ,find text , copy the text before that to a single cell | Excel Programming |