topic Re: Rename Excel Worksheet in Product Forum

Rename Excel Worksheet

joanne.spratt — Thu, 31 Oct 2024 23:46:06 GMT

Looking for some guidance on how to rename a excel worksheet in BP, not the whole workbook, but a single worksheet within the workbook. TIA

Re: Rename Excel Worksheet

Mohamad_Dakkouri — Fri, 01 Nov 2024 06:58:19 GMT

Hi @joanne.spratt

Suppose i have this excel file and i want to delete Sheet2

Drag and drop an action and go to MS Excel VBO

- Create Instance

- Open Workbook

- Activate Worksheet

- Delete Worksheet

- Save

-Close

- Close Instance

Your Flow should look like this :

Try it and let me know 😉

Re: Rename Excel Worksheet

michaeloneil — Fri, 01 Nov 2024 10:04:30 GMT

Hi @joanne.spratt

Are you using the latest version of the excel VBO? im not sure if there is an action in there for a rename of the worksheet but if you have the latest one then have a look in there for any action for renaming. If it doesnt exist then you can create a new action with a new code stage, in the excel vbo create a new action and in the action add a code stage, add 3 inputs called handle, source worksheet and source workbook. There should be 2 outputs as well, success and message then in the code tab add the following:

Dim wb, ws As Object

Try

wb = GetWorkbook(Handle, Source_Workbook)

ws = wb.Worksheets(Source_Worksheet)

ws.select()

ws.Name = New_worksheet_name

Success = True

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

End Try

Below are some screen grabs so you can see whats needed, not the checkinstanceandworkbook stage are optional its not required.

input on the start stage

Action flow

Code stage inputs

Code stage outputs

End stage outputs

Hope this helps 🙂

Re: Rename Excel Worksheet

John__Carter — Fri, 01 Nov 2024 10:26:16 GMT

Hi Joanne - it's best not to edit the original Excel VBO, so make a new VBO for your bespoke actions. Add a code stage like this, with workbook name, worksheet name and new worksheet name as inputs. The logic below makes the workbook and worksheet names optional, where the active workbook or worksheet is used if no name is specified. The code also assumes the workbook has already been opened (probably by the default Excel VBO).

With Microsoft.VisualBasic.GetObject(, "Excel.Application") Dim wb, ws As Object If Workbook = "" Then wb = .ActiveWorkbook Else wb = .WorkBooks(Workbook) End If If Worksheet = "" Then ws = wb.ActiveSheet Else ws = wb.Worksheets(Worksheet) End If ws.Name = NewWorksheet End With

Re: Rename Excel Worksheet

michaeloneil — Fri, 01 Nov 2024 11:18:35 GMT

Hi @joanne.spratt

Although I agree with @John__Carter it would be best to create your on vbo to house any new actions for excel but i need to point out that this will involve significantly more steps to implement so this would depend on your level of knowledge and confidence in creating your own vbo. You will need to create the vbo and then copy the global code from the existing excel vbo to your new one and add it to the initialise page on the new vbo, you will also need to copy over the create instance page from the original (global code will need this) and at minimum the attach page as well which will also need the open instance page, this will allow you to attach to the instance from the original vbo. If you intend to do all the actions for renaming on this new vbo you will need the open workbook action, save and close instance actions as well before you also create the Rename worksheet action.

Re: Rename Excel Worksheet

Mohamad_Dakkouri — Fri, 01 Nov 2024 11:06:09 GMT

@joanne.spratt Apologize i read delete instead of rename..

Apologize again

Re: Rename Excel Worksheet

John__Carter — Fri, 01 Nov 2024 12:08:45 GMT

Hi @michaeloneil the action I posted should work 'as is' and will not need anything from the original Excel VBO. It's a 'lightweight' way of adding new Excel functionality without having to mimic the complexity of the default VBO.

Re: Rename Excel Worksheet

michaeloneil — Fri, 01 Nov 2024 12:24:59 GMT

ah sorry @John__Carter i didnt see the GetObject step in there

Re: Rename Excel Worksheet

stepher — Sun, 03 Nov 2024 16:29:18 GMT

Good morning, @joanne.spratt,

To get back to your original question, there is no specific action to rename a worksheet. Without getting into the custom object options, you have a couple of options:

You could create a macro run macro via the MS Excel VBO. There appear to be a couple different ways to do this.
You can create a new Worksheet, named as you like, copy the data from your original Worksheet and then Delete that Worksheet

Again, all done within the current 'vanilla' version of the MS Excel VBO.

Hope that helps,

Red