Dummy VBA question - Excel Output (1 Viewer)

[Nevsky78]

Hi all,

I've scoured the internet for answers to this as I thought it would be quite simple to sort out.

I have the following VBA code:

Private Sub RunSalesandOrdersbutton_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Step 1", acViewNormal, acEdit
    DoCmd.Close acQuery, "Step 1"
    DoCmd.OpenQuery "Step 2", acViewNormal, acEdit
    DoCmd.OutputTo acOutputTable, "SalesandOrders", "Microsoft Excel Workbook (*.xlsx)", "R:\Access\Reports\Operations\SalesandOrders.xlsx", False, "", 0, acExportQualityPrint
    DoCmd.Quit acSave
    DoCmd.SetWarnings True
End Sub

I thought I had covered the fact that there are more than 65535 rows of data in the "SalesandOrders" table by specifying the Output to Excel 2007. However, when I open the "SalesandOrders.xlsx" file, it only goes up to 65535 rows, as if it were Excel 2003.

I really can't work out why it's doing it, so any help would be much appreciated!

Regards,
Nick

 

[DCrake]

Whilst in 2007 you can click on the query and select External Data from the ribbon and export to Excel. Follow the steps then at the end save the actions. Then next time use DoCmd to run the saved export.

 

[Nevsky78]

Cheers David, worked fine!