Rename Excel Worksheet

[joanne.spratt]

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

[Mohamad_747]

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 😉

[michaeloneil]

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 🙂

[John__Carter]

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

[michaeloneil]

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.

[Mohamad_747]

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

Apologize again

Follow

[John__Carter]

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.

[michaeloneil]

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

[stepher]

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:

1. You could create a macro run macro via the MS Excel VBO.  There appear to be a couple different ways to do this.
2. 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