Refresh Password protected excel from access

[ragsgold]

Hi All,

I have an excel file with table data and a pivot that is linked to one of the tables in my access file. this excel file is password protected. when I run a access macro or a VBA (which ever you suggest) it should open the excel file automatically feed in the password, refresh the macro in excel (there is a macro in excel to refresh work book) ;save the excel file password protect it and close the excel file.

Any suggestions ? I am fine with VBA or non VBA solutions like RunApp macro command lineetc

[June7]

Google: access vba refresh Excel password
Does this help:
http://www.access-programmers.co.uk/...d.php?t=171000

[ragsgold]

Tried the below code in your link - i get error for every linke I am not good at coding for sure i am making a mistake some where. could you please guide me as to where i can input file path and password ?

Sub OpenExcel(xlPath As String, password As String)
'xlPath is the path for the Excel file as a string. Ex. "C:\Book1.xls"
'password is the password for the workbook as a string. Ex. "mypassword"

On Error Resume Next

'Error if Excel app not currently open:
Const ERR_EXCEL_NOT_OPEN As Long = 429

'Excel Variables:
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'Open Excel Application
'===============================================
Set xlApp = GetObject(, "Excel.Application")

If Err.Number = ERR_EXCEL_NOT_OPEN Then

'If Excel not open, open new instance:
Set xlApp = New Excel.Application

End If

xlApp.Visible = True

'Open workbook:
Set xlWb = xlApp.Workbooks.Open(xlPath, , , , password)

'Refresh all pivot tables and save:
With xlWb
.RefreshAll
.Save
'.Close 'Remove comment to close workbook once refreshed
End With

'Clean variables:
Set xlWb = Nothing
Set xlApp = Nothing

End Sub

[June7]

How are you executing the procedure? A button click? If you are calling the sub from Click event, something like:


OpenExcel("the path string for your excel file", "the excel file password")