Writing to text files ... "overload"

I am exporting ms-access data to XML files. This works fine.

However, I need to insert one line at the top of each exported file
(i.e. a reference to the XSL file) and am having a problem with this.

First I did the following:

Export the XML file (using standard XML export) and then read the file
(line by line) and writing it out (together with the required extra
line) to a new file.

This worked OK but, with larger files, the process seems to fall over
with the message "overload". I imagine here that the buffer is full.

Can anyone suggest a way to fix this?

Or is there an easier way to simply add a line to the top of an existing
(text) file?

Maybe someone can help me here? Or can point me towards a HOWTO which
explains VBA and text-file handling in more detail?

Many thanks,
Alan Searle

re: Writing to text files ... "overload"

There are a bunch of ways of approaching this.

Probably the most straightforward is to write your line to a file and then
use the dos copy command to concatenate the files together.

e.g.

Sub DOSCopy(FromFiles As Variant, ByVal ToFile As String)
Dim strCmd As String
Dim strFrom As String
Dim intcount As Integer

ToFile = """" & ToFile & """"

If IsArray(FromFiles) Then
For intcount = LBound(FromFiles) To UBound(FromFiles)
strFrom = strFrom & """" & FromFiles(intcount) & """ + "
Next
strFrom = Left(strFrom, Len(strFrom) - 2)
Else
strFrom = FromFiles & " "
End If

strCmd = Environ$("COMSPEC") & " /c copy " _
& strFrom & ToFile

Call Shell(strCmd)
End Sub

The above procedure allows you to pass in n filenames in an array as the
source files and a single file as the target, it then uses the dos copy
command to oncatenate the files together.

Sample call
call doscopy(array("C:\test\1.txt", "C:\test\2.txt"), "C:\test\3.txt")

This produces a shell command like this
C:\WINNT\system32\cmd.exe /c copy "C:\test\1.txt" + "C:\test\2.txt"
"C:\test\3.txt"

--

Terry Kreft


"Alan Searle" <aj_searle@xxxyahoo.com> wrote in message
news:e60ndk$5d2$1@newsreader2.netcologne.de...[color=blue]
> I am exporting ms-access data to XML files. This works fine.
>
> However, I need to insert one line at the top of each exported file
> (i.e. a reference to the XSL file) and am having a problem with this.
>
> First I did the following:
>
> Export the XML file (using standard XML export) and then read the file
> (line by line) and writing it out (together with the required extra
> line) to a new file.
>
> This worked OK but, with larger files, the process seems to fall over
> with the message "overload". I imagine here that the buffer is full.
>
> Can anyone suggest a way to fix this?
>
> Or is there an easier way to simply add a line to the top of an existing
> (text) file?
>
> Maybe someone can help me here? Or can point me towards a HOWTO which
> explains VBA and text-file handling in more detail?
>
> Many thanks,
> Alan Searle[/color]

re: Writing to text files ... "overload"

Hi Terry,

Great. This looks good and I'll give it a try.

The line I need to write should be written to the second line of the
recipient file. But I'll try writing it to the first instead and then
see what happens :-)

Thanks very much for the tip.

Cheers,
Alan.

Terry Kreft wrote:[color=blue]
> There are a bunch of ways of approaching this.
>
> Probably the most straightforward is to write your line to a file and then
> use the dos copy command to concatenate the files together.
>
> e.g.
>
> Sub DOSCopy(FromFiles As Variant, ByVal ToFile As String)
> Dim strCmd As String
> Dim strFrom As String
> Dim intcount As Integer
>
> ToFile = """" & ToFile & """"
>
> If IsArray(FromFiles) Then
> For intcount = LBound(FromFiles) To UBound(FromFiles)
> strFrom = strFrom & """" & FromFiles(intcount) & """ + "
> Next
> strFrom = Left(strFrom, Len(strFrom) - 2)
> Else
> strFrom = FromFiles & " "
> End If
>
> strCmd = Environ$("COMSPEC") & " /c copy " _
> & strFrom & ToFile
>
> Call Shell(strCmd)
> End Sub
>
> The above procedure allows you to pass in n filenames in an array as the
> source files and a single file as the target, it then uses the dos copy
> command to oncatenate the files together.
>
> Sample call
> call doscopy(array("C:\test\1.txt", "C:\test\2.txt"), "C:\test\3.txt")
>
> This produces a shell command like this
> C:\WINNT\system32\cmd.exe /c copy "C:\test\1.txt" + "C:\test\2.txt"
> "C:\test\3.txt"
>[/color]

re: Writing to text files ... "overload"

Hi Terry,

I implemented your DOCcopy suggestion (below) and it worked fine,
concatenating the files as required.

However, when I tried to open the XML file (with the extra line that I
had added with DOScopy) the browser couldn't process it.

I then went into the file and manually deleted a small square character
that had appeared at the end of the file (maybe a carriage return?) and
then it worked fine and the XML could be displayed.

Any ideas about this character? How can I get DOScopy to not create it?

If I can fix this little problem, then your idea will work for me.

Many thanks,
Alan Searle

Terry Kreft wrote:[color=blue]
> There are a bunch of ways of approaching this.
>
> Probably the most straightforward is to write your line to a file and then
> use the dos copy command to concatenate the files together.
>
> e.g.
>
> Sub DOSCopy(FromFiles As Variant, ByVal ToFile As String)
> Dim strCmd As String
> Dim strFrom As String
> Dim intcount As Integer
>
> ToFile = """" & ToFile & """"
>
> If IsArray(FromFiles) Then
> For intcount = LBound(FromFiles) To UBound(FromFiles)
> strFrom = strFrom & """" & FromFiles(intcount) & """ + "
> Next
> strFrom = Left(strFrom, Len(strFrom) - 2)
> Else
> strFrom = FromFiles & " "
> End If
>
> strCmd = Environ$("COMSPEC") & " /c copy " _
> & strFrom & ToFile
>
> Call Shell(strCmd)
> End Sub
>
> The above procedure allows you to pass in n filenames in an array as the
> source files and a single file as the target, it then uses the dos copy
> command to oncatenate the files together.
>
> Sample call
> call doscopy(array("C:\test\1.txt", "C:\test\2.txt"), "C:\test\3.txt")
>
> This produces a shell command like this
> C:\WINNT\system32\cmd.exe /c copy "C:\test\1.txt" + "C:\test\2.txt"
> "C:\test\3.txt"
>[/color]

re: Writing to text files ... "overload"

Whoops, the line

strCmd = Environ$("COMSPEC") & " /c copy " _
& strFrom & ToFile

Should be
strCmd = Environ$("COMSPEC") & " /c copy /b " _
& strFrom & ToFile
(note the /b)

otherwise it appends the dos eof character which is ASCII 26 (the
rectangular mark you see).



--

Terry Kreft


"Alan Searle" <aj_searle@xxxyahoo.com> wrote in message
news:e6hopb$cff$1@newsreader2.netcologne.de...[color=blue]
> Hi Terry,
>
> I implemented your DOCcopy suggestion (below) and it worked fine,
> concatenating the files as required.
>
> However, when I tried to open the XML file (with the extra line that I
> had added with DOScopy) the browser couldn't process it.
>
> I then went into the file and manually deleted a small square character
> that had appeared at the end of the file (maybe a carriage return?) and
> then it worked fine and the XML could be displayed.
>
> Any ideas about this character? How can I get DOScopy to not create it?
>
> If I can fix this little problem, then your idea will work for me.
>
> Many thanks,
> Alan Searle
>
> Terry Kreft wrote:[color=green]
> > There are a bunch of ways of approaching this.
> >
> > Probably the most straightforward is to write your line to a file and[/color][/color]
then[color=blue][color=green]
> > use the dos copy command to concatenate the files together.
> >
> > e.g.
> >
> > Sub DOSCopy(FromFiles As Variant, ByVal ToFile As String)
> > Dim strCmd As String
> > Dim strFrom As String
> > Dim intcount As Integer
> >
> > ToFile = """" & ToFile & """"
> >
> > If IsArray(FromFiles) Then
> > For intcount = LBound(FromFiles) To UBound(FromFiles)
> > strFrom = strFrom & """" & FromFiles(intcount) & """ + "
> > Next
> > strFrom = Left(strFrom, Len(strFrom) - 2)
> > Else
> > strFrom = FromFiles & " "
> > End If
> >
> > strCmd = Environ$("COMSPEC") & " /c copy " _
> > & strFrom & ToFile
> >
> > Call Shell(strCmd)
> > End Sub
> >
> > The above procedure allows you to pass in n filenames in an array as the
> > source files and a single file as the target, it then uses the dos copy
> > command to oncatenate the files together.
> >
> > Sample call
> > call doscopy(array("C:\test\1.txt", "C:\test\2.txt"),[/color][/color]
"C:\test\3.txt")[color=blue][color=green]
> >
> > This produces a shell command like this
> > C:\WINNT\system32\cmd.exe /c copy "C:\test\1.txt" + "C:\test\2.txt"
> > "C:\test\3.txt"
> >[/color][/color]

re: Writing to text files ... "overload"

Terry Kreft wrote:[color=blue]
> Whoops, the line
>
> strCmd = Environ$("COMSPEC") & " /c copy " _
> & strFrom & ToFile
>
> Should be
> strCmd = Environ$("COMSPEC") & " /c copy /b " _
> & strFrom & ToFile
> (note the /b)
>
> otherwise it appends the dos eof character which is ASCII 26 (the
> rectangular mark you see).
>
>
>[/color]