OPEN A PASSWORD PROTECTED EXCEL FILE

[Gary B]

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

 

[Geoff]

MyXL.Workbooks.Open Filename:=strFileName, Password:=strPassword

Presumably you had a reason for declaring as a string:
xlFileAttribute As String
Help says that GetAttr() returns an integer.

Regards
Geoff