Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009

    Question Unanswered: Help: Macro to Run query with param and export to excel

    Im new to this so please excuse my inexperience.

    I need this macro to open up a query, the query has paramaters that i want to pass in the macro, then export it to an excel file, then run again with the second set of paramaters and append the query to the first excel file as a new sheet.

    I will be doing this frequently and running entering and exporting manually is tiresome and time consuming.


    My attempt thus far:

    Sub test()

    Dim ParamArr As Integer
    ParamArr = Array(0, 50, 100, 125, 150, 175, 200, 300, 400, 500)

    DoCmd.SetWarnings False

    For i = 1 To ParamArr.Count
    'Want to pass i and i+1 each time, untill the end it will be 500 and the second param will be empty as i want 500+
    'Also instead of overwriting the xls file each time append new query as a new worksheet
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "sample", "C:\Documents and Settings\me\test.xls", True
    DoCmd.SetWarnings True

    End Sub

  2. #2
    Join Date
    May 2005
    Welcome to the forums!

    Poots has a post in the code bank of Excel Automation that might be of some use to you.

    Based on that, I've done some work like:
        On Error resume next
        'instantiate variables
        Dim db As DAO.Database, _
            rs As DAO.Recordset
        Dim exlApp As Excel.Application, _
            exlBook As Excel.Workbook, _
            exlSheet As Excel.Worksheet
        Dim strFilePath As String, _
            strExlSheetName As String, _
            strFormCurrency As String
        Dim intColCnt As Long, _
            intRowCnt As Long, _
            i As Long, _
            j As Long
        'Prepare recordset
        Set db = Application.CurrentDb
        Set rs = db.OpenRecordset(strSQL)  'define this on looping
        'Write data to file
        'Populate recordset to get accurate record count
        If Not rs.EOF Then rs.MoveLast
        'Note: Excel arrays and cells start at 1, not 0, don't deduct 1
        intColCnt = rs.Fields.Count
        intRowCnt = rs.RecordCount
        i = 1
        'Write column headings
        For j = 0 To (intColCnt - 1)
            exlSheet.Cells(1, j + 1).Value = Nz(rs.Fields(j).Name, "")
        Next j
        'Write recordset data
        i = 2
        While Not rs.EOF
            For j = 0 To (intColCnt - 1)
                exlSheet.Cells(i, j + 1).Value = Nz(rs.Fields(j).Value, "")
            Next j
            i = i + 1
    I'm sure this could be taken and adapted to your particular needs. You'll just have to put this in your loop and change the SQL every time.

    FYI, when I copied this code over I omitted a bunch of other stuff, so I'm not guaranteeing this code will work "as-is", but it should be enough to give you a good start.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts