MS-Access And Transfer SpreadSheet Command.

Introduction.

A very useful Command to transfer data between Microsoft Access and Excel using the Import/Export Options.  Here, we will concentrate on the Export aspect and what challenges we encounter after exporting the data, using some export Options out of several of them provided with this feature in MS-Access.

The simple VBA Command Syntax is:

Docmd.TransferSpreadsheet [Transfer Type],[SpreadSheet Type],[Input TableName/Query Name],[Output FilePath],True(HasFieldNames),Range,UseOA

1. The first parameter Transfer Type is either acImport or acExport.

2. For the second parameter SpreadSheet Type, inbuilt Options are available from 0 to 10, as an enumerated list, including transfer to Lotus Worksheets as well.

   The Enumerated List is given below:

   • acSpreadsheetTypeExcel12xml  -  10

   • acSpreadsheetTypeExcel12  -  9

   • acSpreadsheetTypeExcel9  -  8

   • acSpreadsheetTypeExcel8  -  8

   • acSpreadsheetTypeExcel7  -  5

   • acSpreadsheetTypeExcel5  -  5

   • acSpreadsheetTypeExcel4  -  6

   • acSpreadsheetTypeExcel3  -  0

   • acSpreadsheetTypeLotusWJ2  -  4

   • acSpreadsheetTypeLotusWk4  -  7

   • acSpreadsheetTypeLotusWk3  -  3

   • acSpreadsheetTypeLotusWk1  -  2

   You can use either the Enumerated List item or the numeric value it represents as the second parameter.

3. The input Table or Query Name must be the third parameter.

4. Next, the Output File Path Name.

5. Next, the parameter True indicates that the Field Names are to be output as the first Row Value in the Worksheet.

6. The optional Range parameter is used along with the acImport Option only.

7. The last optional parameter UseOA is not defined and not used.

Sample Transfer-Spreadsheet Command

Docmd.TransferSpreadSheet acExport,acSpreadSheetTypeExcel12xml,”Products”,”C:\My Documents\Book1.xlsx”,True

The Option acSpreadsheetTypeExcel3 to 9 creates Excel File versions compatible with Excel 97 – 2003 format with the XLS file extension, which can open in Excel 2007.  But, if we give the output file name with the .xlsx extension explicitly, then the output file cannot be opened in Excel 2007 or in higher versions.

The acSpreadsheetTypeExcel12 Option creates an Excel File with XLSB extension and opens in Excel 2007 and Higher Versions.  XLSB extension denotes that the worksheet is a Binary Coded File. When you have a large volume of records this format is ideal because of its reduced file size.

Option acSpreadsheetTypeExcel12xlm creates an Excel File with extension .xlsx and is compatible with Excel 2007 and above.

The output option acSpreadSheetTypeExcel9 or an earlier version, when selected the output, doesn’t look attractive because of its old-fashioned Office Theme.  Like the sample Screenshot given below:



We must open the output file in the current version of Excel and change the Format with the new Font and Font-size to make it look better and save it in the current version of the file.  Besides that if we explicitly add the .xlsx file extension,  to the target file parameter, assuming that the Target File will be created in Excel 2007 or higher Version Default Theme, the Excel file thus created will not open in Excel 2007 or higher versions.

But, with a small trick, we can solve all these problems and can save the output in the current version of Excel, whether it is 2007, 2010, 2013, or whatever version of Excel you have.  Doesn’t matter which version of WorkSheet Type you have selected in the TransferSpreadSheet command the output will be saved in the current version of Excel you have installed on your machine.

A Simple Solution.

1. Create an Excel Workbook in the Current version of Excel and Save the file in the target location.

2. Close the Workbook.

3. Execute the above TransferSpreadSheet command with the saved Workbook file Pathname as the target file parameter.  The output worksheet will be saved in the target Workbook in a new Worksheet.

4. When the WorkSheet is saved in the current Excel Version Workbook the Default Office Theme is automatically applied to the output WorkSheet and the Data Format looks better like the sample Image given below:


We have written three slightly different functions to save the TransferSpreadSheet Command's output WorkSheet(s) in three different ways.

The Export2ExcelA() Function.

The above Function creates a single WorkSheet as output in the Target WorkBook.

Public Function Export2ExcelA(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
On Error GoTo Export2ExcelA_Err
Dim tblName As String
Dim filePath As String
Dim xlsPath As String

Dim wrkBook As Excel.Workbook

'xlFileLoc = "D:\Blink\tmp2\"
'QryORtblName = "Products"

xlsPath = xlFileLoc & QryORtableName & ".xlsx"
If Len(Dir(xlsPath)) = 0 Then
    Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
End If
DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, QryORtableName, xlsPath, True

MsgBox "File: " & xlsPath & " Created ", , "Export2ExcelA()()"

Set wrkBook = Nothing
Export2ExcelA = xlsPath

Export2ExcelA_Exit:
Exit Function

Export2ExcelA_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelA()"
Export2ExcelA = ""
Resume Export2ExcelA_Exit

End Function

The Export2ExcelA() Function needs two parameters. The output Excel file’s target Path is the first parameter.  The second parameter is the input Table/Query name as the second parameter.  In this example, the function creates a WorkSheet using the Products Table and saves the output WorkSheet in a Workbook.

At the beginning of the Code, it checks the presence of an Excel file on the Disk with the specified name in the transfer spreadsheet command.  If not found then create a new WorkBook in the Current Version of Excel, with the same name of the input table/query name. The Workbook is then closed. If the specified file exists, then the Output Worksheet is saved in that WorkBook.

Suppose, we don’t create the current version of the Excel workbook and provide it as the target file for the Excel WorkSheet then what will happen?  Let us take a look at it.

• If we don’t specify the Excel file extension like C:\My Documents\Products and select the SpreadSheetxl9 output type option, then the command creates a new Excel file with an XLS extension, like Products.xls.

• If we explicitly give the .xlsx file extension in the pathname and the SpreadSheet output type selected is SpreadsheetTypexl9 then a Target Excel output file will be created with that file extension. But, the file will not open in Excel 2007 or in higher Versions.

• But, if the WorkBook C:\My Documents\myBook.xlsx already exists, then the output will be saved in that Workbook as a separate WorkSheet. In this case, the Worksheet will be formatted with the current Excel Version Default Office theme.

• This is the reason why we are creating a new WorkBook in the current version of Excel and saving it to the target location in advance. After saving the file we must close it and give the reference in the TransSpreadSheet Output file Path parameter.

• If the target Workbook is already in use then it will end up with an error message; Source File not found. The target file should not be in use and in an open state to avoid this problem.

In the next step, the Workbook Pathname is passed as a parameter to the TransferSpreadsheet command.


Creating Separate WorkSheets in a Single Workbook.

There are times we need to create separate worksheets for data, grouped on some criteria for distribution.  These probably need as separate WorkSheets in a single WorkBook or each WorkSheet in separate WorkBooks.

We have used the Products Table from the Northwind sample database, for grouping of records on Product Category. 

The Export2ExcelB() Function VBA Code:

Public Function Export2ExcelB(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelB_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

    xlsName = QryORtableName & ".xlsx"
    xlsPath = xlFileLoc & xlsName
    
If Len(Dir(xlsPath)) > 0 Then
    Kill xlsPath
End If

    Set wrkBook = Excel.Workbooks.Add
    wrkBook.SaveAs xlsPath
    wrkBook.Close
        
Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel WorkSheets Created " & vbCr & "in Folder: " & xlsPath, , "Export2ExcelB()"
    Set wrkBook = Nothing
    Export2ExcelB = xlsPath
    
Export2ExcelB_Exit:
Exit Function

Export2ExcelB_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelB()"
Export2ExcelB = ""
Resume Export2ExcelB_Exit
End Function

The above Code creates a WorkBook and saves the file in the specified target location and then closes the Workbook.

We have put the WorkBook creation code above, in the For . . . Next Loop and created only a single workbook, to save all the output Worksheets for Products Group in the same WorkBook. 

All Output Worksheets in Different Workbooks.

In this case, we will shift the Excel Workbook creation Code Segment within the For . . . Next Loop. It creates a different WorkBook, for each output Worksheet for the products group, and passes the WorkBook reference to the Transfer Spreadsheet Command. All Worksheets will be saved in a separate Excel Workbook in the next Function.

The Export2ExcelC() Function VBA Code:

Public Function Export2ExcelC(ByVal xlFileLoc As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelC_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh

        xlsName = qryName & ".xlsx"
        xlsPath = xlFileLoc & xlsName
        Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel Files Created " & vbCr & "in Folder: " & xlFileLoc, , "CreateXLSheets()"
    Set wrkBook = Nothing
    Export2ExcelC = xlFileLoc & qryName & ".xlsx"

Export2ExcelC_Exit:
Exit Function

Export2ExcelC_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelC()"
Export2ExcelC = ""
Resume Export2ExcelC_Exit
End Function

A Demo Database with all the three Function Codes with sample Data of Products table and Queries is attached for Download.

Download TransferSpreadSheet1.zip




1. Running-Sum in MS-Access Query
2. Opening Access Objects from Desktop
3. Diminishing Balance Calc in Query
4. Auto Numbers in Query Column Version-2
5. Word Mail-Merge With Ms-Access Table

Read More