Import Excel With Password

[Ross]

My research has provided this code to import excel that is password
protected. The code is not working because:

1. The Spreadsheet is still asking for the password and
2. The spreadsheet remains open even but the code is supposed to close it.

Can anyone spot the error
I am using MS Office 2003 with the Access database format in 2002.

Thanks

Ross

'*******************************
Public Sub Link_Excel_Security()
'*******************************
ImportProtected "C:\A__A_Deal_Calendar\Access to Database.xls", "GMACRFC"
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
DoEvents
oWb.Close False
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing

End Sub

 

[Ross]

Rob,

Your observation would explain the behavior of the code.

I am using the Excel file to hold the security level of individual users and
want to prevent these users from having access to the table that holds the
security (ie change user Jdoe to administrator).

I am unable to manualy link to the spreadsheet. I get a wizard error that
says it is unable to find the file. "Check the file and its format". If I
remove the password from the spreadsheet, the wizard works just fine.

How can I have a usere lookup table that can't be open/changed without A
PASSWORD?

I know that I can hide the table.

Thanks Rob

Ross





I don't for many reasons, want to secure the database.

 

[Ross]

Yes it is Simple:

UserID SecurityRole Name
****** ********* *********
AOlson4 Operator Olson, Amy
ARuud Reader Ruud, Ann
AVillan Operator Villanueva, Andrea
BBalsam Reader Balsam, Brett
BBjorgo Operator Bjorgo, Benita

 

[Ross]

Isn't that what this code does? How is excel Automation Different?

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
DoEvents
oWb.Close False
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing

 

[Ross]

I Will Give it a try. Thanks

everything but the Docmd.Statement.

What you will need to do after you open the excel file is to start reading
row by row.
Since the columns are preset (and hopefully do not change), then you can
maneuver your way cell by cell to read the data.

This tip may offer some guidance.

Tip #2: Excel Automation: More than just a formula
http://www.fmsinc.com/free/tips.html#ExcelautomationVBA

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Alex Dybenko]

Hi Ross,
if you just open excel file with:
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword)

will it ask for password?
make excel visible before this:
oExcel.Visible=true

actually this code:
http://alexdyb.blogspot.com/2005/04/how-to-import-password-protected-excel.html
works fine at me.
can it be that you have 2 versions of excel installed?


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