Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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, in-built Options are available from 0 to 10, as an enumerated list, including transfer to Lotus Worksheets as well.

    The Enumerated List is given below:

    1. acSpreadsheetTypeExcel12xml  -  10
    2. acSpreadsheetTypeExcel12  -  9
    3. acSpreadsheetTypeExcel9  -  8
    4. acSpreadsheetTypeExcel8  -  8
    5. acSpreadsheetTypeExcel7  -  5
    6. acSpreadsheetTypeExcel5  -  5
    7. acSpreadsheetTypeExcel4  -  6
    8. acSpreadsheetTypeExcel3  -  0
    9. acSpreadsheetTypeLotusWJ2  -  4
    10. acSpreadsheetTypeLotusWk4  -  7
    11. acSpreadsheetTypeLotusWk3  -  3
    12. 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 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 .XLS 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 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:

transfer SpreadSheet

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 in 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 is 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.

This 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 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 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, the WorkBook C:\My Documents\myBook.xlsx if already exist 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 saves 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

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

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 a different WorkBook.

We have used the Products Table of Northwind.accdb 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 the For . . . Next Loop and creates only a single workbook and saves all the Output workSheets created for Products Group in the same WorkBook. 

All Output Worksheets in Different WorkBook.

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 products group, and passes the WorkBook reference in 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 Code with sample Data of Products table and Queries is attached for Download.


  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
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code