Solved

Surpressing messages VBA

Posted on 2016-10-18
9
59 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
 
LVL 33

Expert Comment

by:Norie
ID: 41848488
Do you know which application is generating the message?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Outline From PowerPoint 2010 it is possible to have shapes appear in front of video, in earlier versions video always played in front of other shapes. This means it is possible to have captions animated to appear in front of video. Users who h…
 Regular Expressions Microsoft Word has sophisticated search tools that can search for patterns. For example if you wanted to search for all UK phone numbers that followed a pattern of five digits, a space and then six digits you can easily do th…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now