Question Export to named range in excel with named ranges within the data

[merry_fay]

Hiya,

I'm having a bit of fun getting my exports to work!!

I'm exporting a few queries to populate up a nice pretty excel report.

From the excel perspective, there's the 'Data' range, a couple of 'Lookup' tables & the control date (month the report is for)

I've got the Data section working fine -exporting to the named range set up in excel, right place etc etc, but I'm having problems with the Lookups sections.
Each time I export them, they set up a new tab with the named range as the tab name (followed by 1, 2 etc -depending on how many times i've tried...) & reset the named range to the output on these new tabs.

Could this be because I'm got named ranges within the excel data?
I've exported to excel from access so many times & never had this problem before -the only difference I can think of being the named ranges....


Also, for the control date, initially trying to export a table with only 1 record it kept coming up with an error so I built a query, (straight up select) & that solved the error. Is this usual being unable to export a table with only 1 record -I know I've solved it but am curious for learning...

Thanks
merry_fay

 

[GinaWhipp]

This might help...

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

 

[merry_fay]

Thanks. Unfortunately it hasn't though -I think my database has gone mad!!
I even tried inserting columns so none of the named ranges started on cell A1.

I've written many databases over the years, normally using the export bit in code without using a named range in the code -do the export once or set the name of the query/table in your spreadsheet as a named range then exvery time you export, it sticks it in the same place.

With this one, even when I remove the named range specification in the code, I export it, it creates a new tab called the query name & sets a named range called the query name (I could work with it if this was all it did) but then next time I export it, it creates a whole new tab called queryname1 (next time queryname2 etc) & it sets a named range called the query name on that tab

This has never happened before!! At the moment, I'm having to make changes so the users just copy & paste the data from access to excel just so it works.

I am extremely confused here....

Here's my code (the last one is the one that works with the named range specified -for now I've done a " ' " in front of the named range bit so it doesn't run it, but at least shows you what I was doing before):

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_Graph_Systems", sFullPath & ".xls" ', , "Systems"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_Graph_TCIDs", sFullPath & ".xls" ', , "TCIDs"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_Graph_Date", sFullPath & ".xls" ', , "SetDate"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "q_Graph_Data", sFullPath & ".xls" ', , "Data"

Systems & TCID would start on A1, SetDate has only 1 record in it & Data starts on B1 with column A having a calculation in it

Thanks

 

[GinaWhipp]

I'm stumped as I don't worh named ranges... Have you tried running them manually to see if the *real* issue will present itself.