Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Exporting a variable number of queries to a single (multi-tabbed) Excel Spreadsheet

    Hi,



    I'm using the below function as a means of outputting a query onto an Excel Spreadsheet. The path, file name, query, and sheet name are defined as arguments within the function. I have a form in the database which passes that information through. This works perfectly, but now I'm trying to figure out how to modify this so that it can take multiple queries and output them to different sheets/tabs on that same workbook. I can obviously update the class module in the form to simply call the function multiple times but ideally I'd like it to be able to work for other forms in which there could be 3, 4, etc. queries that need to be outputted.
    Thoughts on how to proceed?

    Code:
    Public Function Export_To_New_Excel_Spreadsheet(strPath As String, strFileName As String, strQuery As String, strSheetName As String)
    
    
        Const xlCenter As Long = -4108
        Const xlBottom As Long = -4107
        
        On Error GoTo err_handler
     
        Set db = CurrentDb
        Set qdf = db.QueryDefs(strQuery)
    
    
            For Each prm In qdf.Parameters
                prm = Eval(prm.Name)
            Next prm
            
        Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    
    
        Set XLApp = CreateObject("Excel.Application")
        Set XLBook = XLApp.Workbooks.Add  'Creates new Excel Workbook
        XLApp.Visible = True
        
        XLBook.Worksheets("Sheet1").Name = strSheetName   'Renames "Sheet1" to strSheetName
        
        Set XLSheet = XLBook.Worksheets(strSheetName)
       
            XLSheet.Activate
            XLSheet.Range("A1").Select
        
            'Below code populates the header columns based upon the # of fields in the query or table
            For Each fld In rs.Fields
            
                XLApp.ActiveCell = fld.Name
                XLApp.ActiveCell.Offset(0, 1).Select
                
            Next
            
            rs.MoveFirst
            
            XLSheet.Range("A2").CopyFromRecordset rs   'Copies data from query or table to spreadsheet (starting on Cell A2)
     
            XLApp.ActiveSheet.Cells.Select  'selects all of the cells
            XLApp.ActiveSheet.Cells.EntireColumn.AutoFit  ' does the "autofit" for all columns
        
            XLBook.SaveAs FileName:=strPath & strFileName, _
            FileFormat:=xlOpenXMLWorkbook
        
            rs.close
            Set rs = Nothing
            
        Exit Function
        
    err_handler:
    
    
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function
        
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    vFile = "C:\mypath\files" & format(date(),"yyyy-mm-dd" & "_ExportFile.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,"Export"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsPayables", vFile, True,"payables"

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The big question is "How will you get the multiple queries and output them to different sheets/tabs on that same workbook?"
    I might use a table to store the query name and the worksheet name. Also the form name.

    tblExportInfo
    ------------
    ExpInfoID_PK (Autonumber)
    FormName (Text) - Name of the form so you could have multiple queries/WorkSheetNames
    QueryName (Text)
    WkSheetName (Text)


    Then you would need to loop through a recordset to get each query/worksheet name to export.

    Ken Snell's site is a good reference for exporting/importing to/from Access
    You are using "CopyFromRecordset", so look at this page

    If you get stuck, post back with your dB.........



    ================================================== ====================
    There are things you should fix before continuing on
    - open the IDE, click on TOOLS/Options and ensure that Require Variable Declaration is checked. This adds "Option Explicit" to NEW modules.
    - add the BLUE line below to EVERY module
    Code:
    Option Compare Database
    Option Explicit
    EVERY module should have these two lines at the top.

    - You should properly declare variables - none have been declared.
    - You should properly close/destroy variables.

    ----------------------
    The rule is
    "If you create it, destroy it"
    "If you open it, close it"

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2019, 04:39 PM
  2. Replies: 3
    Last Post: 08-13-2014, 11:07 AM
  3. Exporting two queries into separate sheets on one spreadsheet
    By sam.eade in forum Import/Export Data
    Replies: 2
    Last Post: 07-07-2014, 09:53 AM
  4. Replies: 4
    Last Post: 06-10-2012, 02:29 PM
  5. Replies: 7
    Last Post: 08-05-2011, 10:59 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
  •  
Other Forums: Microsoft Office Forums