D
davidcw
Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
Sry, didn't realize my post was truncated. Here's the rest of the code:
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 opens the first embedded workbook as "Workbook2", the second as "Workbook3", etc. They are invisible.
Application.Activate
Set s = w.Worksheets("Pivot")
Set ptbl = s.pivottables(1)
xl.DisplayAlerts = False
ptbl.sourcedata = strSourceData 'Since it's open, you don't need to include the path but it's a good idea to do so anyway
DoEvents 'let the system catch up with your code
xl.DisplayAlerts = True
Application.Activate
ptbl.refreshtable
DoEvents 'let the system catch up with your code
* ** w.Close (False)'This is the offending line End If
End If
Next ShObj
I_Cheat:
Next SlObj
MsgBox "All embedded Excel spreadsheets in the file have been successfully updated.", vbOKOnly, "Process complete"
End If
Exit_Here:
'Clean up after yourself
If xl.workbooks.Count > 0 Then
For Each wb In xl.workbooks
wb.Close (False)
Next wb
Else
End If
Set w = Nothing
xl.Quit
Set xl = Nothing
ActivePresentation.Save
End Sub
I have worked around by writing a For Each control structure that tells XL to close every file that is not the raw data file (stays open for faster refresh) before moving to the next slide object. It works, but there's got to be an explanation.
Thanks,
David
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
Sry, didn't realize my post was truncated. Here's the rest of the code:
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 opens the first embedded workbook as "Workbook2", the second as "Workbook3", etc. They are invisible.
Application.Activate
Set s = w.Worksheets("Pivot")
Set ptbl = s.pivottables(1)
xl.DisplayAlerts = False
ptbl.sourcedata = strSourceData 'Since it's open, you don't need to include the path but it's a good idea to do so anyway
DoEvents 'let the system catch up with your code
xl.DisplayAlerts = True
Application.Activate
ptbl.refreshtable
DoEvents 'let the system catch up with your code
* ** w.Close (False)'This is the offending line End If
End If
Next ShObj
I_Cheat:
Next SlObj
MsgBox "All embedded Excel spreadsheets in the file have been successfully updated.", vbOKOnly, "Process complete"
End If
Exit_Here:
'Clean up after yourself
If xl.workbooks.Count > 0 Then
For Each wb In xl.workbooks
wb.Close (False)
Next wb
Else
End If
Set w = Nothing
xl.Quit
Set xl = Nothing
ActivePresentation.Save
End Sub
I have worked around by writing a For Each control structure that tells XL to close every file that is not the raw data file (stays open for faster refresh) before moving to the next slide object. It works, but there's got to be an explanation.
Thanks,
David