Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
Hi All
I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code:
'------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
That's terrific, thanks very much
-- Cheers Nigel "Florian Strahberger" wrote in message ... You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code: '------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
VBA has its own facility so no need to use FSO here. From help
Dim MyIndex, FileNumber For MyIndex = 1 To 5 ' Loop 5 times. FileNumber = FreeFile ' Get unused file ' number. Open "TEST" & MyIndex For Output As #FileNumber ' Create file name. Write #FileNumber, "This is a sample." ' Output text. Close #FileNumber ' Close file. Next MyIndex -- HTH RP (remove nothere from the email address if mailing direct) "Florian Strahberger" wrote in message ... You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code: '------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
Since this is for a log file, I think "For append" might be better than
"for Output". (and it matches Florian's logic.) Bob Phillips wrote: VBA has its own facility so no need to use FSO here. From help Dim MyIndex, FileNumber For MyIndex = 1 To 5 ' Loop 5 times. FileNumber = FreeFile ' Get unused file ' number. Open "TEST" & MyIndex For Output As #FileNumber ' Create file name. Write #FileNumber, "This is a sample." ' Output text. Close #FileNumber ' Close file. Next MyIndex -- HTH RP (remove nothere from the email address if mailing direct) "Florian Strahberger" wrote in message ... You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code: '------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
As I said ... from help ... It was an example that the OP can look up
themselves if interested in a simpler solution than FSO and Extras -- Addins (whatever that is). "Dave Peterson" wrote in message ... Since this is for a log file, I think "For append" might be better than "for Output". (and it matches Florian's logic.) Bob Phillips wrote: VBA has its own facility so no need to use FSO here. From help Dim MyIndex, FileNumber For MyIndex = 1 To 5 ' Loop 5 times. FileNumber = FreeFile ' Get unused file ' number. Open "TEST" & MyIndex For Output As #FileNumber ' Create file name. Write #FileNumber, "This is a sample." ' Output text. Close #FileNumber ' Close file. Next MyIndex -- HTH RP (remove nothere from the email address if mailing direct) "Florian Strahberger" wrote in message ... You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code: '------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
My comment was more of a followup for the OP than you.
Bob Phillips wrote: As I said ... from help ... It was an example that the OP can look up themselves if interested in a simpler solution than FSO and Extras -- Addins (whatever that is). "Dave Peterson" wrote in message ... Since this is for a log file, I think "For append" might be better than "for Output". (and it matches Florian's logic.) Bob Phillips wrote: VBA has its own facility so no need to use FSO here. From help Dim MyIndex, FileNumber For MyIndex = 1 To 5 ' Loop 5 times. FileNumber = FreeFile ' Get unused file ' number. Open "TEST" & MyIndex For Output As #FileNumber ' Create file name. Write #FileNumber, "This is a sample." ' Output text. Close #FileNumber ' Close file. Next MyIndex -- HTH RP (remove nothere from the email address if mailing direct) "Florian Strahberger" wrote in message ... You could use the 'Scripting.FileSystemObject' from WSH. Here is a sample code: '------------------------------------------------------------ ' To use this example, go to Extras -- AddIns ' and inclde 'Microsoft Scripting Runtime'. '------------------------------------------------------------ Const PathToFile = "C:\_work\examples\test.txt" Sub TestIt() Dim oFs As New Scripting.FileSystemObject Dim oFile As Scripting.TextStream '------------------------------------------------ ' open existing file for appending, ' if the file does not exist a new one is created '------------------------------------------------- Set oFile = oFs.OpenTextFile(PathToFile, ForAppending, True) oFile.WriteLine ("Hello World from Excel") oFile.Close ' cleanup Set oFile = Nothing Set oFs = Nothing End Sub "Nigel" schrieb: Hi All I have an Excel application that loads a workbook from a data source on a daily basis, acts on it and produces a summary report. What I now wish to add is a logging function, whereby the data rows (up to 1000 per day) can be saved to an archive file. How can I do the following from within VBA..... 1. Create / Open a Text file 2. Write and subsequently append Excel data to this file 3. Close the file Thanks -- Cheers Nigel -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Supressing CHR13 or vbCr write in text file | Excel Programming | |||
Load text file and write back | Excel Programming | |||
how to write other language in text file from VBA | Excel Programming | |||
opening and write to a text file in excel | Excel Programming | |||
write a line from Excel to a text file | Excel Programming |