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
25 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.
The problem with Anonymous' error is that the oWb object is not set to Nothing before the EXCEL object is closed.
Change this one line of code inside the loop:
oWb.Close SaveChanges:=False
to these two lines of code inside the loop:
oWb.Close SaveChanges:=False
Set oWb = Nothing
Hi
I have recently found this and it was doing as I wanted during initial testing but now it is getting stuck in a loop that only closing Access using Task Manager will break it.
I have 4 tables all from the same workbook that I want to import. The workbook has a known password.
I am trying to open excel, import sheet 1, close excel, open excel, import sheet 2 etc etc as per your code(with Ken's adjustment)
However it still asks me for passwords and is not closing out of Excel.
Any advice greatfully received. Is there maybe a way of opening the workbook via VBA and saving it without the password
Hi,
general rule - is to close all objects you open, and set them to nothing.
Try to post your code here
The code works, but when I run the module, it only opens the spreadsheet and just asks for the password. It imports, then closes, but there's a message saying it can be opened for read/write. What am I doing wrong. Here is the code I'm putting into my code builder Public Sub Link_Excel_Security()
ImportProtected "C:\Users\Shultz23\Documents\test.xls", "test"
End Sub
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
Hi,
looks like password is wrong, check this line:
it should open your excel file without error and password prompt:
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
Hi,
looks like password is wrong, check this line:
it should open your excel file without error and password prompt:
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
Hi,
I have found similar problems to above with a reference to Excel left in the processes tab of task manager which results in errors if I try to import again before shuting down the PC.
I have narrowed it to the transferspreadsheet method which seems to open its own connection to excel which is not then closed with:
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
Is there a way to force excel closed once its finished with?
Hi,
you can try to run one more transferspreadsheet afterwards on some fake spreadsheet, for example to make simple export, in some cases that helps to release file or instance
For those people that still have a problem after opening with the password parameters, its likely to be you need a password to open, as well as a password to edit, if this is the case just add another parameter to your open which is something like WriteResPassword:=strPassword
You might then find that each spreadsheet is locked in which case you need to loop them all and unlock, after doing all or some of this it should transfer in with no problems :)
If you need to force the close of the excel application you can try and do it like this:
Dim obj as object
Set obj = GetObject(,"Excel.Application")
obj.quit
set obj = nothing
You may need to loop open workbooks and close etc but hopefully this will help.
If this does not work, then you may have to force a close, if its still a problem I will try and post some code another day to truely force the close.
Hope this helps, simon
Thanks helped me finish my project
I have the same issue.
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
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub
The excel.exe still remains in task manager creaing problems. If the line DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import", strFile, -1 is not present all is fine.
All solutions posted here and everywhere else do not work. Basically, open an excel file that is password protected and import it to a table. I havent found a solution yet and it has been 1 week ive been trying.
Please help I have only 48 hours left to finish the project.
you can try to force Excel closing with API like below:
http://www.mvps.org/access/api/api0025.htm
but i would carefully try again other advices
I could not force close the process
I got the error "Cannot quit microsoft excel"
What else can i do.
Then you have to find a reason why it cannot quit. try to import manually, try without password, try less data, one row for example
I was able to get around it by opening the excel file before hand, ( user enters the password for the excel file ) leaving it open and then using access to import.
I have done many tests with the original excel file and the process always gets stuck even when the file was empty but contains a password. Everyrthing works when there is no password on the file.
I at least have a work around but the users do not want to open the files before using the access app.
Still have to find a solution for this. I will keep looking.
Found a solution.
Must prompt user for pass.
Open file with password
remove password from file
save
do what you want
open file no pass
add pass
save
works A1, for imports as well as exports to excel protected by password.
I copied the "Public Sub ImportProtected" procedure from this site and pasted into access db for testing.
Excel appliction is getting opened with file prompting for Password. Thou it is provided in the calling procedure itself.
Any Idea where I'm going wrong ?
Hmm, perhaps you have supplier wrong password? try on a sample xls with simple password first
Its actually becuase Excel.exe is already in your process when executing the code. Tahts why you see excel instead of being transparent.
The "Public Sub ImportProtected" leaves excel in the process after its being opened, refer to earlier posts for my work around.
I can paste the code I use, if you need it.
Post a Comment
<< Home