Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

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


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
Seamus2626
Asked:
Seamus2626
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Martin LissOlder than dirtCommented:
Try adding ActiveWorkbook.Save after or instead of line 20.
0
 
Seamus2626Author Commented:
Hey Martin, thanks but that has been tried - you can see 'gWorkBook.Save commented out
0
 
Martin LissOlder than dirtCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NorieVBA ExpertCommented:
Do you know which application is generating the message?
0
 
Seamus2626Author Commented:
I believe it is excel

Do you have a surpression line for PowerPoint?
0
 
Rgonzo1971Commented:
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
 
Seamus2626Author Commented:
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
 
Rgonzo1971Commented:
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
 
Seamus2626Author Commented:
Perfect! Thanks Rgonzo!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now