Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

MS Access Create Form

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.

MS Access Export 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.

Select Destination for CSV Export Specification

Click on the Advanced button in the next pane.

MS Access Export Specification

Click on the Save As button to name the Specification

Details of MS access export 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.

Naming the MS Access Export Specification

Click Next to Proceed.

Continue to Next step in Export Spec

Continue clicking Next until the last pane. Click Finish to complete the Process.

Finish Export Specification

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.

Generating a CSV file from MS Access in Excel References

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

Run MS Access Export Form

The file should open in Excel with the headers.

Excel Export File from MS Access

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.

Text File

Clicking Advanced brings you back to the Export Spec Screen where the Start point and width can be set.

Export Spec Save Export Spec

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.

Notepad Export Spec from MS Access