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, 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.

  8. 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:

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


    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:

Translate

PageRank

MSA GURU : Access Tips & Tricks App

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

Newsletter


Feed

Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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 Android App 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