Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Surpressing messages VBA

Posted on 2016-10-18
9
Medium Priority
?
114 Views
Last Modified: 2016-10-19
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


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

Open in new window

0
Comment
Question by:Seamus2626
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41848418
Try adding ActiveWorkbook.Save after or instead of line 20.
0
 

Author Comment

by:Seamus2626
ID: 41848436
Hey Martin, thanks but that has been tried - you can see 'gWorkBook.Save commented out
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41848465
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:Norie
ID: 41848488
Do you know which application is generating the message?
0
 

Author Comment

by:Seamus2626
ID: 41848518
I believe it is excel

Do you have a surpression line for PowerPoint?
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41848553
Hi,

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

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 41849647
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
0
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41849661
then 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.Application.EnableEvents = False
'gWorkBook.Save
gWorkBook.Close False
gWorkBook.Application.EnableEvents = True
gWorkBook.Application.DisplayAlerts = True
Set gWorkSheet = Nothing
Set gWorkBook = Nothing
Set gChartData = Nothing
Set myChart = Nothing

End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 41849834
Perfect! Thanks Rgonzo!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question