Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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