asked on

Appending Access Queries to Excel Sheet

I was wondering if it was possible to run a macro in Access to append these queries into one Excel sheet?

I have a functioning macro that results in two sheets but I want to see if I can get into one sheet to avoid user error.

Private Sub Command12_Click()
DoCmd.SetWarnings False
exportToXl ("Booking Override Query")
exportToXl ("Shipping Override Query")
DoCmd.SetWarnings True

MsgBox "Successfully Exported to M:\Automated Planning Reporting"


End Sub

Sub exportToXl(QueryName As String)

Dim dbTable As String
Dim xlWorksheetPath As String
xlWorksheetPath = "M:\Automated Planning Reporting\"
xlWorksheetPath = xlWorksheetPath & "Export_Ship " & Format(Date, "MM-dd-yyyy") & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, QueryName, xlWorksheetPath, True
End Sub
'- See more at: http://www.askeygeek.com/vba-code-to-export-access-data-to-excel/#sthash.pK0862gD.dpuf

Open in new window

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

I took a look, I don't see a way to do it with TransferSpreadsheet command.

~bp

Matt Miller

ASKER

Hi Bill,

I've tried doing a create table query with an append but the user was running into too many problems so I have to figure out another approach.

Bill Prew

Why not do what I suggested, just add a new query to the Access database that does a UNION of the two queries you already have: "Booking Override Query" and "Shipping Override Query" ?

~bp

Matt Miller

ASKER

That's a great idea. Thank you.

Matt Miller

ASKER

Thank you for your help. A rough solution quickly is a lot more valuable than a pretty solution that takes time.

Bill Prew

Great, glad that was helpful, thanks for the feedback.