Output/Transfer

[Guest]

Hiya

I have some data in a query which needs to be presented in Excel. I am
having trouble automating this.

My data is to be displayed in a graph format so I have an excel template
which has 3 macros - highlight the data, sort it by date, resize the columns,
create a graph based on the data using a user defined style.

If I use OutputTo and enter the template name the macros are not available -
it doesn't seem to use the template atall.

If I use TransferSpreadsheet then the data is actually added into the
template itself.

Can anyone suggest a solution or point out where I am going wrong please.

Many thanks for your time.

Kerry

 

[Ken Snell \(MVP\)]

You cannot run EXCEL macros from an ACCESS macro unless you use VBA and
Automation, which the ACCESS macro can call via VB functions in the ACCESS
database.

TransferSpreadsheet will export to a new or existing EXCEL file, but will
not make a copy of a template EXCEL file first. Also, TransferSpreadsheet
will not allow you to run the EXCEL macros that may be in that existing
file.

Sounds like you may need to use ACCESS VBA to make a copy of the EXCEL
template file, do the export (either via TransferSpreadsheet or via
Automation of the EXCEL file), and then open the EXCEL file and run the
macros within it.

You can use TransferSpreadsheet to export to an existing, formatted EXCEL
spreadsheet by careful, limited use of the "Range" argument
for the method. See this web site for details:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

Post back with more details about your setup, and we will try to guide you
through the process.

 

[Guest]

Hi Ken

Sincere apologies for the delay in responding.

My Access Macro (Mcr Revenue Data):-
Runs a couple of queries which builds Tbl Revenue. Tbl Revenue is then
displayed in Excel using OutputTo (Excel 2002) creating/overwriting Revenue
Stats.xls which shows all the detail of all courses and room hire revenue.
The Macro also exports qry Rev Chart Data (a cross tab query based on tbl
Revenue) using OutputTo creating/overwriting Revenue Chart.xls

I have created a template which highlights the Revenue Chart data, sorts it
by month, highlights the current region and creates a user defined style
chart in a new sheet.

The problem I have is that currently I have to copy the Revenue Chart.xls
data into my template (Revchart.xlt) then run the macro (sort by date,
selected current region, create chart)

I thought that by entering the name of my RevChart template in the OutputTo
and then opening the Revenue Chart.xls file my macros would be available -
this is not the case because it takes no notice of my template. How can I
make my excel macros available in the book where the data is exported to?

I hope this makes sense.

Thanks very much for your time.

Kerry

 

[Ken Snell \(MVP\)]

Comments inline:
--

Ken Snell
<MS ACCESS MVP>

Hi Ken

Sincere apologies for the delay in responding.

My Access Macro (Mcr Revenue Data):-
Runs a couple of queries which builds Tbl Revenue. Tbl Revenue is then
displayed in Excel using OutputTo (Excel 2002) creating/overwriting
Revenue
Stats.xls which shows all the detail of all courses and room hire revenue.
The Macro also exports qry Rev Chart Data (a cross tab query based on tbl
Revenue) using OutputTo creating/overwriting Revenue Chart.xls
OK


I have created a template which highlights the Revenue Chart data, sorts
it
by month, highlights the current region and creates a user defined style
chart in a new sheet.

I assume that the export is not being made to this template file? And I
assume that the macros contained in this template EXCEL file are not in the
Revenue Stats.xls file?

The problem I have is that currently I have to copy the Revenue Chart.xls
data into my template (Revchart.xlt) then run the macro (sort by date,
selected current region, create chart)

I thought that by entering the name of my RevChart template in the
OutputTo
and then opening the Revenue Chart.xls file my macros would be available -
this is not the case because it takes no notice of my template. How can I
make my excel macros available in the book where the data is exported to?

You could use ACCESS VBA to make a copy of the template file (including its
macros), name it Revenue Chart.xls, and then do the exports (via
TransferSpreadsheet method) to this new file. You then could open that file
and then run the macros within the file.