Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004

    Exclamation 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?



  2. #2
    Join Date
    Feb 2004
    Dorset UK

    small miracle

    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.workbooks.Open FileName:=your_excel_file
    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....

    xlapp.workbooks(Dir(your_excel_file)).Close savechanges:=True
    Set xlapp = Nothing

  3. #3
    Join Date
    Jan 2004
    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?

    User Form:

    Name: [TextField]

    [Button] Click to create query

    Thanking you


  4. #4
    Join Date
    Feb 2004
    Dorset UK
    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


  5. #5
    Join Date
    Jan 2004
    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.

    For instance,

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

    Thanks again


  6. #6
    Join Date
    Jan 2004

    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


  7. #7
    Join Date
    Feb 2004
    Dorset UK
    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 !

    Try that.
    Last edited by Ken_Hart; 02-27-04 at 09:54.

  8. #8
    Join Date
    Jan 2004
    Thanks again - Much appreciated.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts