how to rename sht after TransferSpreadsheet method?

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hello,

I transfer data to an Excel file from Ms Access using
TransferSpreadsheet. If I try to transfer data to the
same Excel file again using TransferSpreadsheet, I get the
error message that the sheet already exists. I tried
renaming the sheet, but I still get the same error
message. Like say I transfer tblData, I get a sheet in
Excel called tblData. So I rename that sheet to
shtSomething (and there are no other sheets, or I have
also tried having another sheet - say Sheet1). Even
though I renamed tblData to shtSomething, I still get the
error message that tblData still exists. The only
workaround I have found so far is to copy the data from
tblData sheet to another sheet and delete the tblData
sheet. It appears that Excel is remembering the tblData
sheet at a lower level of coding than VBA. Is there a way
to rename the sheet at that level (API code maybe?) or do
I have to stick with copying the data to another sheet and
deleting the TransferSpreadsheet sheet?

Thanks,
Rich
 
I haven't used TransferSpreadsheet from Access, but If you have renamed the
sheet, another possibility is that a defined name has been created with the
name tblData. Perhaps that is what the error message is refering to?

Sheets have code names, but I have never seen a situation where a duplicate
codename is produced or where the code name causes any type of conflict, so
I don't think there is a lower level naming conflict for sheets.
 
Thank you for your reply. I actually did try changing the
CodeName also, remotely from Access that is,

set xlObj = CreateObject("Excel.Application")
Set wkbk = xlObj.Workbooks.Open("C:\book1.xls")
Set sht = wkbk.Sheets("tblData")
sht.Name = "somethingelse"
or
sht.Codename = "Somethingelse"

But from Access I do this to transfer the data:

DoCmd.TransferSpreadsheet acExport,
8, "tblData", "C:\book1.xls" 'using Excel97

If I did this one time, it is OK. But if I run it again,
and I have not "DELETED" the original sheet (whether or
not it was renamed), it gives me the error msg "tblData"
already exists. But if I delete the sheet, then it runs
OK. So I am guessing that there must be some lower level
thing going on. The idea is to just rename the sheet.
Right now I am doing this:
....
Set sht1 =...
set sht2 = ...
sht1.UsedRange.Copy
sht2.Paste
sht1.Delete
....
This works fine, except that if I transferred like 10,000
records, it takes a little while for the copy action to
happen. So far, no problem. But I have to do this to
hundreds of files. Now it adds up.

If there is a way to get around having to copy/delete
sheets that would be real cool.

Note: the data is actually coming from Sql Server, goes to
Access to Excel where analysts tear it up. I have an ADO
technique that works with Excel97, but from Access, can't
do CopyFromRecordset for ADO types in 97. The ADO things
works fast for small recordsets, but larger ones,
TransferSpreadsheet is faster. I guess maybe when we get
hooked up with Excel2000 my woes will be over (for this
anyway). But if you know of any lowlevel way to change
the reference to that sheet for Excel - that would be cool.

Thanks again,
Rich
 
Back
Top