Thursday, January 6, 2011

Exporting data from Access table / query to a delimited textfile

This is a real simple one (quick and dirty, no exception handling) making use of an InputBox to set the filename and path and then exporting it to a text file. I created the following function in VBA

Public Function ExportToTxt()
 Dim strPath As String
 Dim strDate As String
 Dim strFullPath As String

 ' User sets folder location    
 strPath = InputBox("Give path", "Export to", "J:\Outgoing\YYYYMMDD") & "\"

 ' User sets extract date    
 strDate = InputBox("Give date in YYYYMMDD", "Export to", "YYYYMMDD")

 ' Set filename including path in string, to be used with the Transfer function
 strFullPath = strPath & "\" & strDate
 ' Export results to a delimited textfile,
 '  - using specifications specifications named "BrochSpecs" and "TDSpecs"
 '  - from table/query "03 Export BR Extract" and "04 Export TD Extract"
 '  - to filename and location set in strFullpath, postfixed by "_BR_Extract.txt"

 DoCmd.TransferText acExportDelim, "BrochSpecs", "03 Export BR Extract", strFullPath & "_BR_Extract.txt", True
 DoCmd.TransferText acExportDelim, "TDSpecs", "04 Export TD Extract", strFullPath & "_TD_Extract.txt", True

End Function