Solved

Surpressing messages VBA

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

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Seamus2626
ID: 41848518
I believe it is excel

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

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 48

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Background   Certain code in VBA requires initialization, such as application events. The app initialization is often triggered by the Auto_Open sub which is a special procedure that runs when an add-in loads. More significantly, this sub does n…
Many programs have tried to outwit PowerPoint in terms of technology and skill. These programs, however, still lack several characteristics that PowerPoint has possessed from the start. Here's why PowerPoint replacements won't entirely work for desi…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

708 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

16 Experts available now in Live!

Get 1:1 Help Now