How to change sheet name at close excel workbook

[willin]

Hi there,

Did ADO 2.X able to change the close excel worksheet name?

thks!

[OdiN]

The basic code is here, which will go into the "ThisWorkbook" object in the code editor:

[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets(Sheet1).Name = "New Name"
ThisWorkbook.Save

End Sub[/vba]
However, this will only work once as the next time you open it, there will be no "Sheet1" (and you will get an error) - it will be called "New Name". What you need to do is have something set in the sheet to signify which one you want to change. Or, if you only have one worksheet that you are working with, then the following code will work:


[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

For Each ws In Worksheets

ws.Name = "New Name"

Next ws

ThisWorkbook.Save

End Sub

[/vba]

Note that your workbook can only contain 1 sheet with that code as with more it will try to name them the same thing.

[OdiN]

If you want something that will work all the time, no matter what, you will need to do something like this. First, I have the prefix of SH1, SH2, SH3 on each sheet, then use this code:

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)

For Each ws In Worksheets

If ws.Name Like "SH1*" Then

ws.Name = "SH1 - New Name"

ElseIf ws.Name Like "SH2*" Then

ws.Name = "SH2 - New Name"

ElseIf ws.Name Like "SH3*" Then

ws.Name = "SH3 - New Name"

End If

Next ws

ThisWorkbook.Save

End Sub[/VBA]

As long as you rename it so that it still has the SH1, etc. prefix then it will always work no matter how many times you close, what you rename to, etc. Also you can use variables or the date function to append other info to the name depending on what you want to name it.

[Bob Phillips]

Not with ADO I think.

[OdiN]

Oops, missed the ADO part. Well that's how to do it just in excel :P

[willin]

Thks OdiN,

But I need change the sheet name for those excel files not opening. that's why I need ADO.

The reason is that I hv many files need processed frequency, If I change those vis open/close, it will take long time.

Is it possible deal with ADO for this case?

[mikerickson]

Could you put the change name routine in a workbook_Open routine?
I'm thinking that an Application wide names like BookOneSheetOneNewName could be applied when BookOne is opened. Or do you use the new names before BookOne has been opened.

Also, ThisWorkbook.Sheets(1) is a way to refer to that sheet even if it is renamed. (It doesn't work if the sheet is moved.)

[OdiN]

Thks OdiN,

But I need change the sheet name for those excel files not opening. that's why I need ADO.

The reason is that I hv many files need processed frequency, If I change those vis open/close, it will take long time.

Is it possible deal with ADO for this case?

Hmm...I've done this with Access, but not Excel:

You could try setting this:

Excel.Application.Visible = False

It won't be visible while it batch processes the files, which *should* make it faster since it doesn't have to update the screen, etc. Not sure how much faster that would make it though as I have not tried it.

[willin]

Hmm...I've done this with Access, but not Excel:

You could try setting this:

Excel.Application.Visible = False

It won't be visible while it batch processes the files, which *should* make it faster since it doesn't have to update the screen, etc. Not sure how much faster that would make it though as I have not tried it.

In order to fast, current I setting Excel.Application.Visible = False and Application.Calculation = xlManual then do something(Open/Close approach) such as change sheet name, but I still met problem on time expense.(more thousand files need proceed)

Thks!

[willin]

Where I can download all the method related excel of Microsoft ADO Ext. 2.8 for DDL and Security? I only know the few of them now.

thks!

[Bob Phillips]

If you are processingthousands of files, expect a time hit. It will still be quicker than doing it manually, and surely it is not something you would do every hour.

[daniel_d_n_r]

sub AUTO_CLOSE()

"code here"

end sub

[OdiN]

Why do you need to change the sheet name for so many workbooks? Is this a one time thing?

If you constantly need to be changing the name for this many workbooks, then I would say you need to look at whatever business process you are doing and see if there's a better way to do it.

[willin]

Actually, I do analysis for those file which hv standard format, only issue is that as the sheet name is project name but some of guys use the slight diff name as finance used, in the case, the final analysis report will hv some problem such as linkage.......

and it is frequency job, almost do it weekly.

thks a lot for yr attention!

[Bob Phillips]

Weekly isn't too bad, even for a long job.

How about using the sheet codename in your code, the Excel sheet name is then irrelevant.

[willin]

Weekly isn't too bad, even for a long job.

How about using the sheet codename in your code, the Excel sheet name is then irrelevant.

thks for yr advise. but it can't met the overall program request.

[rory]

You cannot use ADODB or ADOX to change the name of a worksheet - you will need to use automation to open the workbooks, or find a BIFF editor.
Regards,
Rory

[Bob Phillips]

We didn't realise that!