?
Solved

Appending Access Queries to Excel Sheet

Posted on 2016-09-06
7
Medium Priority
?
73 Views
Last Modified: 2016-09-06
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
Comment
Question by:mattfmiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 56

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 41786642
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
 
LVL 56

Expert Comment

by:Bill Prew
ID: 41786662
I took a look, I don't see a way to do it with TransferSpreadsheet command.

~bp
0
 
LVL 1

Author Comment

by:mattfmiller
ID: 41786696
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 56

Expert Comment

by:Bill Prew
ID: 41786797
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
 
LVL 1

Author Comment

by:mattfmiller
ID: 41786873
That's a great idea.  Thank you.
0
 
LVL 1

Author Closing Comment

by:mattfmiller
ID: 41786877
Thank you for your help.   A rough solution quickly is a lot more valuable than a pretty solution that takes time.
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 41787055
Great, glad that was helpful, thanks for the feedback.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question