How to import password-protected excel spreadsheet
If you would try to import password-protected Excel file into Access, using DoCmd.TransferSpreadsheet - method will fail. You can use following workaround - open protected excel file in Excel using automation, then run DoCmd.TransferSpreadsheet and then close file. Here a sample procedure to perform such task:
Public Sub ImportProtected(strFile As String, _
strPassword As String)
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
End Sub
To run this procudere:
ImportProtected "C:\MyFile.xls", "123"
Public Sub ImportProtected(strFile As String, _
strPassword As String)
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
End Sub
To run this procudere:
ImportProtected "C:\MyFile.xls", "123"
posted by Alex Dybenko | 14:50
5 Comments:
Hi,
I just found this on google. Thank you so much. It was really helpful. I made a few changes to accomodate the specific worksheet and range.
Public Sub ImportProtected(strFile As String, _
strPassword As String)
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="tmpTableName", FileName:=strFile, _
Hasfieldnames:=True, Range:="Q1!C:G"
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
End Sub
Thanks Again!
Hi,
I found this on google as well and it was a huge help. I needed to modify it to create a loop that opens and import ranges from a series of spreadsheets. The problem is that some times the code works and other times it doesn't. Can you understand why? The problem seems to be with passing in the password string. Something it works and other times it does not. I appreciate any advice that you can give.
Sub CompSheetsEurope_Extract()
On Error GoTo CompSheetsEurope_Extract_Err
Dim recFilePaths As Variant, varFileName As Variant
Dim strPassword As String
Dim db As Database
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
DoCmd.SetWarnings False
Set db = CurrentDb()
Set recFilePaths = db.OpenRecordset("tblCompSheetsEurope")
strPassword = "comp"
DoCmd.RunSQL "DELETE * FROM [tblSalesActualsQuotas] WHERE [LOB] = 'Europe'"
recFilePaths.MoveFirst
Do
varFileName = recFilePaths![FilePathName]
Set oWb = oExcel.Workbooks.Open(Filename:=varFileName, ReadOnly:=True, Password:=strPassword, _
UpdateLinks:=0, IgnoreReadOnlyRecommended:=True)
DoCmd.TransferSpreadsheet acImport, 8, "tblSalesActualsQuotas", _
varFileName, True, "SalesReport"
recFilePaths.MoveNext
oWb.Close SaveChanges:=False
Loop While Not recFilePaths.EOF
DoCmd.RunSQL "DELETE * FROM [tblSalesActualsQuotas] WHERE ([Actuals] = 0) AND ([Quotas] = 0"
DoCmd.SetWarnings True
CompSheetsEurope_Extract_Exit:
recFilePaths.Close
oExcel.Quit
Set oExcel = Nothing
Exit Sub
CompSheetsEurope_Extract_Err:
MsgBox Error$
Resume CompSheetsEurope_Extract_Exit
End Sub
Hi,
and what error you get when it does not work?
The errors differ. Sometimes Access crashes with a Runtime error. Other times it says that a particular file is being reserved. After doing some more digging, it seems that the problem stems from this part of the code:
oExcel.Quit
Set oExcel = Nothing
Excel never seems to shut down afterwards. If I go into Task Manager and kill any excel.exe processes, then the macro works.
Otherwise the files seem to remain locked in memory and the macro fails.
Thanks again for your help.
Hi,
i would try the following - make a new dummy excel file, a new dummy table to import this excel file, and them after your DoCmd.TransferSpreadsheet each time import this dummy excel file.
Seems like Access/Excel holds some reference to last imported file, so it does not allow to close excel
Also you can try to import dummy text file.
Post a Comment
Links to this post:
Create a Link
<< Home