Import Excel With Password

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
By observation, openin the excel file and the DoCmd.TransferSpreadsheet are
two independent identities.

Just for the suggestion that it is... If you are receiving only 1 excel file
that you have to transfer data into Access, why not just manually open it
and dump the contents into an Excel file that does not have a password
associated with it.

--
Rob Mastrostefano

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

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
 
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.
 
What is the format of the Excel sheet? Is it simple, like this:

First Name Last Name UserType
------------------------------------------------------------
Joe Dude User
Jane Dudette User
Super User Administrator



--
Rob Mastrostefano

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

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
 
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
 
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
 
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
 
I Will Give it a try. Thanks

RobFMS said:
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
Click to expand...
 
Back
Top