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.
Hope this helps a few people
Rob