Open Passworded Excel file from Access 1

[jw45]

I am trying to automate the transfer of data from Access to Excel. The file is password protected and contains links to refresh.

I want to open the workbook, give it the password, refresh the data, then append the access data to the end of the existing data in Excel.

It appears that the password and refresh portion of my code below is not working correctly. When I change to the Excel app it has the dialog box asking for a password. If I manually type in the password and manually refresh/update the links, the remainder of the vb code runs ok.

How can I pass this info to the workbook object?

I'm using Access 2002/Excel2002 and XP.

Thanks in advance for your help.
JW

This is what I have so far:

Function cmdXl_Click()
On Error GoTo Err_cmdXl_Click

Dim oXL As Object 'Excel.Application
Dim oWb As Object 'Excel.Workbook
Dim oSh As Object 'Excel.Worksheet
Dim strFileLoc As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim LastRow As Long
Dim strWorkBookOpenPWD As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Weekly Scrap Report") 'dbOpenSnapshot
        
strFileLoc = "D:\QA\Scrap\Scrap_Data.xls"
strWorkBookOpenPWD = "mva"
  
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWb = oXL.Workbooks.Open(filename:=strFileLoc, Password:=strWorkBookOpenPWD, _
            ReadOnly:=False)
Set oSh = oWb.Sheets(1)
LastRow = oSh.[A65536].End(-4162).Row  'xlUp
 With oXL.Application
      oSh.Range("A2").Select
      oSh.cells(LastRow + 1, "A").CopyFromRecordset rst
 End With
       
oWb.Save
oWb.Close
oXL.Quit

'Do not forget to clean up properly:
Set oSh = Nothing
Set oWb = Nothing
Set oXL = Nothing
MsgBox "Finished"

End Function

 

[lupins46]

I would guess you have set two passwords in your workbook.

Set oWb = oXL.Workbooks.Open(FileName:=strFileLoc, Password:=strWorkBookOpenPWD, writerespassword:=strWorkBookOpenPWD,ReadOnly:=False)

 

[jw45]

Thanks lupins..... that got my past the password dialog box.

Now it has a dialog box:
This workbook contains links to other data sources.
Update? Yes/No.

How do I get past this? I want to select 'No'

Thanks,
JW

 

[lupins46]

I believe it's :
UpdateLinks:=0

 

[jw45]

Thank you ... both answers worked! A 'STAR' for you!