Renaming Excel Worksheet Names after creation in VBA

[Guest]

Hello,

I created an excel spreadsheet using VBA Code. It creates 3 sheets in one
workbook acquiring the information from queries. However, when the excel
workbook is created and I open the file that VBA saved on my hard drive, it
says that it had to "repair" the workbook, that there were invalid sheet
names. As a result, it named the sheets "Recovered_Sheet1",
"Recovered_Sheet2" and so on. I would like these individual sheets to be
renamed to the queries that I got the data from.

Is this possible within VBA?

Thank you!
MN

 

[Guest]

If the names were invalid when the sheet was created, it stands to reason
using the query name (which, unless otherwise specified in the Range
argument) would still be invalid.
Look in VBA Help at the TransferSpreadsheet method. You can use the Range
arguement to specifiy the name of the worksheet.

If Excel doesn't like the names of your queries, I would suggest you
rethink your naming conventions.

 

[Guest]

Hello,

Thank you for the suggestion. However, I renamed the queries (they has
asterisks in the names) and when i then looked at the spreadsheet VBA
created, those tab names had the correct names, but then 3 additional tabs
were created with those same "Recovered_Sheet_1" naming conventions. Any
ideas why this would be happening?

Thank you!
MN

 

[Guest]

Well, I could understand the problem with the *, but let me see if I
understand what you are saying.
You are creating an Excel workbook and each of three exports creates a sheet
with the correct query name as the tab name and there are 3 additional sheets
created with the recovered name.
Is that correct?
Are you sure the Excel workbook did not already exist with the bad names and
you are adding the correct names?
Other than that, I don't know what could cause that problem.

 

[Guest]

Yes, that is correct.

You are actually spot on -- it was just adding the valid names to the
document that was already created on my hard drive with the invalid names.
It's working great now once i deleted that old version.

Thanks a lot!
MN

 

[Guest]

Oh good. I could not imagine how Excel would be doing something like that.