AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, April 12, 2005

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"

25 Comments:

Anonymous Faruque said...

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!

9:29 PM  
Anonymous Anonymous said...

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

11:07 PM  
Blogger Alex Dybenko said...

Hi,
and what error you get when it does not work?

11:21 PM  
Anonymous Anonymous said...

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.

11:57 PM  
Blogger Alex Dybenko said...

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.

8:20 AM  
Anonymous Ken Snell MVP said...

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

6:41 PM  
Anonymous Anonymous said...

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

8:34 PM  
Blogger Alex Dybenko said...

Hi,
general rule - is to close all objects you open, and set them to nothing.
Try to post your code here

7:47 AM  
Blogger Jeremy said...

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

4:36 AM  
Blogger Alex Dybenko said...

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)

10:17 AM  
Blogger Alex Dybenko said...

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)

10:17 AM  
Anonymous Anonymous said...

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?

2:41 AM  
Blogger Alex Dybenko said...

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

4:18 PM  
Anonymous Simon Milne said...

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 :)

6:45 PM  
Anonymous Simon Milne said...

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

12:09 PM  
Anonymous Anonymous said...

Thanks helped me finish my project

10:16 PM  
Anonymous Anonymous said...

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.

2:34 AM  
Blogger Alex Dybenko said...

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

10:29 AM  
Anonymous Anonymous said...

I could not force close the process

I got the error "Cannot quit microsoft excel"

What else can i do.

5:12 PM  
Blogger Alex Dybenko said...

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

5:30 PM  
Anonymous Anonymous said...

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.

9:35 PM  
Anonymous Anonymous said...

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.

7:27 PM  
Anonymous Anonymous said...

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 ?

9:03 PM  
Blogger Alex Dybenko said...

Hmm, perhaps you have supplier wrong password? try on a sample xls with simple password first

8:57 AM  
Anonymous Anonymous said...

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.

7:51 PM  

Post a Comment

Links to this post:

Create a Link

<< Home