var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Exporting to Excel as Sheets
I am in need of some expert advice.
I can quite happied export data from access to an excel spreadsheet, but is there a way to export more than one report to excel but as separate sheets in one workbook.
I know this sounds far too fancy for Access but i'm hoping for a miracle.
One other thing, if there is a way, can I name these sheets by a name listed in a field?
I'm using access2000
If you were to export say 3 queries to Excel, providing they have the same bookname, each one will goto a different sheet. (Thats the easy bit)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "query1", "your path\and file\name.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, "query2", "your path\and file\name.xls"
The bit about renaming them is slightly different, now I have 2 ways to alter the sheet names in excel using Access.
1, name the queries with the names of the sheets. (eg Years profit)
2, slightly more advanced..
This is the code you'll need (maybe!!), if you need a hand modding it, let me know and I will type it for you.
Set xlapp = CreateObject("excel.application")
your_excel_file = "your path\and file\name.xls" '<---type your file and path here
xlapp.Visible = False
xlapp.DisplayAlerts = False
xlapp.sheets(1).Name = "Smilla 1" 'Sheet1
xlapp.sheets(2).Name = "Smilla 2" 'Sheet2
'and so on, you can alter this to do how many sheets are present or write a loop: for a = 0 to xlapp.sheets.count....
Set xlapp = Nothing
Thank you Ken for this information.
I have another question for you....
Can a user copy an existing query (by clicking a button) and by typing a value into a field will then create the criteria for the query?
[Button] Click to create query
Yes they can
The way I would do this, would be to get a 'sql' string made from a query, then use the Createquerydef function.
Heres a chunk of code I used this morning, it creates a crosstab query, then exports it to excel, then removes the said query...
Dim dbs As Database, qdf As QueryDef, strSQL As String
SqlText = "TRANSFORM Sum(report2_data.Sold) AS SumOfSold"
SqlText = SqlText & " SELECT Tbl_Products_KH.Stock_Code, report2_data.Item"
SqlText = SqlText & " FROM report2_data INNER JOIN Tbl_Products_KH ON report2_data.Item = Tbl_Products_KH.Product"
SqlText = SqlText & " GROUP BY Tbl_Products_KH.Stock_Code, report2_data.Item"
SqlText = SqlText & " ORDER BY Tbl_Products_KH.Stock_Code, report2_data.Item, report2_data.Route"
SqlText = SqlText & " PIVOT report2_data.Route;"
Qry_Name = "Report 1"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef(Qry_Name, SqlText)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, Qry_Name, "C:\.....\reports.xls"
you can also include this afterwards..
DoCmd.DeleteObject acQuery, Qry_Name
It deletes the query.
If this doesnt help, let me know
Sorry Ken, you have lost me a bit.
If I could explain what I want to do, would you mind translating it for me please.
The process of what the user does,
Opens Form 'Form1'
Picks a name from a combo box (Combo box name is 'TalentName')
This opens a query called 'qryTalent' based on the criteria from 'TalentName'.
I would like to be able to change the name of the query based on the 'TalentName', can this be done?
Then finally, export the query (with new name) to an existing spreadsheet with the Sheet name being the name of the query.
Phew, its confused me...
I think I have done it!
Here is the code, what do you think?
Dim stDocName As String
Dim stCriteria As String
stDocName = "qryTalents"
stCriteria = [TalentName]
DoCmd.OpenQuery stDocName, acViewPreview, acReadOnly
DoCmd.Rename stCriteria, acQuery, "qryTalents"
DoCmd.CopyObject "c:\Databasename.mdb", "qryTalents", acQuery, stCriteria
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, stCriteria, "c:\SpreadsheetnameCamp.xls"
Thanks again for your help
Ah ha !
Good answer Sam
Heres a much simpler way ofthe code I posted.
Dim dbs As Database, qdf As QueryDef,Sql_text as String
Set dbs = CurrentDb
Sql_text = CurrentDb.QueryDefs("qryTalent").SQL
Set qdf = dbs.CreateQueryDef("New Name", Sql_text)
"New Name" will be your new query, call it what you like !
Last edited by Ken_Hart; 02-27-04 at 09:54.
Thanks again - Much appreciated.