Problem getting data to refresh

[ksminor]

Hi All.
I am using access to open, refresh, and close an excel spreadsheet. The
code below works fine as far as opening and closing the spreadsheet,
but it doesn't refresh the data. Any idea why?
Thanks in advance for any help.

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


DoCmd.SetWarnings True


xlFile = "c:\ActualHires.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 FileName:=xlFile, Password:="ABCD"



'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

 

[Martin Fishlock]

Hi KS,

From what I see in your code you are updating source data that is linked to
a pivot table in the workbook.

You therefore update the table but you omitted to refresh the pivot table

This is done by

MyXL.Application.ActiveWorkbook.Sheets( _
"sheet2").PivotTables("PivotTable1").PivotCache.Refresh

also it may be better to write the code as follows:


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

DoCmd.SetWarnings True

xlFile = "c:\ActualHires.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
With MyXL.Application
.DisplayAlerts = False
.AlertBeforeOverwriting = False
.Visible = True
End With

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

' Update Pivottables and Save the File

With MyXL.Application.ActiveWorkbook
.Worksheets("sheet1").Range("A16").Value = "opened"
'>>>>>>> check the names here >>>>>>>>>>>>>>
.Worksheets("sheet2").PivotTables("PivotTable1").PivotCache.Refresh
.Close SaveChanges:=True
End With
'
' 'Save Excel file, close & destroy all Excel objects
' 'Arm all warnings before quiting Excel
With MyXL.Application
.DisplayAlerts = True
.AlertBeforeOverwriting = True
.Quit
End With
Set MyXL = Nothing


End Sub