Open Excel after transfering a query

[Bobp3114]

I am using : DoCmd.OutputTo acQuery, "qryRepreportCompany3", "MicrosoftExcel(*.xls)" to open the query results in Excel. This works OK but I would like to open the spreadsheet automatically once it is created. Saving is not necessary.
All help appreciated

 

[theDBguy]

I think OutputTo has an argument for doing that. Set it to True - it's usually the last argument. it's called the AutoStart argument.

DoCmd.OutputTo method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Bobp3114]

Thanks theDBguy
DoCmd.OutputTo acQuery, "qryRepreportCompany3", "MicrosoftExcel(*.xls)", , True works fine. Saves the file, and while this is Ok=K, an option to NOT SAVE would be great
Thanks for your prompt reply
Bob

 

[theDBguy]

Thanks theDBguy
DoCmd.OutputTo acQuery, "qryRepreportCompany3", "MicrosoftExcel(*.xls)", , True works fine. Saves the file, and while this is Ok=K, an option to NOT SAVE would be great
Thanks for your prompt reply
Bob

Hi Bob. Are you saying you don't want an Excel file created?

 

[Bobp3114]

I want the user to see the file but not to save it necessarily. I assume they would be able to choose to save if they wished.

 

[theDBguy]

I want the user to see the file but not to save it necessarily. I assume they would be able to choose to save if they wished.

In that case, you will have to use automation. Let me find you a link. Stand by...

 

[June7]

Would require Excel automation - not OutputTo nor TransferSpreadsheet. Something like:

Public Sub ExportToExcel()
Dim xlApp As Excel.Application, xlWB As Excel.Workbook
Dim rs As DAO.Recordset
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set rs = CurrentDb.OpenRecordset("qryRepreportCompany3")
    xlWB.Sheets("Sheet1").Range("A1").CopyFromRecordset rs
    xlApp.Visible = True
End Sub

More about import/export Excel from Access VBA https://accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[arnelgp]

you also need to Add the Column (field) header first:

Public Sub ExportToExcel()
Dim xlApp As Object, xlWB As Object
Dim rs As DAO.Recordset
Dim fld As Field, i As Integer

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set rs = CurrentDb.OpenRecordset("qryRepreportCompany3")
    With xlWB.Sheets("Sheet1")
        For Each fld In rs.Fields
            .Range("A1").Offset(0, i) = fld.Name
            i = i + 1
        Next
        .Range("A2").CopyFromRecordset rs
    End With

xlApp.Visible = True
End Sub