Export to Excel from Access

  • Thread starter Thread starter Karrie
  • Start date Start date
K

Karrie

I need help with the export function in Access. I know
that when I export a query, the tab in Excel hold that
name of the query. I also know if I were to export two
different queries, and name the Excel file the same, I
would have two tabs (the name of the query) within the
same workbook. I need to create about 10 workbooks that
could hold 10 - 20 tabs within each workbook. I do not
want to create 200 queries with the name that is needed
on the tab, but be able to use the variable as the name
on the tab within Excel and export in one workbook. Can
this be done? Thanks for all your help.
 
You could use VBA code that opens the EXCEL file via Automation and changes
the name of the worksheets. And, if you do that, you might as well use
Automation to write the data into the worksheet.

Or you could use VBA code to create new queries from the template query,
naming each temporary, new query, and then do the export using that
temporary query.

Can you give more info about what you're doing -- some specifics would be
helpful.
 
I think I would like to use temp queries, naming each
query and export the results into a workbook. For
example, I have Customer Field, Orders, Items, UM, New
Price, Old Price. Within each customer, I could have 10
orders and within each order I could have 5 - 100 items.
What I need to do is to query Customer, Order 1
(temporary query with the name of Order X) and export the
results into a Customer.xls with the tab labeled Order 1.
Query the same customer, Order 2, and export the results
into Customer.xls with the tab labeled Order 2, etc.
Would this be a macro, I use marcos on a very limited
basis. I hope this is enough information.

Thanks,
Karrie
 
You can't use a macro...you'd need to use VBA code. Here is an example of
what you can do if you wish to use the same query SQL for all the queries
(don't know if this is what you want?).


Dim qdf As DAO.QueryDef, qds As DAO.QueryDef
Dim dbs As DAO.Database
Dim intLoop As Integer
Dim strSQL As String, strQ As String

Set dbs = CurrentDb
Set qds = dbs.QueryDefs("QueryBeingUsedAsTemplate")
strSQL = qds.SQL
qds.Close
Set qds = Nothing

strQ = "NameOfNewQuery"
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


The above code can be modified to revise the SQL statement for each new
query, to have a different name for each new query, etc.
 
Thanks,

I will give it a try.

Karrie
-----Original Message-----
You can't use a macro...you'd need to use VBA code. Here is an example of
what you can do if you wish to use the same query SQL for all the queries
(don't know if this is what you want?).


Dim qdf As DAO.QueryDef, qds As DAO.QueryDef
Dim dbs As DAO.Database
Dim intLoop As Integer
Dim strSQL As String, strQ As String

Set dbs = CurrentDb
Set qds = dbs.QueryDefs("QueryBeingUsedAsTemplate")
strSQL = qds.SQL
qds.Close
Set qds = Nothing

strQ = "NameOfNewQuery"
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQ,
"C:\Ken.xls", False
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


The above code can be modified to revise the SQL statement for each new
query, to have a different name for each new query, etc.
--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
Back
Top