Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 923
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 923
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,758
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,758
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Supressing CHR13 or vbCr write in text file Edwin Niemoller[_2_] Excel Programming 0 March 9th 05 08:38 PM
Load text file and write back sanjay Excel Programming 1 May 26th 04 03:28 AM
how to write other language in text file from VBA Ravi Excel Programming 1 January 27th 04 03:21 AM
opening and write to a text file in excel julian brotherton Excel Programming 1 October 5th 03 11:13 PM
write a line from Excel to a text file julian_bro Excel Programming 1 September 30th 03 01:29 AM


All times are GMT +1. The time now is 08:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"