Export Access Query to Specific Worksheet

[mrmmickle1]

Background:

I have an Excel Macro Enabled Template that has a number of dynamic reports on it based on different pivot tables...etc. The file has a fair amount of code and a lot of formatting to make it look "professional"....etc. In the Access Database I run an backup data export currently. That has about 13 different worksheets. The challenge is that I now need to export specific queries to specific worksheets in my new macro enabled template. I am having trouble getting this to work right. Can someone please help me to straighten out my code. I have used only one query in this example. (If someone can help me to do it once I can figure the rest out )

This is my current code that does not work right.... I'm trying to avoid going through every record and exporting each field one at a time so I would like to use .CopyRecordSet if possible. The reason I want to do this si because the queries are quite large and may sometimes contain 30,000 + rows...

Code fails on Red Lines... however I would be interested in some feedback. Not sure this is the best way of doing things....

[COLOR=#0000ff][B]Sub [/B][/COLOR]ExportToTemplatev1()


    [COLOR=#0000ff][B]Dim [/B][/COLOR]xl                [COLOR=#0000ff][B]As[/B][/COLOR] Excel.Application
    [COLOR=#0000ff][B]Dim[/B][/COLOR] xlFilePath     [B][COLOR=#0000ff]   As String[/COLOR][/B]
   [COLOR=#0000ff][B] Dim[/B][/COLOR] xlWS              [COLOR=#0000ff][B]As[/B][/COLOR] Worksheet
    [COLOR=#0000ff][B]Dim[/B][/COLOR] rng              [B][COLOR=#0000ff] As Range[/COLOR][/B]
  [COLOR=#0000ff][B]  Dim [/B][/COLOR]i                [COLOR=#0000ff][B] As Long[/B][/COLOR]
  [COLOR=#0000ff][B]  Dim[/B][/COLOR] lFieldCount      [COLOR=#0000ff][B] As Long[/B][/COLOR]
   [COLOR=#0000ff][B] Dim[/B][/COLOR] rsQuery           [COLOR=#0000ff][B]As[/B][/COLOR] Recordset
    
    [COLOR=#0000ff][B]Set[/B][/COLOR] xl = New Excel.Application
    xlFilePath = "C:\Users\mmickle1\Documents\SpotlightExportTest.xlsm"
    
    Workbooks.Open xlFilePath
    xl.Visible = [COLOR=#0000ff][B]True[/B][/COLOR]
    
[COLOR=#008000][B]    'Set the worksheet:[/B][/COLOR]
   [COLOR=#0000ff][B] Set[/B][/COLOR] xlWS = ActiveWorkbook.Sheets("Sheet1")
    
[COLOR=#008000][B]    'open query[/B][/COLOR]
[COLOR=#ff0000][B]    Set rsQuery = CurrentDb.OpenRecordset("01 HC-Most Recent Quarter", dbOpenDynaset)[/B][/COLOR]
    
    [COLOR=#0000ff][B]Set [/B][/COLOR]rng = ws.Range("A1")
    lFieldCount = rsQuery.Fields.Count
     
    [COLOR=#0000ff][B]For[/B][/COLOR] i = 0 [COLOR=#0000ff][B]To[/B][/COLOR] lFieldCount - 1
[B][COLOR=#008000]    'copy column names in first row of the worksheet:[/COLOR][/B]
    rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
   [COLOR=#0000ff][B] Next[/B][/COLOR] i
    
[COLOR=#008000][B]    'copy record values starting from second row of the worksheet:[/B][/COLOR]
    rng.Offset(1, 0).CopyFromRecordset rsQuery
    
    rsQuery.Close


[COLOR=#0000ff][B]End Sub[/B][/COLOR]

Thank you for any help !

 

[alansidman]

May Bob's code will do the work for you:

Export A Table Or Query To Excel – To Specific Worksheet | BTAB Development

 

[mrmmickle1]

Alan,

Thanks for the reference website. After looking at it and thinking about the error I was getting I searched a little more and was able to get it working...

I was unaware that I needed to specify the query parameters even though my form was open in the database. When I added the parameters the code runs fine:

    [COLOR=#0000ff][B]Set [/B][/COLOR]db = CurrentDb()
    [COLOR=#0000ff][B]Set[/B][/COLOR] qdf = db.QueryDefs("01 HC-Most Recent Quarter")
        qdf.Parameters("[forms]![frm001-Export]![DateEntry]") = [Forms]![frm001-Export]![DateEntry]
        qdf.Parameters("[forms]![frm001-Export]![Sequence1]") = [Forms]![frm001-Export]![Sequence1]
        qdf.Parameters("[forms]![frm001-Export]![Sequence2]") = [Forms]![frm001-Export]![Sequence2]
        qdf.Parameters("[forms]![frm001-Export]![Sequence3]") = [Forms]![frm001-Export]![Sequence3]
        qdf.Parameters("[forms]![frm001-Export]![Sequence4]") = [Forms]![frm001-Export]![Sequence4]
    [COLOR=#0000ff][B]Set[/B][/COLOR] rst = qdf.OpenRecordset