Thanks for posting this niallfee. I just recently figured out the code myself and came here to post it. I'm following more or less the same logic myself but lack knowledge of Visual Basic and I'm not sure what the function of the Type.Missing objects. Would you be able to explain these?
In addition to the above, I included WriteResPassword in my own, if in the case of files having separate write/read credentials. These can be set to whatever data item inputs you need. As a bonus, there's no issue using the amended code if the files you are opening do not have password access enabled.
Dim wb as Object = GetInstance(handle).Workbooks.Open(filename,Password:=(Password),WriteResPassword:=(Password))
name = wb.Name
wb.Activate()