Export Cross tab Query results to excel

  • Thread starter WilliamJ via AccessMonster.com
  • Start date

W

WilliamJ via AccessMonster.com

I am trying to refresh an Excel spread sheet with the updated results of a
cross tab query. I need to send the data every day to a certain tab within
the spreadsheet. I can do the macro to export to excel, but I need it to go
to the 2nd tab. It keeps wanting to go to the first tab which is my working
part containing all the formulas for analysis. The second tab is for the
data.

Any suggestions.

I tried using Microsoft query from within Excel to pull the data but that
only goes to the tables whereas I need Access query results.
 
P

PieterLinden via AccessMonster.com

WilliamJ said:
I am trying to refresh an Excel spread sheet with the updated results of a
cross tab query. I need to send the data every day to a certain tab within
the spreadsheet. I can do the macro to export to excel, but I need it to go
to the 2nd tab. It keeps wanting to go to the first tab which is my working
part containing all the formulas for analysis. The second tab is for the
data.

Any suggestions.

I tried using Microsoft query from within Excel to pull the data but that
only goes to the tables whereas I need Access query results.
What if you specify the sheet you want to write to in the TransferSpreadsheet
command? It should overwrite the contents of the existing sheet.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "Name Of Tab to Write To"
 
Ad

Advertisements

W

WilliamJ via AccessMonster.com

PieterLinden said:
I am trying to refresh an Excel spread sheet with the updated results of a
cross tab query. I need to send the data every day to a certain tab within
[quoted text clipped - 7 lines]
I tried using Microsoft query from within Excel to pull the data but that
only goes to the tables whereas I need Access query results.
What if you specify the sheet you want to write to in the TransferSpreadsheet
command? It should overwrite the contents of the existing sheet.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "Name Of Tab to Write To"

I have tried your suggestion and it will work fine exporting to the workbook
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
 
W

WilliamJ via AccessMonster.com

WilliamJ said:
[quoted text clipped - 7 lines]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "Name Of Tab to Write To"
I have tried your suggestion and it will work fine exporting to the workbook
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
Access exports into a new tab and gives it the name of the query instead of
adding to an existing tab.
 
K

Ken Snell

See this article -- it may give you some insights on what is happening and
what you need to do to make it work the way you want:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange


--

Ken Snell
http://www.accessmvp.com/KDSnell/




WilliamJ via AccessMonster.com said:
PieterLinden said:
I am trying to refresh an Excel spread sheet with the updated results of
a
cross tab query. I need to send the data every day to a certain tab
within
[quoted text clipped - 7 lines]
I tried using Microsoft query from within Excel to pull the data but that
only goes to the tables whereas I need Access query results.
What if you specify the sheet you want to write to in the
TransferSpreadsheet
command? It should overwrite the contents of the existing sheet.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "Name Of Tab to Write To"

I have tried your suggestion and it will work fine exporting to the
workbook
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
 
W

WilliamJ via AccessMonster.com

Ken said:
See this article -- it may give you some insights on what is happening and
what you need to do to make it work the way you want:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange
[quoted text clipped - 15 lines]
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
Thank you very much, this does give me some insight into why it won't work.
I will try another direction.
 
S

Sarita

Hi there,

Here a suggestion that might help. Instead of exporting the crosstab query
into excel use Microsoft query through excel to bring in the data through
Access. I've done this before and all you have to do is set up the colums
that will be populated. You cannot create complicated queries because those
should be done in Access but its a great way to bring in large datasets into
Excel.

Open Excel>Data>Import External Data>New Database Query

Have fun!!!

Sarita

WilliamJ via AccessMonster.com said:
Ken said:
See this article -- it may give you some insights on what is happening and
what you need to do to make it work the way you want:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange
I am trying to refresh an Excel spread sheet with the updated results of
a
[quoted text clipped - 15 lines]
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
Thank you very much, this does give me some insight into why it won't work.
I will try another direction.

--



.
 
S

Sarita

Sorry just realize that you went down that road.

Sarita said:
Hi there,

Here a suggestion that might help. Instead of exporting the crosstab query
into excel use Microsoft query through excel to bring in the data through
Access. I've done this before and all you have to do is set up the colums
that will be populated. You cannot create complicated queries because those
should be done in Access but its a great way to bring in large datasets into
Excel.

Open Excel>Data>Import External Data>New Database Query

Have fun!!!

Sarita

WilliamJ via AccessMonster.com said:
Ken said:
See this article -- it may give you some insights on what is happening and
what you need to do to make it work the way you want:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

I am trying to refresh an Excel spread sheet with the updated results of
a
[quoted text clipped - 15 lines]
but when I try to specify a tab or sheet in my excel workbook it fails or
does not do anything. It will not export to a specific tab.
Thank you very much, this does give me some insight into why it won't work.
I will try another direction.

--



.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top