Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to rename sht after TransferSpreadsheet method?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to rename sht after TransferSpreadsheet method?
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. -- Regards, Tom Ogilvy "Rich" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to rename sht after TransferSpreadsheet method?
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 -----Original Message----- 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. -- Regards, Tom Ogilvy "Rich" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
TransferSpreadsheet to colored fields | Excel Worksheet Functions | |||
Access 2003: transferspreadsheet writes over existing spreadsheet | Excel Discussion (Misc queries) | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
What hyperlink method if any supports target rename? | Links and Linking in Excel |