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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
Thanks Guys, this is really useful stuff.
For my interest, what is the implication of using FSO rather than not? I understand the complexity issue but then once set up it presumbaly is no more complex? So I guess is one of perfromance, compatability or something else? -- Cheers Nigel "Dave Peterson" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
Using another application has an overhead in terms of creating an instance
to that object, needs setting a reference to that application's runtime library, which can cause compatibility issues (unless you use late binding, which is much slower and difficult to develop), and in some sites, these scripting technologies may be disabled. I am not anti FSO, use it all the time myself, especially as I dislike the VBA Dir and FileSearch (had too many problems with them), but it should be used where it adds value, and in this case, I think the overhead is not worth it seeing as VB has a perfectly good facility for what you wanted. -- HTH RP (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Thanks Guys, this is really useful stuff. For my interest, what is the implication of using FSO rather than not? I understand the complexity issue but then once set up it presumbaly is no more complex? So I guess is one of perfromance, compatability or something else? -- Cheers Nigel "Dave Peterson" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Text File
Hi Bob,
Yes that makes sense. In this case I will revert to the far simpler version, as in my application the FSO would only be needed for this routine. -- Cheers Nigel "Bob Phillips" wrote in message ... Using another application has an overhead in terms of creating an instance to that object, needs setting a reference to that application's runtime library, which can cause compatibility issues (unless you use late binding, which is much slower and difficult to develop), and in some sites, these scripting technologies may be disabled. I am not anti FSO, use it all the time myself, especially as I dislike the VBA Dir and FileSearch (had too many problems with them), but it should be used where it adds value, and in this case, I think the overhead is not worth it seeing as VB has a perfectly good facility for what you wanted. -- HTH RP (remove nothere from the email address if mailing direct) "Nigel" wrote in message ... Thanks Guys, this is really useful stuff. For my interest, what is the implication of using FSO rather than not? I understand the complexity issue but then once set up it presumbaly is no more complex? So I guess is one of perfromance, compatability or something else? -- Cheers Nigel "Dave Peterson" wrote in message ... 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 |