trouble modifying embedded XL workbooks with VBA

[davidcw]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Office 2004 with all updates, OS X 10.5 with all updates, MacBook Pro with Intel processor. New to Mac but have been writing VBA in Excel, Access and Word professionally for more than ten years.

I have a presentation where each slide has an embedded Xl workbook permitting chart display. The workbooks all have two worksheets -- one containing the chart, the other containing a pivot table that is the source for the chart series and values. So far, so good.

Here is the tricky part: Each of those embedded workbook pivot tables has the same data source, a rather large XL file that is updated weekly with new raw data. I have a VBA routine that forces all pivots to refresh by reading a named range in the raw data file. The raw data file remains outside the PPT file. This keeps my PPT file size down by avoiding embedding the same raw data 20 times.

The first problem: crash, crash, crash. Leopard becomes unhappy when it senses the MS software is trying to open a second instance of a running application. Anybody have any tips on settings/preferences/techniques to avoid this?

The second problem: when it does not crash, Xl seems to have difficulty closing the embedded file after it has been modified. Sometimes it does, sometimes it does not. Here is the full procedure with the offending line starting with ***. The first part opens the large raw data file. The second part opens each embedded workbook individually, updates the pivot table's source data and refreshes the pivot (triggering automatic chart redraw) and closes the embedded workbook.

Sub RefreshEmbeddedPivotTables()
Dim strPath As String
Dim strSourceData As String
Dim strSourceDataFile As String
Dim flagNoEmbeddedXLFiles As Boolean
Dim w As Object
Dim xl As Object
Dim wSourceDataFile As Object
Dim s As Object
Dim ptbl As Object
Dim n As Integer
strPath = "Macintosh HD:Users:davidcwocuments:"
strSourceData = "'[consolidated raw data rolling 4 months.xls]Raw Data'!ROLLING_4_MONTHS"
'Extract source data file name from strSourceData
strSourceDataFile = Mid(strSourceData, 3, InStr(1, strSourceData, "]") - 3)
'Loop through slide shapes until you find an embedded worksheet, then open source data file for speedy refresh
flagNoEmbeddedXLFiles = True
For Each SlObj In Application.ActivePresentation.Slides
For Each ShObj In SlObj.Shapes

If ShObj.Type = msoEmbeddedOLEObject Then

If Mid$(ShObj.OLEFormat.ProgID, 1, 11) = "Excel.Sheet" Then

flagNoEmbeddedXLFiles = False
Set w = ShObj.OLEFormat.Object 'I know you didn't say "Open", but this action opens the embedded workbook
'as "Workbook1"
Set xl = w.Parent
xl.DisplayAlerts = False
xl.Visible = False
Set wSourceDataFile = xl.workbooks.Open(strPath & strSourceDataFile, 0, True)
'With DisplayAlerts turned off and the chosen parameters, Excel opens the source file read-only with macros disabled
wSourceDataFile.Windows(1).Visible = False
DoEvents 'let the system catch up with your code
Application.Activate 'Let user watch the show
'You don't need the embedded workbook open any more
xl.workbooks(1).Close False
xl.DisplayAlerts = True
GoTo Resume_Here
End If

End If

Next ShObj
Next SlObj
Resume_Here:
If flagNoEmbeddedXLFiles = True Then
MsgBox "There are no embedded Excel spreadsheets in the file.", vbOKOnly, "Process complete"
GoTo Exit_Here
Else
For Each SlObj In Application.ActivePresentation.Slides
n = n + 1
For Each ShObj In SlObj.Shapes

If ShObj.Type = msoEmbeddedOLEObject Then

If Mid$(ShObj.OLEFormat.ProgID, 1, 11) = "Excel.Sheet" Then

SlObj.Select
Set w = ShObj.OLEFormat.Object
'Excel ope