Results 1 to 4 of 4

vba to Export Multiple Sheets to xlsx File

  1. #1
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    63

    vba to Export Multiple Sheets to xlsx File

    I'm using Excel 2007. For years I've been using the following code to export to xls.

    Private Sub cmdExport1_Click()
    On Error GoTo Err_cmdExport1_Click
    Dim strFileName As String
    Dim strFilter As String
    Dim strFileDesc As String
    Dim strFileExt As String
    lblExport1.Visible = False
    strFileDesc = "Excel File (*.xls)"
    strFileExt = "R3*.xls"
    strFilter = ahtAddFilterItem(strFilter, strFileDesc, strFileExt)

    strFileName = ahtCommonFileOpenSave( _
    InitialDir:="C:\Users\myusername\Documents\My SAS Files\9.3\R3\OT", _
    Filter:=strFilter, _
    OpenFile:=False, _
    DialogTitle:="Save file as ... ", _
    Flags:=ahtOFN_HIDEREADONLY)

    DoCmd.RepaintObject acForm, "FrmMainMenu"
    DoCmd.Hourglass True

    ' 1 - Export exp_INS_Combos
    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "exp_INS_Combos", _
    strFileName, _
    True, _
    "INS_Combos"

    ' 2 - Export exp_INSData_M2
    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "exp_INSData_M2", _
    strFileName, _
    True, _
    "INSData_M2"

    ' 3 - Export exp_OM_Output
    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "exp_OM_Output", _
    strFileName, _
    True, _
    "OM_Output"

    ' 4 - Export exp_SAP_Maps
    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "exp_SAP_Maps", _
    strFileName, _
    True, _
    "SAP_Maps"

    ' 5 - Export exp_Sum_Merge
    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    "exp_Sum_Merge", _
    strFileName, _
    True, _
    "Sum_Merge"

    DoCmd.Hourglass False

    MsgBox "Export to Excel is Complete" & vbCrLf & _
    "FileName: " & strFileName & vbCrLf & _
    "Five (5) Sheets Created ", _
    vbInformation, "File Export Status: "
    lblExport1.Visible = True

    Exit_cmdExport1_Click:
    Exit Sub

    Err_cmdExport1_Click:
    ' MsgBox Err.Description
    DoCmd.Hourglass False
    Resume Exit_cmdExport1_Click
    End Sub

    I need to modify the code to export to xlsx, since some of the sheets now have more than 66k lines.
    I've changed acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12, but am unable to open the xlsx file that's created (Error: file format or file extension is not valid).
    Which TypeExcel should I use?

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,930
    The code still shows the old references for Excel9 and file extension .xls. What did you change these to - Excel12 and .xlsx? I did a test with Excel12 and .xls. The export works. I do get an error message when I open the workbook but I just respond Yes to open and all the data is there in the bigger sheet. Then do SaveAs.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    jhrBanker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    63
    Quote Originally Posted by June7 View Post
    The code still shows the old references for Excel9 and file extension .xls. What did you change these to - Excel12 and .xlsx?
    I wrote the following after the code, so maybe you didn't notice it:
    I need to modify the code to export to xlsx, since some of the sheets now have more than 66k lines.
    I've changed acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12, but am unable to open the xlsx file that's created (Error: file format or file extension is not valid).
    Which TypeExcel should I use?

    The acSpreadsheetTypeExcel12 didn't work.

    However, I have since gotten it to work by replacing the acSpreadsheetTypeExcel9 with just the number 10, like this:

    DoCmd.TransferSpreadsheet _
    acExport, _
    10, _
    "exp_INS_Combos", _
    strFileName, _
    True, _
    "INS_Combos"

    Thanks,
    jr

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,930
    Interesting, I think 10 is supposed to be Excel 2002.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-23-2013, 10:57 AM
  2. Replies: 12
    Last Post: 12-17-2012, 10:47 AM
  3. No Option to Export to .xlsx
    By laytonp in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2010, 11:50 AM
  4. export table to multiple sheets
    By TheShabz in forum Import/Export Data
    Replies: 5
    Last Post: 04-06-2010, 01:59 PM
  5. export to multiple sheets based on change in sort
    By mws5872 in forum Programming
    Replies: 0
    Last Post: 06-30-2009, 06:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums