Solved

Appending Access Queries to Excel Sheet

Posted on 2016-09-06
7
62 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 54

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 54

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 54

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 54

Expert Comment

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

Featured Post

Technology Partners: 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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