Open Excel and refresh

[mosh]

Hello All,

I have a database that runs a set of de-duplication and update queries. After this is all done, I would like to Open a password protected excel file and then update the pivots automatically.

Can someone please assist in module that can do this?

Thank you,
________
Zoloft help

 

[honda882000]

How far have you gotten? Are you familiar with Excel vba objects?

If it is the refreshing you are stuck in, this example will refresh all queries/data in your Excel sheet:

'In this case, my workbook is called xlWb

xlWb.RefreshAll

 

[mosh]

I could put that as an autoexec in excel?

But the main thing i require is to actually open the workbook frm access automatically after the macro has run. The workbook also has a password.

The workbook should also save after it has refreshed.

Thanks
________
Live sex

 

[honda882000]

I hope this will get you started:

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