Introduction.
We already learned how to use MS Access live data in Excel earlier. Another VBA-based procedure was created to export the Table/Query data in the current version of the Excel Workbook. The main advantage, when the exported data is saved in the current version of Excel Workbook, the default Theme of data formatting features is automatically applied, rather than the Excel 2003 formatting used by the acSpreadSheetTypeExcel3 to 9 range of Parameters, by default.
We could export filtered data through multiple query criteria and save the data sets in separate Worksheets in a single WorkBook. A modified version of this method filters data on multiple criteria and exports the records into separate Workbooks too.
Excel Tools are superb for Worksheet analysis, Charts, and Reports with automation features. But, Excel is not made as a Database Management System. When the history of data becomes crucial for decision making, budget projections, business target settings and so on Excel users will look for a better database management system, like MS Access with flexibility.
But, exporting the complete set of Tables from Access to Excel Workbook may not be an everyday requirement.
Exporting All Access Tables Into Excel Workbook.
But, for some reason or the other, if it becomes necessary, you can use the VBA Program given below.
The VBA Program ExportAllTables2Excel()
Copy and Paste the Code given below into the Standard Module of your Project.
Public Sub ExportAllTables2Excel()
'----------------------------------------------------------------
'Program : ExportAllTables2Excel
'Purpose : Export All Access Tables into Excel WorkBook
'Author : a.p.r. pillai
'Rights : All Rights Reserved by www.msaccesstips.com
'Remarks : Creates separate WorkSheets for each Table
' : in a single WorkBook.
' : Table Name is Worksheet Name
'----------------------------------------------------------------
Dim db As Database
Dim xlsFileLoc As String
Dim xlsName As String
Dim xlsPath As String
Dim Tbl As TableDef
Dim tblName As String
Dim j As Integer
Dim wrkBook As Excel.Workbook
On Error GoTo Export2Excel_Err
xlsFileLoc = CurrentProject.Path & "\"
xlsName = "AllTables.xlsx"
xlsPath = xlsFileLoc & xlsName
If Len(Dir(xlsPath)) > 0 Then
Kill xlsPath
End If
Set wrkBook = Excel.Workbooks.Add
wrkBook.SaveAs xlsPath
wrkBook.Close
Set db = CurrentDb
j = 0
For Each Tbl In db.TableDefs
tblName = Tbl.Name
If Left(tblName, 4) = "MSys" Then 'System Tables
GoTo nextstep
Else
j = j + 1
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
If Err > 0 Then
Err.Clear
debug.print tblName
j = j - 1
Resume nextstep
End If
End If
nextstep:
Next
On Error GoTo Export2Excel_Err
MsgBox j & " Table(s) Exported to File:" & vbCr & xlsPath, , "Export2Excel()"
Set wrkBook = Nothing
Set db = Nothing
Export2Excel_Exit:
Exit Sub
Export2Excel_Err:
MsgBox Err & " : " & Err.Description, , "Export2Excel()"
Resume Export2Excel_Exit
End Sub
Caution: You may compare the worksheet's Maximum Rows available (press End-Key then Down-Arrow Key) in your version of Excel Worksheet to the Access Table with the maximum number of records. If any of the tables have more than the Excel worksheet Rows, then there are chances that the extra records may overflow into a second Worksheet or may get lost in the process. The above code is not tested for this and use it at your own risk.
Before going through the Code you may attach the Microsoft Excel Object xx.0 Library File to your Access Project, before attempting to compile the above VBA Code.
Open the VBA Editing Window (Alt+F11).
Select References from Tools Menu
Look for Microsoft Excel 16.0 Object Library file in the list.
Put a check mark, on the left side of the file, to select it.
Click OK to close the Object Library files list.
The VBA Code Review.
At the beginning of the Code, the required Variables are declared.
The xlsFileLoc variable is initialized with the Database Path, and we will be creating a new Workbook, in the current version of Excel you have, in this location to save the Access Tables.
The Workbook name will be AllTables.xlsx, if you would like to give a different name then change it.
The xlsPath Variable is initialized with the Workbook Path and filename.
In the next three steps, we create a new Excel Workbook with the name AllTables.xlsx, save it in the specified location, and close the file. If the file already exists there, then a warning message is displayed. You may either choose to overwrite the existing file or Cancel the export procedure and retain the existing Excel Workbook contents.
NB: But, the Excel file should not be kept open while exporting the Access Tables. If it is kept open then the export operation will fail. Check the following TransferSpreadsheet Command:
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel12Xml, TblName, xlsPath, TrueThe parameter xlsPath value is where we give a specific Excel Workbook File reference to save the output data. If the Workbook File already exists, then the output will be written into a separate Worksheet in that file. If you omit this parameter, then it creates a target Work Sheet File based on the second parameter acSpreadsheetTypeExcel12Xml selection, one out of several options available to select from.
Creating a Workbook in the current version of Excel and giving it as the Target Workbook for the output will save all the Tables in separate Worksheets, in the same Workbook, rather than creating every table output creating in a separate Workbook. Besides that, the output data will be formatted with the current Excel version default Office theme.
Note: The Table Export option normally creates an Excel 2003 file automatically, based on one of the export options acSpreadsheetTypeExcel3 - 9 selected and the output data formatting may not be as pleasing as the current version of Excel that you have. This topic was discussed in detail in an earlier post with the Title: MS Access And Transfer Spreadsheet Command and you may go through it for a better understanding of this procedure, different versions of options available and their output formats, and other important points to note.
In the next step, the Current Database object is assigned to the Object Variable db. The Variable j is used to take a count of Tables, exported into the Excel Workbook.
We can get all the Table names from the TableDefs collection of our Database Object and can pass these names to the Docmd.TransferSpreadSheet Command to transfer the records.
But, besides the user-created Tables, there are some System Tables also (even though they are normally kept hidden) in this collection of TableDefs. Fortunately, all System Table names start with the letters MSys and we can identify them easily and stop outputting their contents into Excel.
j = 0
For Each Tbl In db.TableDefs
tblName = Tbl.Name
If Left(tblName, 4) = "MSys" Then 'System Tables
GoTo nextstep
Else
j = j + 1
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel12Xml, tblName, xlsPath, True
If Err > 0 Then
Err.Clear
Debug.Print tblName
j = j - 1
Resume nextstep
End If
End If
nextstep:
Next
In the above For Each ... Next statement reads each Table definition from the TableDefs collection in the TableDef object Tbl.
The next statement reads the name of the Table from the Name Property into the String Variable tblName.
The next statement checks whether the first four characters of the table name match the text MSys or not. If it does, then it is a System Table and the program skips the remaining part of the Code and goes for taking the next table to validate.
If it is a valid user-created Table then the counter Variable j is incremented by one. The next Error capture line was added as a precaution to check if anything goes wrong with the TransferSpreadSheet Command.
Next, the TransferSpreadSheet command is executed and the Records from the Table are transferred into the Excel WorkSheet, if it is a valid table, with the Field Names placed on the first row of the worksheet.
If the above operation failed due to some unforeseen Error, we ignore that table and go to the next table in line, after the table counter variable is reset to the earlier successful operation count. The name of the Table in Error is dumped into the Debug Window and you may check and take corrective actions on it later. If something happened to a single table, we don't want to stop our operation and expect other tables will output normally.
When finished with the exporting operation, a message is displayed with the count of Tables exported successfully into Excel WorkBook.
You may download the Demo Database attached here and try it right away.
ExportAllTables.zip
