+ Reply to Thread
Results 1 to 10 of 10

Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet names.

  1. #1
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet names.

    Hello, and thank you in advance for any advice. I have 22 queries ("FULL_REPORT", "PLANNERS", "NAME_01" through "NAME_20"), to export to Excel from Access 2010. Rather than have the query export as "Name_01", I would like to have it change dynamically to match the worksheet in the same workbook named "Planners", and to match B2. Name_02, would be the same, only B3 and so on through B23. Name_01=B2, Name_02=B3, Name_03=B4 and so on.
    I am using a kill command in Access to delete the spreadsheet prior to rebuilding it with Access each day, so I can't use macros within Excel and they would be deleted every day.
    Thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,764

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    See if this link helps to get what you need.

    http://accessblog.net/2006/07/export...cel-range.html

  3. #3
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    Thank you. I had made it as far as what you posted, although your suggestion offered additional data for another day, I am still stuck on the part where "MyName" goes in the first example of your suggestion. Rather than the "MyName", Which for now I would use the static "Name_One", (also name of the table I am exporting), I would like the name to be chosen from the first worksheet in the spreadsheet. In the case of this example, I would like the name to be from worksheet "Planner", and from cell B2. Is it "allowed" to, rather that use just a name in quotes, use a path to a workbook/worksheet/cell? Thanks.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,764

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    Try it out and see what happens. Since I don't have your DB or Workbook, I cannot test for you. Also, I am running a later version of Excel and Access which may be an issue or not.

  5. #5
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    Thanks! I simply don't know how to enter it! quotes, brackets, single quotes, periods, exclamation points... :-) my desired path is \\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX with "Planner" being the worksheet within that path, and B2 being the cell I want. I have been successful in renaming it "test", and unfortunately "Planner b2", but nothing close to success as I don't know how to enter the path correctly to be read as a path, rather than just an attempt to post a fixed name.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,764

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    I'm a bit confused now as I re-read your post. If you "kill" the spreadsheet, how can you have data in B2 and other ranges as you have indicated

  7. #7
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    I recreate the spreadsheet on each update. The database creates the spreadsheet, adds the first worksheet as "Planners", then adds the second worksheet as Full_Report. It is currently also adding worksheets "Name_One" through "Name_20". It is "Name_One" through "Name_20" that I wish to have named from B2 through B22 using the "Planner" worksheet. "Name_One" worksheet is actually a filtered version of "Full_Report, filtered data equals the name found in B2 of worksheet. All exported straight away from Access. (22 tables.)
    Looking to have one worksheet with all of the data, then subsets of all of the data using the "Planner" column as the filter, and each worksheet named after the "Planner" the filter is based on. Sorry I am so terrible at this. It is my first posting. I really appreciate the attempts to help me.

  8. #8
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    "Googling" "Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)" gets me another method of achieving the same goal, and maybe explains my objective better. I have not been successful in manipulating the example there either. I would post the actual URL, but I am too new, and it won't let me.

  9. #9
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PLANNER_LIST", "\\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX", -1, "PLANNERS"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "FULL_TABLE", "\\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX", -1, "FULL_TABLE"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "NAME_01", "\\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX", -1, "NAME_01"

    after Name_01, I continue through Name_20. at the end of the line that says "Name_01", I am hoping to add a path that identifies as the contents of b2 of the spreadsheet.
    Thanks.

  10. #10
    Registered User
    Join Date
    06-14-2019
    Location
    Andover KS
    MS-Off Ver
    7
    Posts
    8

    Re: Access 2010 to Excel table/queries to worksheets, then dynamically change worksheet na

    [SOLVED] I found I can use a form in Access to identify the names I wish to reference. after the final comma, instead of hard coding a name between quotes, I can name the field by referencing a field in a form. for example: Forms!FRMMAINMENU!PLANNER_ONE. Thank you for your input!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1