Seamus2626
asked on
Surpressing messages VBA
Hi,
I have a load of embedded charts in a PPT presentation
My code opens the embedded chart and pastes in some new data, then closes the embedded chart.
I have 100's of these charts. I am now receiving the message "Do you want to save changes to Chart 2"
We need to nort save and surpress the message.
I have tried application.displayalerts false to no avail
Any suggestions would be appreciated!
Thanks
Seamus
I have a load of embedded charts in a PPT presentation
My code opens the embedded chart and pastes in some new data, then closes the embedded chart.
I have 100's of these charts. I am now receiving the message "Do you want to save changes to Chart 2"
We need to nort save and surpress the message.
I have tried application.displayalerts false to no avail
Any suggestions would be appreciated!
Thanks
Seamus
Sub Update_Graph(shtName As String, rngName As String, slideNum As String, shpName As String)
Sheets(shtName).Range(rngName).Copy
Dim myChart As Object
Dim myChartData As Object
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
Set myChart = objPres.Slides(slideNum).Shapes(shpName).Chart
Set myChartData = myChart.ChartData
myChartData.Activate
Set gWorkBook = myChart.ChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)
gWorkSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
Calculate
Application.DisplayAlerts = False
'gWorkBook.Save
gWorkBook.Close False
Application.DisplayAlerts = True
Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing
End Sub
Try adding ActiveWorkbook.Save after or instead of line 20.
ASKER
Hey Martin, thanks but that has been tried - you can see 'gWorkBook.Save commented out
Sorry, I missed that but do me a favor and try my line anyhow. BTW, as an aside, naming things gWhatever normally indicates that the variable has global scope and when it's defined in a sub, as you probably know, its scope is limited to that sub.
Do you know which application is generating the message?
ASKER
I believe it is excel
Do you have a surpression line for PowerPoint?
Do you have a surpression line for PowerPoint?
Hi,
pls try
pls try
Sub Update_Graph(shtName As String, rngName As String, slideNum As String, shpName As String)
Sheets(shtName).Range(rngName).Copy
Dim myChart As Object
Dim myChartData As Object
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
Set myChart = objPres.Slides(slideNum).Shapes(shpName).Chart
Set myChartData = myChart.ChartData
myChartData.Activate
Set gWorkBook = myChart.ChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)
gWorkSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
Calculate
gWorkBook.Application.DisplayAlerts = False
'gWorkBook.Save
gWorkBook.Close False
gWorkBook.Application.DisplayAlerts = True
Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing
End Sub
ASKER
Hi,
That didnt work
We have an internal classification where when we save a new excel doc, it asks us whether it is an "Internal" or "External" doc
Would this be affecting the surpression code?
Thanks
That didnt work
We have an internal classification where when we save a new excel doc, it asks us whether it is an "Internal" or "External" doc
Would this be affecting the surpression code?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thanks Rgonzo!