Make export specs on the fly

[Danny]

Is there a way to create export specs on the fly using
VBA? I want to be able to use all of the functionality of
the export specifications, without knowing the exact
format of the file ahead of time, and without forcing the
user to manually export an Access object (usually a
query).

For example, I want the user to be able to select a query
name from a pulldown, specify the export location (e.g.,
c:\temp\exportfile.txt), and then click a command button
that will create a spec for that file, but with a
standard list of parameters - tab delimited, zero-filled
dates, four-digit year.

Is this possible?

Thanks for the help.

Danny

 

[Joe Fallon]

Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub

 

[Danny]

Thank you. I will give this a try!

Danny

-----Original Message-----
Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset

("MyTableOrQueryName", dbOpenSnapshot)

'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(! [key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(! [Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(!

[TransDate], "mm/dd/yyyy") '27-36

strData = strData & Format(!

[Date1], "mm/dd/yyyy") '37-46

strData = strData & Format(! [Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(!

[Date3], "mm/dd/yyyy") '57-66

strData = strData & Format(! [Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(! [Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub

--
Joe Fallon
Access MVP

Is there a way to create export specs on the fly using
VBA? I want to be able to use all of the functionality of
the export specifications, without knowing the exact
format of the file ahead of time, and without forcing the
user to manually export an Access object (usually a
query).

For example, I want the user to be able to select a query
name from a pulldown, specify the export location (e.g.,
c:\temp\exportfile.txt), and then click a command button
that will create a spec for that file, but with a
standard list of parameters - tab delimited, zero- filled
dates, four-digit year.

Is this possible?

Thanks for the help.

Danny

.