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?

hi,

On 25.02.2010 17:23, Aposto wrote:
> 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 <--

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.

"Aposto" <(E-Mail Removed)> wrote in message
news2663C6F-77C6-443D-AB67-(E-Mail Removed)...
>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?