I have an embedded macro that runs when a certain spreadsheet is called from
inside access. For the most part the sheet works on certain machines but will
not run on all computers. I am kind of baffled why the spreadsheet will not
open up and refresh.

The code I have under the spreadsheet is:

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Macro1
Application.WindowState = xlMaximized
Wn.WindowState = xlMaximized
End Sub

Module1
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
End Sub

If the workbook opens on a sheet that does not have a pivot table, then the
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e will fail. If the table
is on Sheet 1 of all workbooks, then you can simply put Sheets(1).Activate in
the Macro1 code to ensure it is the active sheet.


"Cameron" wrote:

I have an embedded macro that runs when a certain spreadsheet is called from
inside access. For the most part the sheet works on certain machines but will
not run on all computers. I am kind of baffled why the spreadsheet will not
open up and refresh.

The code I have under the spreadsheet is:

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Macro1
Application.WindowState = xlMaximized
Wn.WindowState = xlMaximized
End Sub

Module1
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
End Sub

Yes all the excel spreadsheets have pivot tables on them. But do you think
putting the Sheets clause would be prudent anyway? I built them all to only
have one sheet but am trying to determine why the sheet is not updating.
Seems kind of odd.



"JLGWhiz" wrote:

If the workbook opens on a sheet that does not have a pivot table, then the
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e will fail. If the table
is on Sheet 1 of all workbooks, then you can simply put Sheets(1).Activate in
the Macro1 code to ensure it is the active sheet.


"Cameron" wrote:

I have an embedded macro that runs when a certain spreadsheet is called from
inside access. For the most part the sheet works on certain machines but will
not run on all computers. I am kind of baffled why the spreadsheet will not
open up and refresh.

The code I have under the spreadsheet is:

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Macro1
Application.WindowState = xlMaximized
Wn.WindowState = xlMaximized
End Sub

Module1
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
End Sub

I don't know. I don't work with Pivot Tables but when code does not execute
as designed, then I start looking for disconnects and that was the first
possibility that came to mind. Looking at the help file, I noticed that it
refers to a specific range as part of the Pivot Table Refresh method:

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
pvtTable.RefreshTable

Could that have an affect?




"Cameron" wrote:

Yes all the excel spreadsheets have pivot tables on them. But do you think
putting the Sheets clause would be prudent anyway? I built them all to only
have one sheet but am trying to determine why the sheet is not updating.
Seems kind of odd.



"JLGWhiz" wrote:

If the workbook opens on a sheet that does not have a pivot table, then the
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e will fail. If the table
is on Sheet 1 of all workbooks, then you can simply put Sheets(1).Activate in
the Macro1 code to ensure it is the active sheet.


"Cameron" wrote:

I have an embedded macro that runs when a certain spreadsheet is called from
inside access. For the most part the sheet works on certain machines but will
not run on all computers. I am kind of baffled why the spreadsheet will not
open up and refresh.

The code I have under the spreadsheet is:

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Macro1
Application.WindowState = xlMaximized
Wn.WindowState = xlMaximized
End Sub

Module1
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
End Sub

Thank you, I will give it a try.

Just kind of dreading editing all 30 spreadsheets.

:-)

"JLGWhiz" wrote:

I don't know. I don't work with Pivot Tables but when code does not execute
as designed, then I start looking for disconnects and that was the first
possibility that came to mind. Looking at the help file, I noticed that it
refers to a specific range as part of the Pivot Table Refresh method:

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
pvtTable.RefreshTable

Could that have an affect?




"Cameron" wrote:

Yes all the excel spreadsheets have pivot tables on them. But do you think
putting the Sheets clause would be prudent anyway? I built them all to only
have one sheet but am trying to determine why the sheet is not updating.
Seems kind of odd.



"JLGWhiz" wrote:

If the workbook opens on a sheet that does not have a pivot table, then the
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e will fail. If the table
is on Sheet 1 of all workbooks, then you can simply put Sheets(1).Activate in
the Macro1 code to ensure it is the active sheet.


"Cameron" wrote:

I have an embedded macro that runs when a certain spreadsheet is called from
inside access. For the most part the sheet works on certain machines but will
not run on all computers. I am kind of baffled why the spreadsheet will not
open up and refresh.

The code I have under the spreadsheet is:

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Macro1
Application.WindowState = xlMaximized
Wn.WindowState = xlMaximized
End Sub

Module1
Sub Macro1()
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
End Sub