MS Access - Export Specifications and CSV Files
Opening CSV files using Export Specification and Excel.Application object
This article describes how to create an MS Access form that generates a CSV file using an Export Specification and a FileSystemObject.
Most of the time, a TransferText command does not require an Export Specification be specified to generate a CSV file on the system.
DoCmd.TransferText acExportDelim, "", "vw_employees", sFile, True
However, there have been times when I have gotten an error for not putting a specification name in the command. This article will start with the creation of the Export Spec.
The form that this article uses has a single command button that will generate the file.
Creating the MS Access Export Specification - Delimited Option
To begin generating the Export Spec, right click on the table, in this case: vw_employees, select Export and select Text File.
The Wizard will appear. Type in the path to the location of the csv file and do not select any other options. Click OK.
Click on the Advanced button in the next pane.
Click on the Save As button to name the Specification
Change the name of the Specification in the box or leave it. Click OK to continue and then OK in the Export Specification box.
Click Next to Proceed.
Continue clicking Next until the last pane. Click Finish to complete the Process.
Generating the CSV File from MS Access
Part of the project will be to open the CSV file in Excel once it has been generated, so click Tools, References and add the Microsoft Excel Object Library to the project.
Add the code to the command button in the Access form created. This code uses a FileSystemObject to create, delete and test for Folder and File existence. An Excel Application object is used to open the file once it has been transferred to the file location. Note that the location is C:\temp as Windows7 permissions will not permit a file to be written to C:\.
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim sFile As String
Dim sFolder As String
Dim fs
Dim bTest As Boolean
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
Set oExcel = GetObject("", "Excel.Application")
If oExcel Is Nothing Then
Set oExcel = CreateObject("Excel.Application")
End If
sFile = "C:\temp\employees.csv"
sFolder = "C:\temp"
Set fs = CreateObject("Scripting.FileSystemObject")
'create the folder, if it does not exist
bTest = fs.folderExists(sFolder)
If bTest = False Then
fs.createFolder (sFolder)
End If
bTest = fs.FileExists(sFile)
If bTest = True Then
fs.deletefile (sFile)
End If
DoCmd.TransferText acExportDelim, "vw_employees Export Specification", "vw_employees", sFile, True
'test to see if file was generated.
bTest = fs.FileExists(sFile)
If bTest = True Then
oExcel.Workbooks.Open (sFile)
oExcel.Visible = True
End If
Set oExcel = Nothing
Set fs = Nothing
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Run the form and check the button
The file should open in Excel with the headers.
Creating the MS Access Export Specification - Fixed Width Option
There is also a Fixed Width Option that can be used when creating an Export Specification. Select Fixed Width prior to clicking Advanced as shown below.
Clicking Advanced brings you back to the Export Spec Screen where the Start point and width can be set.
Public Sub formattedTextFile()
On Error GoTo Err_formattedText_Click
Dim sFile As String
Dim sFolder As String
Dim fs
Dim bTest As Boolean
sFile = "C:\temp\employees.txt"
sFolder = "C:\temp"
Set fs = CreateObject("Scripting.FileSystemObject")
'create the folder, if it does not exist
bTest = fs.folderExists(sFolder)
If bTest = False Then
fs.createFolder (sFolder)
End If
bTest = fs.FileExists(sFile)
If bTest = True Then
fs.deletefile (sFile)
End If
DoCmd.TransferText acExportFixed, "vw_employeesTXT Export Specification", "vw_employees", sFile, True
'test to see if file was generated.
bTest = fs.FileExists(sFile)
If bTest = True Then
Shell "notepad.exe c:\temp\employees.txt", vbMaximizedFocus
End If
Set fs = Nothing
Exit_formattedText_Click:
Exit Sub
Err_formattedText_Click:
MsgBox Err.Description
Resume Exit_formattedText_Click
End Sub
View the opened file in Notepad from the Access Code execution.
