TransferSpreadsheet to existing worksheet creates new worksheet instead.

[Phil Smith]

I started with 12 queries, and a macro to use TransferSpreadsheet into
an Excel spreadsheet called SalesD.xls into pre-existing preformated
worksheet tabs called: sales1-d, sales2-d, sales3-d etc. These are also
the names of the queries.
I run that macro, it works. I run it 10 times, it still works. I get
the existing tabs filled with the correct data, formatting intact.
Now I need the same thing again but with a couple key changes to the
selection criteria, so I:
1) Opened, edited for criteria, and saved each query as
Sales1-w, Sales2-w, etc.
2) I copied the macro and edited it to transferSpreadsheet Sales1-w
instead of Sales1-d, etc. and changed the filename to transfer in to
SalesW.xls.
3) Copied SalesD.xls to SalesW.xls, end renamed all of the formatted
tabs from Sales1-d to Sales1-w, etc. all once again matching the Query
names.

PROBLEM:
When I run THIS Macro, While there is a tab called Sales1-w, and there
is a worksheet tab in that spreadsheet called Sales1-w, it instead
creates a new tab called Sales1-w1, and on with all twelve queries.
I run it several times, and it contiues to put the data into Sales1-w1,
etc. instead of the queries it should.

What's up with that?
If I kill the spreadsheet and run the macro, it creates it with the
correct tabs, and I can run it several times and will still work with
the correct tabs.

Help?

 

[Ken Snell MVP]

Are you using the Range argument of the TransferSpreadsheet action for these
exports? If yes, see this article for information about how spreadsheets are
named -- there is an example of what you're seeing in this article.

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

I think what you're seeing is the result of when the workbook file contains
ranges with the same name as the worksheet names that you want to use, so
the export creates a new worksheet with the "duplicate" naming convention
found in EXCEL, namely adding a 1 to the end of the worksheet name.

 

[Phil Smith]

No, I do not have any ranges in this spreadsheet. I may at some point,
but for the time being, there are no named ranges in the spreadsheet.

In the case of this problem one, all I did was manually rename existing
tabs to match the name of the new set of queries. Is it possible that
manually renaming a tab creates an entry in the named range table? Or
in some other area that is also tested for duplications?

Phil

 

[Phil Smith]

Ok, you were correct, there are a bunch of Names one for each tab.
There are also names for those tabs in the original worksheet which
still works. What I do not understand is how they got there. I created
this original spreadsheet by running my macro. There are no ranges
specified in the transferspreadsheet macro.

So, something is creating those range names. I will try to delete them
all and see if it makes a difference.

Thanx

 

[Ken Snell MVP]

When you use TransferSpreadsheet to export a table or query, it actually
creates a Range in the workbook with the same name as the worksheet. So when
you changed the worksheet names, you didn't actually change the Ranges. My
article (see earlier link) explains what happens in those cases.