MyXL.Workbooks.Open Filename:=xlFile, password:="ABCD"

--
Regards,
Tom Ogilvy



Gary B wrote in message
...
Hi,

I have an Access app that provides data for an Excel
Pivot Table.

Within the Access app, I wish to open the Excel file, and
update the pivot table, then save the Excel file.

The Excel file is password protected.

I use the following code, which works fine for
unprotected Excel files. How do I amend the code to pass
the password, (which I need to do twice) ?

Sub pwfile()
Dim MyXL As Object, xlFileAttribute As String
Dim xlFile As String, xlpw As String

DoCmd.SetWarnings False

xlFile = "c:\pw.xls"

'Set file attribute
xlFileAttribute = GetAttr(xlFile) ' Returns 1.
If xlFileAttribute = 1 Then
SetAttr (xlFile), vbNormal
End If

'Defer error trapping.
'If the application isn't running, an error occurs.
On Error Resume Next
Set MyXL = GetObject(, "Excel.Application")
Set MyXL = CreateObject("Excel.Application")

'Clear Err object in case error occurred.
Err.Clear

'Disarm all warnings
MyXL.Application.DisplayAlerts = False
MyXL.Application.AlertBeforeOverwriting = False
MyXL.Application.Visible = True
MyXL.Workbooks.Open (xlFile)

'Update Pivottables and Save the File

MyXL.Application.Activeworkbook.Sheets
("sheet1").Select
MyXL.Application.ActiveSheet.Range("A16").Value
= "opened"
MyXL.Application.Activeworkbook.Save
MyXL.Application.Activeworkbook.Close

'Save Excel file, close & destroy all Excel objects
'Arm all warnings before quiting Excel
MyXL.Application.DisplayAlerts = True
MyXL.Application.AlertBeforeOverwriting = True
MyXL.Application.Quit
Set MyXL = Nothing

End Sub

Works great, but also need to open for "write" purposes.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Look in Excel VBA help for the workbook.open event. There are several
password option arguments. You should be able to open it and do what you
want by supplying the correct password.

--
Regards,
Tom Ogilvy


"Gary Burke" wrote in message
...

Works great, but also need to open for "write" purposes.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!