• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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

0
mattfmiller
Asked:
mattfmiller
  • 4
  • 3
1 Solution
 
Bill PrewCommented:
Not sure if it can be done in the TransferSpreadsheet command, not aware that it can, but will look.

An alternative would be to just create a new query in Access that is the union of the two existing queries you run, and only call exportToXl once with the union query.  Not elegant, but should get the job done...

~bp
1
 
Bill PrewCommented:
I took a look, I don't see a way to do it with TransferSpreadsheet command.

~bp
0
 
mattfmillerAuthor Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Bill PrewCommented:
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
0
 
mattfmillerAuthor Commented:
That's a great idea.  Thank you.
0
 
mattfmillerAuthor Commented:
Thank you for your help.   A rough solution quickly is a lot more valuable than a pretty solution that takes time.
0
 
Bill PrewCommented:
Great, glad that was helpful, thanks for the feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now