macro/code needed to export multiple tables to .txt files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Several times a month, I export the same 3 tables each to a fixed-width text
file. I maintain the same file name (as table object) and then append the
current date at the end of the name. These files are then appended together
in a text app for upload to another system (I can't do this in Access as the
table structures vary widely.)

How can I automate the export of these files using the fixed-width
specification and, if possible, add the date to the end of the name to
distinguish from past exports? Destination location will always be the same.

Thanks in advance.
 
Hi Dawn,

Using VBA, have three calls to DoCmd.TransferText to export the three
tables to three temporary files with fixed names - let's call them,
Table1.txt, Table2.txt ... Then concatenate the three files into one,
giving it the name and location you want.

Air code:

Const DEST_NAME As String = "C:\Folder\Subfolder\Export file "
Const DEST_EXT As String = ".txt"

Dim TempFolder As String
Dim FinalName As String
Dim CmdStr As String
Dim Temp1 As String, Temp2 As String, Temp3 As String

'Build temp file names using user's temp folder
Temp1 = Environ("TEMP") & "\Table1.txt"
Temp2 = Environ("TEMP") & "\Table2.txt"
Temp3 = Environ("TEMP") & "\Table3.txt"

'Delete previous temp files if they exist
On Error Resume Next
Kill Temp1
Kill Temp2
Kill Temp3
On Error GoTo 0

'Export to temp files
DoCmd.TransferText blah "Table1" blah Temp1 blah
DoCmd.TransferText blah "Table2" blah Temp2 blah
DoCmd.TransferText blah "Table3" blah Temp3 blah

'Concatenate using Windows COPY command
FinalName = DEST_NAME & Format(Date(), "yyyymmdd") & DEST_EXT
CmdStr = "COPY /B /Y """ _
& Temp1 & """ + """ _
& Temp2 & """ + """ _
& Temp3 & """ " & FinalName
Shell CmdStr
 
Back
Top