Solved

Appending Access Queries to Excel Sheet

Posted on 2016-09-06
7
67 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 55

Accepted Solution

by:
Bill Prew earned 500 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 55

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 55

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 55

Expert Comment

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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