ADODB Testing for Password

[Aposto]

I have a routine which opens various Excel files and then imports the data in
to Access:

Sub OpenExcelConn(conn As ADODB.Connection, rst As ADODB.Recordset, FileName
As String, SheetName As String)
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
FileName & ";" & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
rst.CursorLocation = adUseClient
rst.Open "[" & SheetName & "$]", conn, adOpenStatic, adLockOptimistic
End Sub

I have recently added some excel files with passwords to open them and they
obviously fail the above code. How can I modify the code to open Excel files
with or with out passwords?

 

[Stefan Hoffmann]

hi,

I have recently added some excel files with passwords to open them and they
obviously fail the above code. How can I modify the code to open Excel files
with or with out passwords?

Wow, you need to clean up your code...

Sub OpenExcelConn(conn As ADODB.Connection, rst As ADODB.Recordset, FileName

Why do you name your function "open connection" if it does more than that?

Use error handling and take a look at

http://connectionstrings.com/excel-2007


mfG
--> stefan <--

 

[David C. Holley]

If the Excel spreadsheet is passward protected, you'll have to supply the
password, which I'm fairly certain the OpenWorkseet function of the Excel
Application object allows you to do. The catch though is that you have to
store the password so that it can be retrieved.