Exporting query to text file with pre-defined header

[James]

Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James

 

[John Nurick]

Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF

 

[James]

John,

Thanks for the lead. I have resolved part of the issue by
using

DoCmd.TransferText acExportDelim, etc

However, I am having trouble getting your code to add the
header and footer to work. Even when I hard code the file
names in the code, I get a "File Not Found" Error. I have
tried tweaking your code numerous times, but it won't
work. So I have the body, but can't get VBA to copy header
and footer files - or any files?

strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd

Maybe I need something other than "Copy"?

Cheers,

James

-----Original Message-----
Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF

Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi James,

Two things to try. First, if any of your file or folder names may
contain spaces, make sure the resulting filespecs are surrounded by
quotes:

strCmd = "Copy """ & strFolder & "Header.txt"" + """ & _
& strFolder & "Body.txt"" + """ & strFolder & "Footer.txt"" " _
& strFolder & "ExportFile.txt"

e.g. if the filenames are Header File.txt, Body File.txt etc., strCmd
should be like
Copy "D:\folder\Header File.txt" + "D:\folder\Body File.txt" +
"D:\Folder\Footer File.txt" "D:\Folder\Output File.txt"

Also, put
cmd.exe /c
or (in older versions of Windows)
command.com /c
at the beginning of strCmd, so it begins
cmd.exe /c copy ...

John,

Thanks for the lead. I have resolved part of the issue by
using

DoCmd.TransferText acExportDelim, etc

However, I am having trouble getting your code to add the
header and footer to work. Even when I hard code the file
names in the code, I get a "File Not Found" Error. I have
tried tweaking your code numerous times, but it won't
work. So I have the body, but can't get VBA to copy header
and footer files - or any files?

strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd

Maybe I need something other than "Copy"?

Cheers,

James

-----Original Message-----
Hi James,

There are various ways of doing this. Perhaps the simplest is to create
three temporary files, for the header, the body and the footer, and then
use a Windows command to concatenate them to the output file before
deleting them e.g.

Dim strFolder As String
Dim strCmd As String

strFolder = "D:\Folder\Subfolder\"
strCmd = "Copy " & strFolder & "Header.txt + " & _
& strFolder & "Body.txt + " & strFolder & "Footer.txt " _
& strFolder & "ExportFile.txt"
Shell strCmd
Kill strFolder & "Header.txt"
Kill strFolder & "Body.txt"
Kill strFolder & "Footer.txt"


For the footer that calculates the number of records, you can probably
use a totals query, something like this:

SELECT "Total: " & COUNT(ID) & " Records." AS F1 FROM qryXXX;

For the header file, you can either put the necessary information in a
little two-row table and use a query to assemble and export it, or else
create the file under program control, as in this untested air code:

Dim lngF As Long
Dim strLine As String

lngFN = FreeFile()
Open strFolder & "Header.txt" For Output As #lngF

strLine = "This is the first line of the header"
Print #lngF, strLine

strLine = "Created " & Format(Date(), "yyyy.mm.dd")
Print #lngF, strLine

Close #lngF

Can anyone assist with exporting a query to a txt flat
file with delimeters, including a pre-defined header in
the txt file. The header is two lines, but has
information necessary for it to work with the target
application - ie I am going to import the text file from
the target application.

The only other option I could think of was to somehow
incorporate the header information in the query - but I
don't see how I can do that. I also need a footer that
caluclates the number of records.

Cheers,

James

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.