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

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

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

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

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

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

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

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

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

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