Solved Opening a password protected Excel workbook?

Status
Not open for further replies.

RDawson9

Geek
Staff member
Hi Nick,

Hope you’re well mate.

I’d add a page to the Excel VBO similar to Open Workbook but after opening the Workbook, have a wait stage that waits for the password field (spied using AA) and handle it that way.

Let us know how you get on
Rob
 

nicholas.pawley

Nicholas Pawley
Hi Nick,

Hope you’re well mate.

I’d add a page to the Excel VBO similar to Open Workbook but after opening the Workbook, have a wait stage that waits for the password field (spied using AA) and handle it that way.

Let us know how you get on
Rob

Hey Rob,

Thanks for your help. This is for Abigail, she's saying that it wont go past the code stage as it gets stuck there when the password window opens?

Nick
 

anisjolly

Administrator
Hi Nick

The best approach (which I have used for my developments) is to duplicate the existing Open Workbook action from the MS Excel VBO and change the 'Open Workbook' code stage to include the extra Password input.

1512126270862.png

Then open up the 'Open Workbook' Code stage and amend the Code (within the Code tab) to the following:

Code:
Dim wb as Object = GetInstance(handle).Workbooks.Open(Filename:=filename, Password:=password)
name = wb.Name
wb.Activate()

You'll notice that there is a change of code within the Workbooks.Open brackets compared to the original Open Workbook Code stage. This performs the magic of opening an Excel spreadsheet and entering the password at the same time.

Try it and let me know how you get on.
 

nicholas.pawley

Nicholas Pawley
Hey Anis,

Abigail copied your example perfectly but it still did not happen to work. She has now managed to get around it in a different way.

Nick
 

RDawson9

Geek
Staff member
Was intrigued to see how this can be done without the use of code so had a go myself. Works as follows:

1) Create a new VBO for Excel and set up App Modeller to work with Excel.
2) Create a Launch page that receives two inputs - Password and Workbook.
3) In the Launch Navigate stage, used the Workbook as the Command Line Parameter and use a Wait stage to wait for the Password pop-up to appear.
4) Use Global Send Key Events to the Application to send the password and then send an Enter "{ENTER}" to the application then wait for the pop-up to disappear.
5) In the original Excel VBO, use the Attach to Workbook page to attach the VBO to Excel and output the handle.

1512647179571.png


Hope this helps a few people :)
Rob
 
Status
Not open for further replies.
Top