Export Cross tab Query results to excel

[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.

 

[PieterLinden 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.

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"

 

[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

[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.

 

[WilliamJ via AccessMonster.com]

[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.

 

[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/

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.

 

[WilliamJ via AccessMonster.com]

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.

 

[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

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.

--



.

 

[Sarita]

Sorry just realize that you went down that road.

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

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.

--



.

 

[WilliamJ via AccessMonster.com]

Sorry just realize that you went down that road.

Hi there,

[quoted text clipped - 26 lines]

Thanks for thinking of my problem though.