Exporting multiple queries into Excel workbook tabs

[Jul B.]

I have a set of queries I run for vendor metrics on a weekly basis and want
to export into 1 Excel spreadsheet. Currently, the export is a manual effort
copying 5 query results into 5 separate tabs of the same spreadsheet then
emailing to 10 vendors. Is there a way to automate this process? HELP!

 

[Piet Linden]

I have a set of queries I run for vendor metrics on a weekly basis and want
to export into 1 Excel spreadsheet.  Currently, the export is a manual effort
copying 5 query results into 5 separate tabs of the same spreadsheet then
emailing to 10 vendors.  Is there a way to automate this process?  HELP!

Ken Snell has code to do Excel Automation/Exporting on his website.
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

Danny has code on his website to automate Outlook... you would have to
write the name of the Excel file to a variable, and then you could
Attach it...
http://www.amazecreations.com/datafast/CodePages/CodeOutlookMail.asp

 

[filip]

I have a set of queries I run for vendor metrics on a weekly basis and want
to export into 1 Excel spreadsheet.  Currently, the export is a manual effort
copying 5 query results into 5 separate tabs of the same spreadsheet then
emailing to 10 vendors.  Is there a way to automate this process?  HELP!

Hi,

You should build .NET application that exports data to Excel file. For
that I recommend GemBox.Spreadsheet cause it's easy to use and works
very fast. Here's more about it: http://www.gemboxsoftware.com/GBSpreadsheet.htm

 

[Jul B]

thanks for your reply - i did not mention that i am not a code writer in any
aspect of the meaning. i am using access 2003 and understand that i can use
transferSpreadsheet to get the data into excel, however, where i put the
code, etc., is way beyond my skills. i'll need step by step
directions...sorry! THANKS so much!

 

[Ken Snell [MVP]]

To assist you in more detail, you will need to give us more details.

Are the queries saved queries?

Do you want to run the automated code by clicking a button on a form? or by
running a macro? or by some other means?

Is the name of the EXCEL file into which the data are to be exported a
constant, fixed name? or is it a dynamic name?

Is the location of the EXCEL file (whether it already exists or will be
created by TransferSpreadsheet) in a fixed folder? or in a dynamically named
folder?

 

[Jul B.]

Hi Ken - Thanks in advance for any help you can provide.

 

[Ken Snell [MVP]]

Are you using a macro now to run your delete queries, etc.? By macro, I mean
the Macros option, not VBA code.

Assuming yes, you need to add a single step to your macro for each query
that you want to export. The information for each step would be similar to
this:

Action: TransferSpreadsheet
Spreadsheet Type: Microsoft Excel 8-10
Table Name: < put the name of the query to be exported here,
without the angle brackets >
File Name: ="C:\FolderName\SomeFixedPartOfFileName" &
InputBox("Enter date information (yyyymmdd):", "Enter Date") & ".xls"
Has Field Names: No
Range: < leave blank >

The InputBox function will ask you to enter the date information. I've
arbitrarily used the prompt of "Enter date information (yyyymmdd):". You can
use whatever you want, but do not include / characters in the date string.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jul B.]

Ken-
Yes, I am running a macro to run the delete queries & to update the data. I
don't even know how to get to the VBA code much less write it.

I am going to try it first thing Tuesday! This will allow me to send
multiple queries to multiple tabs on one Excel spreadsheet and will make my
life SO much easier. I'm sure you've been told multiple times before, but
you ROCK! THANKS, Mr. Wizard.

 

[Jul B.]

Ken - this works great, it's WAY better than what I had before except now I'm
entering the date 30 times for each query that's exported to a tab - is there
a way to condense that somehow?

 

[Ken Snell MVP]

Assuming that you're using a command button on a form to run the macro, add
a textbox to the form (name it txtDate). Use that textbox to provide the
desired date; type the date (in yyyymmdd format) in that textbox and then
click the button.

Change the macro's File name argument first, of course, to this:

File Name: ="C:\FolderName\SomeFixedPartOfFileName" &
Forms!NameOfTheForm!txtDate & ".xls"


If you're not using a form, then change your setup so that you do use a
form.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jul B.]

Thanks, Ken - I'll try it!

Assuming that you're using a command button on a form to run the macro, add
a textbox to the form (name it txtDate). Use that textbox to provide the
desired date; type the date (in yyyymmdd format) in that textbox and then
click the button.

Change the macro's File name argument first, of course, to this:

File Name: ="C:\FolderName\SomeFixedPartOfFileName" &
Forms!NameOfTheForm!txtDate & ".xls"


If you're not using a form, then change your setup so that you do use a
form.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/