Exporting and appending to txt file

[RJF]

Using VBA in Access, I need to export 2 queries to txt files, then append the
2 txt files together. In this case, I can't use a union query and then
export the data because they are totally different formats.

Exporting the 2 files is not a problem. I’m using:

strSaveFileName_Copy = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01
& "00_Copy.txt"
strSaveFileName_Fax = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01
& "00_Fax.txt"
strSaveFileName = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"

DoCmd.TransferText acExportFixed, "Copy_Export_Spec", "qry_Copy",
strSaveFileName_Copy
DoCmd.TransferText acExportFixed, "Fax_Export_Spec", "qry_Fax",
strSaveFileName_Fax

After this I get the correct separate txt files exported: R090200_Copy.txt
and R090200_Fax.txt

I found this code on-line to append these, but it's not working and I can't
figure out why.

I created a .bat file called merge.bat as follows and stored it in the
following directory: G:\Code\File_Conversion

"Copy strSaveFileName_Copy + strSaveFileName_Fax strSaveFileName"

(I tried using double quotes around the statement above and not using them
and neither worked)

Then in the VHB code, after I export the 2 files, I use this:


Shell "G:\Code\File_Conversion\merge.bat"
DoEvents

This code does nothing for me. I expected it to create a txt file called
R090200.txt then copy R090200_Copy.txt and R090200_Fax.txt into it.

Can anyone tell me what I'm doing wrong?

It will be greatly appreciated.

Thank you,
Rach

 

[Mark A. Sam]

RJF,

Check help for Print # Statement. It will show you how to write directly
into a text file so that you can select what to print from each query and
format it the way you need to. In Access 2007 open a module, then open
Help. Search on the key words Print Statement then the result lableled
Print # Statement. It will explain this. I don't know how to find it in
previous versions, but maybe the same way or enter the keywords, Print # or
Print#.

You get an explanation and a good example on how to do this:

Open "TESTFILE" For Output As #1 ' Open file for output.
Print #1, "This is a test" ' Print text to file.
Print #1, ' Print blank line to file.
Print #1, "Zone 1"; Tab ; "Zone 2" ' Print in two print zones.
Print #1, "Hello" ; " " ; "World" ' Separate strings with space.
Print #1, Spc(5) ; "5 leading spaces " ' Print five leading spaces.
Print #1, Tab(10) ; "Hello" ' Print word at column 10.

' Assign Boolean, Date, Null and Error values.
Dim MyBool, MyDate, MyNull, MyError
MyBool = False : MyDate = #February 12, 1969# : MyNull = Null
MyError = CVErr(32767)
' True, False, Null, and Error are translated using locale settings of
' your system. Date literals are written using standard short date
' format.
Print #1, MyBool ; " is a Boolean value"
Print #1, MyDate ; " is a date"
Print #1, MyNull ; " is a null value"
Print #1, MyError ; " is an error value"
Close #1 ' Close file.

God Bless,

Mark A. Sam

Using VBA in Access, I need to export 2 queries to txt files, then append
the
2 txt files together. In this case, I can't use a union query and then
export the data because they are totally different formats.

Exporting the 2 files is not a problem. I'm using:

strSaveFileName_Copy = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Copy.txt"
strSaveFileName_Fax = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Fax.txt"
strSaveFileName = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"

DoCmd.TransferText acExportFixed, "Copy_Export_Spec", "qry_Copy",
strSaveFileName_Copy
DoCmd.TransferText acExportFixed, "Fax_Export_Spec", "qry_Fax",
strSaveFileName_Fax

After this I get the correct separate txt files exported:
R090200_Copy.txt
and R090200_Fax.txt

I found this code on-line to append these, but it's not working and I
can't
figure out why.

I created a .bat file called merge.bat as follows and stored it in the
following directory: G:\Code\File_Conversion

"Copy strSaveFileName_Copy + strSaveFileName_Fax strSaveFileName"

(I tried using double quotes around the statement above and not using them
and neither worked)

Then in the VHB code, after I export the 2 files, I use this:


Shell "G:\Code\File_Conversion\merge.bat"
DoEvents

This code does nothing for me. I expected it to create a txt file called
R090200.txt then copy R090200_Copy.txt and R090200_Fax.txt into it.

Can anyone tell me what I'm doing wrong?

It will be greatly appreciated.

Thank you,
Rach

 

[RJF]

Thanks Mark.

I will give it a try and report back.

-Rach

--
RJF

RJF,

Check help for Print # Statement. It will show you how to write directly
into a text file so that you can select what to print from each query and
format it the way you need to. In Access 2007 open a module, then open
Help. Search on the key words Print Statement then the result lableled
Print # Statement. It will explain this. I don't know how to find it in
previous versions, but maybe the same way or enter the keywords, Print # or
Print#.

You get an explanation and a good example on how to do this:

Open "TESTFILE" For Output As #1 ' Open file for output.
Print #1, "This is a test" ' Print text to file.
Print #1, ' Print blank line to file.
Print #1, "Zone 1"; Tab ; "Zone 2" ' Print in two print zones.
Print #1, "Hello" ; " " ; "World" ' Separate strings with space.
Print #1, Spc(5) ; "5 leading spaces " ' Print five leading spaces.
Print #1, Tab(10) ; "Hello" ' Print word at column 10.

' Assign Boolean, Date, Null and Error values.
Dim MyBool, MyDate, MyNull, MyError
MyBool = False : MyDate = #February 12, 1969# : MyNull = Null
MyError = CVErr(32767)
' True, False, Null, and Error are translated using locale settings of
' your system. Date literals are written using standard short date
' format.
Print #1, MyBool ; " is a Boolean value"
Print #1, MyDate ; " is a date"
Print #1, MyNull ; " is a null value"
Print #1, MyError ; " is an error value"
Close #1 ' Close file.

God Bless,

Mark A. Sam

Using VBA in Access, I need to export 2 queries to txt files, then append
the
2 txt files together. In this case, I can't use a union query and then
export the data because they are totally different formats.

Exporting the 2 files is not a problem. I'm using:

strSaveFileName_Copy = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Copy.txt"
strSaveFileName_Fax = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Fax.txt"
strSaveFileName = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"

DoCmd.TransferText acExportFixed, "Copy_Export_Spec", "qry_Copy",
strSaveFileName_Copy
DoCmd.TransferText acExportFixed, "Fax_Export_Spec", "qry_Fax",
strSaveFileName_Fax

After this I get the correct separate txt files exported:
R090200_Copy.txt
and R090200_Fax.txt

I found this code on-line to append these, but it's not working and I
can't
figure out why.

I created a .bat file called merge.bat as follows and stored it in the
following directory: G:\Code\File_Conversion

"Copy strSaveFileName_Copy + strSaveFileName_Fax strSaveFileName"

(I tried using double quotes around the statement above and not using them
and neither worked)

Then in the VHB code, after I export the 2 files, I use this:


Shell "G:\Code\File_Conversion\merge.bat"
DoEvents

This code does nothing for me. I expected it to create a txt file called
R090200.txt then copy R090200_Copy.txt and R090200_Fax.txt into it.

Can anyone tell me what I'm doing wrong?

It will be greatly appreciated.

Thank you,
Rach

 

[RJF]

Hi Mark,

This is what I came up with using your response and searching other forums.

strSaveFileName_01 = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"
strSaveFileName_Fax_01 = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01 & "50.txt"
strSaveFileName_Test_01 = [Forms]![frm_ClientConv]!txt_FilePath &
"Test_01.txt"


Dim FFileIn As Long, FFileOut As Long

FFileOut = FreeFile
Open strSaveFileName_Test_01 For Output As #FFileOut

FFileIn = FreeFile
Open strSaveFileName_01 For Append As #FFileIn
Print #FFileOut, strSaveFileName_01
Close #FFileIn

FFileIn = FreeFile
Open strSaveFileName_Fax_01 For Append As #FFileIn
Print #FFileOut, strSaveFileName_Fax_01
Close #FFileIn

Close #FFileOut

This almost works. But my file called "Test_01.txt" is appended with the
path and names of the files that I'm trying to append the data from, not the
data contained inside these files.

Example of the text that's appended into "Test_01.txt" :
G:\OSS\OSSBill\TEST_rjf\Append_txt_files\R090100.txt
G:\OSS\OSSBill\TEST_rjf\Append_txt_files\R090150.txt

How do I get the actual data from these files to append to "Test_01.txt"?

Any idea of what I'm doing wrong?

Thanks,
Rach

--
RJF

RJF,

Check help for Print # Statement. It will show you how to write directly
into a text file so that you can select what to print from each query and
format it the way you need to. In Access 2007 open a module, then open
Help. Search on the key words Print Statement then the result lableled
Print # Statement. It will explain this. I don't know how to find it in
previous versions, but maybe the same way or enter the keywords, Print # or
Print#.

You get an explanation and a good example on how to do this:

Open "TESTFILE" For Output As #1 ' Open file for output.
Print #1, "This is a test" ' Print text to file.
Print #1, ' Print blank line to file.
Print #1, "Zone 1"; Tab ; "Zone 2" ' Print in two print zones.
Print #1, "Hello" ; " " ; "World" ' Separate strings with space.
Print #1, Spc(5) ; "5 leading spaces " ' Print five leading spaces.
Print #1, Tab(10) ; "Hello" ' Print word at column 10.

' Assign Boolean, Date, Null and Error values.
Dim MyBool, MyDate, MyNull, MyError
MyBool = False : MyDate = #February 12, 1969# : MyNull = Null
MyError = CVErr(32767)
' True, False, Null, and Error are translated using locale settings of
' your system. Date literals are written using standard short date
' format.
Print #1, MyBool ; " is a Boolean value"
Print #1, MyDate ; " is a date"
Print #1, MyNull ; " is a null value"
Print #1, MyError ; " is an error value"
Close #1 ' Close file.

God Bless,

Mark A. Sam

Using VBA in Access, I need to export 2 queries to txt files, then append
the
2 txt files together. In this case, I can't use a union query and then
export the data because they are totally different formats.

Exporting the 2 files is not a problem. I'm using:

strSaveFileName_Copy = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Copy.txt"
strSaveFileName_Fax = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Fax.txt"
strSaveFileName = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"

DoCmd.TransferText acExportFixed, "Copy_Export_Spec", "qry_Copy",
strSaveFileName_Copy
DoCmd.TransferText acExportFixed, "Fax_Export_Spec", "qry_Fax",
strSaveFileName_Fax

After this I get the correct separate txt files exported:
R090200_Copy.txt
and R090200_Fax.txt

I found this code on-line to append these, but it's not working and I
can't
figure out why.

I created a .bat file called merge.bat as follows and stored it in the
following directory: G:\Code\File_Conversion

"Copy strSaveFileName_Copy + strSaveFileName_Fax strSaveFileName"

(I tried using double quotes around the statement above and not using them
and neither worked)

Then in the VHB code, after I export the 2 files, I use this:


Shell "G:\Code\File_Conversion\merge.bat"
DoEvents

This code does nothing for me. I expected it to create a txt file called
R090200.txt then copy R090200_Copy.txt and R090200_Fax.txt into it.

Can anyone tell me what I'm doing wrong?

It will be greatly appreciated.

Thank you,
Rach

 

[RJF]

After more research I found something that is working. Thank you for your
help.


Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String

DestNum = FreeFile()
Open strSaveFileName_Test_01 For Append As DestNum

SourceNum = FreeFile()
Open strSaveFileName_01 For Input As SourceNum
Line Input #SourceNum, Temp

Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
Print #DestNum, Temp
Loop

Close #DestNum
Close #SourceNum
--
RJF

Hi Mark,

This is what I came up with using your response and searching other forums.

strSaveFileName_01 = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"
strSaveFileName_Fax_01 = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01 & "50.txt"
strSaveFileName_Test_01 = [Forms]![frm_ClientConv]!txt_FilePath &
"Test_01.txt"


Dim FFileIn As Long, FFileOut As Long

FFileOut = FreeFile
Open strSaveFileName_Test_01 For Output As #FFileOut

FFileIn = FreeFile
Open strSaveFileName_01 For Append As #FFileIn
Print #FFileOut, strSaveFileName_01
Close #FFileIn

FFileIn = FreeFile
Open strSaveFileName_Fax_01 For Append As #FFileIn
Print #FFileOut, strSaveFileName_Fax_01
Close #FFileIn

Close #FFileOut

This almost works. But my file called "Test_01.txt" is appended with the
path and names of the files that I'm trying to append the data from, not the
data contained inside these files.

Example of the text that's appended into "Test_01.txt" :
G:\OSS\OSSBill\TEST_rjf\Append_txt_files\R090100.txt
G:\OSS\OSSBill\TEST_rjf\Append_txt_files\R090150.txt

How do I get the actual data from these files to append to "Test_01.txt"?

Any idea of what I'm doing wrong?

Thanks,
Rach

--
RJF

RJF,

Check help for Print # Statement. It will show you how to write directly
into a text file so that you can select what to print from each query and
format it the way you need to. In Access 2007 open a module, then open
Help. Search on the key words Print Statement then the result lableled
Print # Statement. It will explain this. I don't know how to find it in
previous versions, but maybe the same way or enter the keywords, Print # or
Print#.

You get an explanation and a good example on how to do this:

Open "TESTFILE" For Output As #1 ' Open file for output.
Print #1, "This is a test" ' Print text to file.
Print #1, ' Print blank line to file.
Print #1, "Zone 1"; Tab ; "Zone 2" ' Print in two print zones.
Print #1, "Hello" ; " " ; "World" ' Separate strings with space.
Print #1, Spc(5) ; "5 leading spaces " ' Print five leading spaces.
Print #1, Tab(10) ; "Hello" ' Print word at column 10.

' Assign Boolean, Date, Null and Error values.
Dim MyBool, MyDate, MyNull, MyError
MyBool = False : MyDate = #February 12, 1969# : MyNull = Null
MyError = CVErr(32767)
' True, False, Null, and Error are translated using locale settings of
' your system. Date literals are written using standard short date
' format.
Print #1, MyBool ; " is a Boolean value"
Print #1, MyDate ; " is a date"
Print #1, MyNull ; " is a null value"
Print #1, MyError ; " is an error value"
Close #1 ' Close file.

God Bless,

Mark A. Sam

Using VBA in Access, I need to export 2 queries to txt files, then append
the
2 txt files together. In this case, I can't use a union query and then
export the data because they are totally different formats.

Exporting the 2 files is not a problem. I'm using:

strSaveFileName_Copy = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Copy.txt"
strSaveFileName_Fax = [Forms]![frm_ClientConv]!txt_FilePath & "R" &
YYMM_01
& "00_Fax.txt"
strSaveFileName = [Forms]![frm_ClientConv]!txt_FilePath & "R" & YYMM_01 &
"00.txt"

DoCmd.TransferText acExportFixed, "Copy_Export_Spec", "qry_Copy",
strSaveFileName_Copy
DoCmd.TransferText acExportFixed, "Fax_Export_Spec", "qry_Fax",
strSaveFileName_Fax

After this I get the correct separate txt files exported:
R090200_Copy.txt
and R090200_Fax.txt

I found this code on-line to append these, but it's not working and I
can't
figure out why.

I created a .bat file called merge.bat as follows and stored it in the
following directory: G:\Code\File_Conversion

"Copy strSaveFileName_Copy + strSaveFileName_Fax strSaveFileName"

(I tried using double quotes around the statement above and not using them
and neither worked)

Then in the VHB code, after I export the 2 files, I use this:


Shell "G:\Code\File_Conversion\merge.bat"
DoEvents

This code does nothing for me. I expected it to create a txt file called
R090200.txt then copy R090200_Copy.txt and R090200_Fax.txt into it.

Can anyone tell me what I'm doing wrong?

It will be greatly appreciated.

Thank you,
Rach