Chris,
here are some answers from Google search of past discussion group answers.
I haven't tried these, but they sound as if they will work.
I don't believe that you can give a password in the TransferSpreadsheet
command.
One way around this is to open the EXCEL file using Automation and then read
the data.
Or you could get "fancy" and open the file, save it to a temporary file
without the security, import that file, and then delete the temporary file.
Ken Snell
<MS ACCESS MVP>
As far as I know, Access's Jet database engine can only link to a
protected workbook if it is already open in Excel.
You can open it manually or write code that launches Excel and opens the
workbook using Excel's Workbooks.Open method, which takes a password
argument.
I can link to sheets in a protected workbook without difficulty -
provided that no password was set on the protection. Using a password
encrypts the workbook file, and there is no mechanism for Access to
decrypt it so it can get at the data.
It's not mandatory to set a password when you protect the workbook, so
if you only need to protect against accidental changes by the users you
can omit the password and use your linked tables as normal.
If you do have to use a password on the workbook, the only way Access
can get at the data is if you open the workbook in Excel first and then
use the linked tables. This can be done manually or via VBA.
John Nurick [Microsoft Access MVP]
I use automation to open the Excel file using the password. I then call the
TransferSpreadsheet method and my data is imported
Unknown
Jeanette Cunningham
