Transfer a dynamic query into Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi...i have a small problem and i hope someone can help. Thank u in advance.
The idea is to send a query created in code to Excel and then generate a
graph in Excel that i attach directly to report in access. I have seen that
graphs in Excel r easily maintained.

I am trying using code to export a dynamic crosstab query that re-genrates
itself everytime depending on specific criteria. The crosstab query has been
tested and works fine. However, the "docmd.Transferdatasheet" does not accept
the created string (i.e. crosstab query):
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strSourceName,
strFileName, False
The "strSourceName" does not accept a created string in code. Can this be
resolved??

I have tried all sorts of methods, but couldn't find the right solution.
Furthermore, is there a another solution that re-creates an Excel graph
directly from a query??!

Thanx again :-)
 
How about doing the Graph in Access? I don't think the transfer spreadsheet
will accept a SQL statement. It was met to transfer the results of a
query/table (data) to the sheet.

Blessings
 
Hi,

You can do something like this to put your new SQL string into an
existing querydef:

Dim dbD as DAO.Database
...
Set dbD = CurrentDB()
...
dbD.QueryDefs("Query1").SQL = "SELECT blah blah;"
 
Thanx John it works fine, but it seems that u have to create a dummy query1
just the first time....
eventually the excel file is hidden from the user and the graph is shown in
my report.
i am plotting by month (from jan to dec) on the x-axis certain statistical
data, so every year, the crossatb regenerates itself with variable column
headings (by month: format is YYMM i.e 0410 for oct. 2004) without my
interference !
thanx again....
 
Back
Top