Access 97: Link to Excel WB with password?

[Savil]

Hi All
I need to connect an Access 97 DB to an Excel worksheet where the workbook has an opening password. Is there a way in VBA to pass the password in the connect string. Every method I have tried has failed but I will be the first to put my hand up and say connecting different applications is not my bag
Thanks in advance

 

[TKaminski]

This reply my be a little late to be of help to you, but it may help others.

I have used the method found at:

http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaro97ta/html/msdn_autooff.asp

Sub AutomateExcel()
' This procedure is a brief sample showing
' how to automate Excel.

' Remember to set a reference to the most current available
' Microsoft Excel object library.

'>>>I added this hint on how to add a reference.<<<
'   How to add a reference:
'   1)Click on Tools/References
'   2)Scroll down the list and find _
        "Microsoft Excel ## Object Library"
'   3)Place a check next to it
'   4)Click 'OK"
'>>>End<<<

    ' Declare object variables.
    Dim appXl As Excel.Application
    Dim wrkFile As Workbooks
    
    ' Set object variables.
    Set appXl = New Excel.Application
    Set wrkFile = appXl.Workbooks

'>>>I changed the MS example's path to work with my folders and files.<<<
    ' Opens a file that has no password.
    'wrkFile.Open "c:\data\NameOfWorkBook-WithOut-Password.xls"
    
'>>>This is the line of code that I changed to work with a password.<<<
    ' Opens a file with known password.
    wrkFile.Open "c:\data\NameOfWorkBook-With-Password.xls", , , , "MyPassword"
    
    ' Display Excel.
    appXl.Visible = True
    
    MsgBox "At this point Excel is open and displays a document." & Chr$(13) & _
    "The following statements will close the document and then close Excel."
    
    ' Close the file.
    wrkFile.Close
    
    ' Quit Excel.
    appXl.Quit

    ' Close the object references.
    Set wrkFile = Nothing
    Set appXl = Nothing
    
End Sub