Solved

Surpressing messages VBA

Posted on 2016-10-18
9
69 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 46

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 46

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 33

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 49

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 49

Accepted Solution

by:
Rgonzo1971 earned 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Setting the Scene Animations in PowerPoint are a great tool to convey messages when used carefuly with the content of your slides. There are plenty of animation effects and options, including a Repeat feature for individual animation effects. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

785 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