How to Use VBA to Export Microsoft® Access Data to Excel
Last updated on 2024-05-15.
Preface
For many of the databases I develop I include an export-to-Excel capability. This is for clients who want the means of dumping data so that they can do their own thing without risk to the primary data.
This article describes a VBA procedure for dumping data from Access into Excel, exploiting the DoCmd.TransferSpreadsheet method to take you beyond the explanation of the workings of this method that you read in many websites.
Initial Considerations
Compounded from client requirements over the years, I've identified these are the main features of an export-to-Excel capability:
- Automated export of data sets from queries
- Export directed to workbooks in folders of the client's choice
- Automated formatting of worksheets on completion of an export
- The possibility of users creating their own queries.
All DMW databases supplied to clients are split — Front End (FE) and Back End (BE). FEs contain queries, forms, reports, macros and modules, and, as appropriate, a table or two, as I'll explain below.
BEs contain tables only.
From the viewpoint of a developer, these are considerations:
- Users not to be allowed to modify the structure of the BE
- Users not to be allowed to alter the FE
- Provision for users to create their own queries
- Provision for incorporation of users' own queries into FE
- Give suitable control of the process to the client's IT.
Were users to be allowed to tinker, it would be an impractical and time-consuming task for me to provide upgrades and on-gong support to the client. And, the client would not be happy with the bill.
VBA TransferSpreadsheet Method
Our procedure sExportToExcel to export the data uses the TransferSpreadsheet method to export the contents of a table, or of a query datasheet, as a named Excel file to a named folder:
Sub sExportToExcel(query$, path$)
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=query$ ,_
FileName:=path$, _
HasFieldNames:=True
End Sub
For example
sExportToExcel("qsResults", "S:\Reports\Results.XLSX")
exports the contents of the query qsResults to the folder S:\Reports\ as an Excel file named Results.XLSX.
Programming for Opening the Exported Workbook
Thus far our procedure creates the workbook and saves it. Experience suggests that most users want to see the results of the export as soon as it's complete.
So, the next process in ExportToExcel is to display the completed workbook.
Sub sExportToExcel(query$, path$)
Dim xlApp As Object, wkbk As Object
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=query$, _
FileName:=path$, _
HasFieldNames:=True
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set wkbk = .Workbooks.Open(path$)
End With
End Sub
Error Handling
At this point we will include error handling and make sure that the procedure releases any connection with Excel once it has presented the workbook:
Sub sExportToExcel(query$, path$)
On Error Goto errHandler
Dim xlApp As Object, wkbk As Object
Dim msg$, icon&, title$
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=query$, _
FileName:=path$, _
HasFieldNames:=True
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set wkbk = .Workbooks.Open(path$)
End With
procDone:
Set wkbk = Nothing
Set xlApp = Nothing
Exit Sub
errHandler:
title$ = "DATA EXPORT ERROR"
icon& = vbOKOnly + vbCritical
msg$ = _
"Please take screen clip of this message." & _
vbNewline & vbNewLine & _
"If not, make note of following details." & _
vbNewline & vbNewLine & _
"Calling Proc: ExportToExcel" & _
vbNewLine & _
"Error Number: " & Err.Number & _
vbNewLine & _
"Description: " & Err.Description
MsgBox msg$, icon&, title$"
Resume procDone
End Sub
Path and Workbook Naming
You need to take care when supplying values to the path$ argument of sExportToExcel(query$, path$). Consider these values:
"S:\Reports\Results.XLSX" This works satisfactorily — the Workbook named Results.XLSX is directed to the S:\Reports\ folder.
"S:\Reports\Results" This works satisfactorily too — the procedure attaches the .XLSX extension to the workbook's name so that Results.XLSX is directed to the S:\Reports\ folder.
"S:\Reports\Results\" Here the problem arises that the final \ causes the procedure to treat S:\Reports\Results\ as a folder, without specifying any workbook at all, with resulting error conditions, e.g. —
In the full-blown export program described below there is code to contend with this issue.
Program Refinement — Providing for Folder Changes
In my experience, clients like to determine for themselves to which folder the export process directs the workbooks it creates.
This is a particular requirement when a client's IT want the freedom to change locations for files on a network and to re-map drives.
One method of providing for this uses an addition to the two main FE and BE files. This third file I name KEY.ini, which is a simple text file, the content of which is this:
Important
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This file to be placed in same folder as USER file
Edit ExportPath to correspond to your folder structure
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DEFAULT]
ExportPath = "S:\Reports\"
Now we need to accommodate KEY.ini into the export process. The process must pick out the export path from KEY.ini.
In tune with good practice we are going to structure our programming by separating our code into a number of subroutines. Each of these will perform a distinct operation, one of which will be the picking from KEY.ini.
The Controlling Procedure
This is the sequence of routines that the controlling program, named sExportData, will call:
fnGetPathFromKey This function subroutine will look for KEY.ini and get the export path from it. If fnGetPathFromKey cannot locate KEY.ini, or is unable the find the information about the export path, then it will return an error message to sExportData.
fnCheckPath This function subroutine will seek to confirm the existence of the folder to which dumps are to be directed. If fnCheckPath cannot locate that folder it will report the folder as missing to sExportData.
fnExportToExcel This will complete the export program passing data from Access and into Excel, the opening Excel to display the data, and the formatting of the worksheet.
Get Path from KEY File Subroutine
The job of the subroutine fnGetPathFromKey is to retrieve the path of the back-end DATA file from KEY.ini:
Function fnGetPathFromKey(pathINI$, element$) As String
On Error GoTo errHandler
Dim i&, lenElement&
Dim fstChar34%, lstChar34%
Dim lineINI$, path$
If Len(Dir(pathINI$)) > 0 And Len(element$) > 0 Then
lenElement& = Len(element$)
i& = FreeFile()
Open pathINI$ For Input As #i&
Do While Not EOF(i&)
Line Input #i&, lineINI$
If Left(lineINI$, lenElement&) = element$ Then
path$ = Mid(lineINI$, lenElement& + 1)
Exit Do
End If
Loop
Close #i&
fstChar34% = InStr(path$, Chr(34)) + 1
lstChar34% = InStrRev(path$, Chr(34))
path$ = Mid(path$, fstChar34%, lstChar34% - fstChar34%)
Else
path$ = "Error"
End If
procDone:
fnGetPathFromKey = path$
Exit Function
errHandler:
path$ = "Error"
Resume procDone
End Function
If it's unable to return the whereabouts of DATA, then fnGetPathFromKey warnings from which sExportData composes messages to the user.
Check Path Subroutine
The job of the second subroutine, fnCheckPath, in the export program is to confirm that the folder specified in KEY.ini actually exists:
Function fnCheckPath(path$) As String
On Error GoTo errHandler
Dim msg$
If Dir(path$, vbDirectory) = "." Then
msg$ = vbNullString
Else
msg$ = _
"No folder matches entry in KEY file"
End If
procDone:
fnCheckPath = msg$
Exit Function
errHandler:
msg$ = Err.Description
Resume procDone
End Function
fnCheckPath returns a null string if the folder is in place, or a message if that folder is missing or that the program cannot find it at the anticipated location.
Export to Excel Subroutine
My clients most like to see these actions performed by the export program:
- Formatting of column headings
- Application of a currency format to columns, where appropriate
- Application of a date format to columns, where appropriate
- Adjustment of columns widths to suit content
- Appropriate naming of the worksheet's tab.
The third and final subroutine in the program is fnExportToWorkbook. This routine contains lines of code aimed at smartening up the contents of the worksheet created by the export:
Function fnExportToWorkbook( _
query$, path$, _
fileName$, wksName$, _
colsCurrency$, colsDate$ _
) As String
On Error GoTo errHandler
Dim xlApp As Object, wkbk As Object, wks As Object
Dim file$
Dim formatCur$, formatDate$, intColor&
Dim arrayCols() As String, col$, n%, i%, w!
Dim cell As Range
Dim msg$
' Worksheet formats
formatCur$ = "£#,##0.00"
formatDate$ = "yyyy-mm-dd"
intColor& = RGB(100, 200, 200)
' Create workbook
file$ = path$ & fileName$
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=query$, _
FileName:=file$, _
HasFieldNames:=True
' Open workbook
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set wkbk = .Workbooks.Open(file$)
End With
' Format worksheet
Set wks = wkbk.worksheets(1)
With wks
.Name = wksName$
' Currency columns
arrayCols = Split(colsCurrency$, ",")
For i = LBound(arrayCols) To UBound(arrayCols)
With .Columns(arrayCols(i))
.NumberFormat = formatCur$
End With
Next i
' Date columns
arrayCols = Split(colsDate$, ",")
For i = LBound(arrayCols) To UBound(arrayCols)
With .Columns(arrayCols(i))
.NumberFormat = formatDate$
End With
Next i
' Filters
With .Range("A1")
.Select
.autofilter
End With
' Column width adjustments
With .Cells
.Select
.EntireColumn.AutoFit
End With
n% = .Cells(1, 1).End(xlToRight).Column
For i% = 1 To n%
With .Cells(1, i%)
w! = .EntireColumn.ColumnWidth
.EntireColumn.ColumnWidth = w! + 4
.HorizontalAlignment = xlCenter
.Interior.Color = intColor&
.Font.Bold = True
End With
Next i%
End With
With xlApp.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
msg$ = vbNullString
procDone:
Set wks = Nothing
Set wkbk = Nothing
Set xlApp = Nothing
fnExportToWorkbook = msg$
Exit Function
errHandler:
msg$ = _
Err.Number & ": " & Err.Description
Resume procDone
End Function
Completed Export to Excel Program
This is the controlling procedure that pulls together the subroutines to perform the export to Excel:
Sub sExportData(query$, fileName$, wksName$, _
colsCurrency$, colsDate$)
On Error GoTo errHandler
Dim bln As Boolean
Dim path$
Dim msg$
path$ = Left(CurrentProject.FullName, _
InStrRev(CurrentProject.FullName, "\"))
path$ = path$ & "KEY.ini"
path$ = fnGetPathFromKey(path$, "ExportPath")
Select Case path$
Case "Error"
msg$ = "Unanticipated error locating KEY"
bln = False
Case Else
bln = True
End Select
If bln Then msg$ = fnCheckPath(path$)
If msg$ = vbNullString Then
msg$ = fnExportToWorkbook( _
query$, path$, _
fileName$, wksName$, _
colsCurrency$, colsDate$)
Else
msg$ = _
"Folder for exports cannot be located. " & msg$
End If
procDone:
If msg$ <> vbNullString Then
MsgBox msg$, vbExclamation, "DATA EXPORT TO EXCEL"
End If
Exit Function
errHandler:
msg$ = _
"Please take screen clip of this message." & _
vbNewline & vbNewLine & _
"If not, make note of following details." & _
vbNewline & vbNewLine & _
"Process: sExportData>" & _
vbNewLine & _
"Error Number: " & Err.Number & _
vbNewLine & _
"Description: " & Err.Description
Resume procDone
End Function
This is how you might provide values for the arguments of sExportData:
query$ as “qsExportSalesByMonth” — your database query
fileName$ as “Export Sales” — the workbook file's name.
wksName$ as “Feb 2020” — the worksheet's name
colsCurrency$ as “F:F” — Column F currency format
colsDate$ as “C:C,D:D” — Columns C and D date format.
Each argument value must be enclosed in the quotation marks as shown above.
Check Your References
Thanks to Marek, who on 2020-10-14 commented wisely on an ommission from my article at the time:
“Just in case this is of any help. I'm looking at your Export to Excel procedures on [this web page].
“I'm not sure if it would be helpful to add the need to add the reference to the Microsoft Excel Object Library as my version of Access was missing this.
“Kind regards and thank you for sharing your code.”
In your code, Access is making calls to Excel. For it to work you need to tell Access that you're using Excel. This is how you tell Access:
- Go into the Visual Basic Editor in which you've written your code
- From the Tools menu, choose References to display the References dialog box –
- If Microsoft Excel ##.# Object Library is not included in your list of ticked references, scroll down the list and tick it there
- Click OK
- From the Debug menu, choose Compact.
The fifth action checks whether or not Access has got the hang of things.
Donation
Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
Thanks, in anticipation.