Access import Password protected data from Excel

[George]

Does anyone know how to do this? I have this DoCmd.TransferSpreadsheet code
in an Access 2000 database that brings in Excel data. This data wasn't
password protected but now it is. I need to know how to put code in Access
that will bring in the same data but with a password. I have over 70 Excel
files that are brought in and I don't want to open each file and unprotect it.

This is the non password protected code I'm using:
***************************
DoCmd.TransferSpreadsheet acImport, 8, "tbl_Monthly_Set_Current_Activity", _
argFileName, True, "Monthly Set!a1:af2"
** *************************

These are object defintions:
tbl_Monthly_Set_Current_Activity Access database table

argFileName Excel file name

Monthly Set!a1:af2 Excel range to import


Thanks

 

[Alex Dybenko]

Hi,
what you can try - is to open excel instance via automation (createobject),
then open this file there supplying password, then run TransferSpreadsheet
and then close excel using quit method

 

[George]

Thank you Alex, but how do I do this?

Hi,
what you can try - is to open excel instance via automation (createobject),
then open this file there supplying password, then run TransferSpreadsheet
and then close excel using quit method

 

[Alex Dybenko]

Hi George,
i made a blog post with sample code:
http://alexdyb.blogspot.com/2005/04/how-to-import-password-protected-excel.html

 

[George]

Thank you so much Alex, this worked!

I have another question about this code. Since I'm opening up the Excel
file
and right after the oWB.Close closing code executes, I get the Excel prompt
"Do you want to save changes to the Contact.xls? yes or no or cancel. How
do I get rid of the prompt? The answer would be no I do not want to save.

 

[Alex Dybenko]

Hi George,
you can change it to:

oWB.Close SaveChanges:=False

 

[Martin]

Hi Alex

Thanks for your code - it's good and works some of the time. The only
problem is that the .Quit and setting oExcel to Nothing doesn't seem to work
and I'm always left with an invisible Excel running in memory (viewable from
the Processes tab of the Task Manager). My Access then crashes if I try to
run the code again.

Any ideas?

Martin

 

[Alex Dybenko]

Hi,
perhaps you still have some references to Excel objects, so check that
before running .Quit - you close all opened workbooks and set all open
objects to nothing

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com