Make name assigned to newly added worksheet persist when file save
 

This VB6 code code to assign "DupsList" as name to new worksheet appears to
work during execution. But after the run, when I bring up the new worksheet
in Excel, the name is still "Sheet1". It forgot my name assignment. Thanks
for kind assistance.
sXLSSpec = "C:\Documents and Settings\CQA\My documents\" & _
"DuplicatesList.xls"
On Error Resume Next
Kill sXLSSpec
On Error GoTo 0
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
Set myBook = xlApp.Workbooks(1)
'xlApp.myBook.Worksheets.Add
Set mySheet = myBook.Sheets(1)
myBook.Sheets(1).Name = "DupsLIst"
--
Lamont Phemister

Make name assigned to newly added worksheet persist when file save
 

Lamont,
Possible that xlApp.Workbooks(1) is not the WB you think, if you have hidden
WBs open, like PERSONAL.xls.
You can test by adding a
Msgbox myBook.Name

Better to set you WB variable when you add:
Set myBook = xlApp.Workbooks.Add

NickHK

"Lamont" wrote in message
...
This VB6 code code to assign "DupsList" as name to new worksheet appears
to
work during execution. But after the run, when I bring up the new
worksheet
in Excel, the name is still "Sheet1". It forgot my name assignment.
Thanks
for kind assistance.
sXLSSpec = "C:\Documents and Settings\CQA\My documents\" & _
"DuplicatesList.xls"
On Error Resume Next
Kill sXLSSpec
On Error GoTo 0
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
Set myBook = xlApp.Workbooks(1)
'xlApp.myBook.Worksheets.Add
Set mySheet = myBook.Sheets(1)
myBook.Sheets(1).Name = "DupsLIst"
--
Lamont Phemister

Make name assigned to newly added worksheet persist when file
 

Thanks Nick. It works. But, time for confession. As mentioned, the
assignment of a name with mysheet.name = "DupsList" tested to work but had
changed back to "Sheet1" by the time the run came to save the file. So, I
put in a watch and discovered that deep in the code there was another
assignment to "Sheet1" that I had forgotten about. Groan. Sorry to have
troubled you.
--
Lamont Phemister


"NickHK" wrote:

Lamont,
Possible that xlApp.Workbooks(1) is not the WB you think, if you have hidden
WBs open, like PERSONAL.xls.
You can test by adding a
Msgbox myBook.Name

Better to set you WB variable when you add:
Set myBook = xlApp.Workbooks.Add

NickHK

"Lamont" wrote in message
...
This VB6 code code to assign "DupsList" as name to new worksheet appears
to
work during execution. But after the run, when I bring up the new
worksheet
in Excel, the name is still "Sheet1". It forgot my name assignment.
Thanks
for kind assistance.
sXLSSpec = "C:\Documents and Settings\CQA\My documents\" & _
"DuplicatesList.xls"
On Error Resume Next
Kill sXLSSpec
On Error GoTo 0
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
Set myBook = xlApp.Workbooks(1)
'xlApp.myBook.Worksheets.Add
Set mySheet = myBook.Sheets(1)
myBook.Sheets(1).Name = "DupsLIst"
--
Lamont Phemister