Import password protected excel sheets

[Boss]

I use the following code to import multiple files into one single master table.


Dim strPathFile As String 'strFile As String
'strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "C:\CMS Files\Files\"

'Name the table
strTable = "Master"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop
MsgBox "Import complete."



what if the excel files have an fileopen password. Please help me with the
changes i need to do in the code.
I did alot of google... and prepared the following code...

Dim oExcel As Object, oWb As Object
Dim strPassword, strFile As String
strPath = "C:\CMS Files\Files\"
strPathFile = Dir(strPath & "*.xls")
Do While Len(strPathFile) > 0
strFile = strPath & strPathFile
Set oExcel = CreateObject("Excel.Application")
strPassword = "database"
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword)
oWb.Unprotect strPassword
oWb.Password = ""
oExcel.DisplayAlerts = False
oWb.Save
oExcel.DisplayAlerts = True
oWb.Close
strTable = "Master"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
strFile, True
oExcel.Quit
Set oExcel = Nothing
strPathFile = Dir()
Loop
MsgBox "Import complete."


but it asks for password, moreover opens up excel which needs to be closed
manually...


Thanks!
Boss