Open Query and Export to Excel 1

[MacroAlan]

I have some code I cobbled together that sort of writes a RecordSet to Excel. What I really need is to write Query results to Excel so that I get my headings/field names.

Both Access and Excel are version 2007.

Private Sub ExportModel_Click()
    Dim intStart    As Integer
    Dim appXL       As Excel.Application
    Dim rst         As DAO.Recordset
    Dim DBs         As DAO.Database

 [COLOR=red]   'Exports data to Excel and formats for Asset Report[/color]
    Set appXL = New Excel.Application

    Set DBs = CurrentDb
    Set rst = DBs.OpenRecordset("qryMulti_model")

    appXL.Workbooks.Open "c:\book1.xlsx"

    appXL.Cells(1, 1).Select
    appXL.ActiveSheet.Range("A1").CopyFromRecordset rst
        
    With appXL
        .Worksheets(1).Select
        .ActiveSheet.Paste
     [COLOR=red]'   .ActiveSheet.UsedRange.Select[/color]
        .Selection.Copy
        .Range("C8").Select
        .Selection.PasteSpecial Transpose:=True
    End With
Set appXL = Nothing
   [COLOR=red]' When does Excel show me what it did ??[/color]

End Sub

I was using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
        "qryMulti_model", "test.xltm", -1

but it blew up worse. Does Access 2007 recognize the new file extensions in Excel?


Alan

 

[PWise]

Try

dim iCols as interger
For iCols = 0 To rst.Fields.Count - 1
    appXL .Worksheets(1).Cells(1, iCols + 1).Value = appXL .Fields(iCols).Name
Next
appXL .Worksheets(1).Range("a2").CopyFromRecordset rst

NOTE: I am appending the recordset to cell A2 not A1

 

[PHV]

When does Excel show me what it did ?
appXL.Visible = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

PHV:

Not always does one have to see what excell did. I use this code to create A excell Sheet and Email it.

 

[MacroAlan]

Here is what I came up with in the end. I know it says export to PDF -- our manager does not want us to us Excel, so I export to Excel, manipulate the output, export to PDF and delete the intermediate Excel file.

Private Sub ExportModel_Click()
    Dim ExpResp     As Long
    On Error GoTo ErrRout
    DoCmd.OpenQuery "qryMulti_model", acViewNormal
    [COLOR=red]'Exports data to Excel and formats for Assessor Report[/color]
    ExpResp = MsgBox("Would you like to Export to PDF now?", vbYesNo, "Export to Report")
    If ExpResp = 7 Then GoTo ExitRout
        [COLOR=red]'Perform export of query data to Excel[/color]
        DoCmd.RunSavedImportExport ("Export-qryMulti_model")
    Exit Sub
ExitRout:
    Exit Sub
ErrRout:
    MsgBox Err.Number & Err.Description & vbCrLf & " You probably have the last run open"
End Sub

Thanks to everyone for there assistance!


Alan