Referencing to chart object

[Joaoack]

Hello,

My problem for today is that I got charts that arent placed inside sheets. Those charts I have are the "sheet itself", meaning that the chart object represents a new separated worksheet inside a given workbook.

Therefore, I have a problem looping through those "outside sheets" charts. Part of the code follows:

Dim cht As Excel.ChartObject
For Each cht In .............

Any suggestions?

Thanks

[JosephP]

Please Login or Register  to view this content.

for instance

[Joaoack]

Please Login or Register  to view this content.

for instance

I get run-time error '13': Type mismatch

[JosephP]

I don't see how you could-did you change the declaration of cht?

[Joaoack]

I don't see how you could-did you change the declaration of cht?

Not really. Entire code follows bellow for further analysis:


Sub CreatePowerPoint()

Dim Wb As Workbook
Dim Ws_Dados As Worksheet
Dim Ws_Tab As Worksheet
Dim appOutlook As Object
Dim appMail As Object
Dim Rng As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim strBody As String
Dim Hoje As String
Dim Chart_Count As Long
Dim Pasta As String
Dim Linha_Divulgação As Long
Dim Titulo_Email As String
Dim Diretorio_Planilha As String
Dim Arquivo_Planilha As String
Dim Ate_Grafico As Long
Dim Diretorio_Figuras As String
Dim Exporta_Portal As String
Dim Nome_Grafico As String
Dim Dia As String
Dim Mes As String
Dim Ano As String

ThisWorkbook.Sheets("Menu").Activate


Excel_To_PPT = Application.WorksheetFunction.Max(Range("A3:A5000"))

For I = 1 To Excel_To_PPT



Linha_Divulgação = WorksheetFunction.Match(I, Range("A1:A5000"), 1) 'Usado pra fazer match dos demais parâmetros
Diretorio_Planilha = WorksheetFunction.Index(Range("A1:AK5000"), Linha_Divulgação, 5) & "\" 'Diretório no qual a planilha fica localizada
Arquivo_Planilha = WorksheetFunction.Index(Range("A1:AK5000"), Linha_Divulgação, 6) 'Nome do arquivo excel
Ate_Grafico = WorksheetFunction.Index(Range("A1:AK5000"), Linha_Divulgação, 7) 'Irá exportar até o gráfico especificado
Tabela = WorksheetFunction.Index(Range("A1:AK5000"), Linha_Divulgação, 8)



I = I + 1

On Error Resume Next

Teste_Aberto = False

'Se workbook estiver aberta, nao faz nada. Caso contrário, abre
For Each Workbook In Workbooks
If Workbook.Name = Arquivo_Planilha Then
Teste_Aberto = True
Exit For
End If
Next Workbook

If Teste_Aberto = False Then
Workbooks.Open Filename:=(Diretorio_Planilha & Arquivo_Planilha)
End If

Set Wb = Workbooks(Arquivo_Planilha)
Set appOutlook = CreateObject("Outlook.Application")
Set appMail = appOutlook.CreateItem(olMailItem)


'Conta quantos graficos tem
If Ate_Grafico = 0 Then
Chart_Count = Wb.Charts.Count
Else
Chart_Count = Ate_Grafico
End If


'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

'First we declare the variables we will be using
Dim newPowerPoint As PowerPoint.Application
Dim activeSlide As PowerPoint.Slide
Dim cht As Excel.ChartObject

'Look for existing instance
On Error Resume Next
Set newPowerPoint = GetObject(, "PowerPoint.Application")
On Error GoTo 0

'Let's create a new PowerPoint
If newPowerPoint Is Nothing Then
Set newPowerPoint = New PowerPoint.Application
End If
'Make a presentation in PowerPoint
If newPowerPoint.Presentations.Count = 0 Then
newPowerPoint.Presentations.Add
End If

'Show the PowerPoint
newPowerPoint.Visible = True

'Loop through each chart in the Excel worksheet and paste them into the PowerPoint

Workbooks(Arquivo_Planilha).Sheets(1).Activate
For Each cht In ActiveWorkbook.Charts

'Add a new slide where we will paste the chart
newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

'Copy the chart and paste it into the PowerPoint as a Metafile Picture
cht.Select
ActiveChart.ChartArea.Copy
activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

'Set the title of the slide the same as the title of the chart
activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text

'Adjust the positioning of the Chart on Powerpoint Slide
newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125

activeSlide.Shapes(2).Width = 200
activeSlide.Shapes(2).Left = 505

Next

AppActivate ("Microsoft PowerPoint")
Set activeSlide = Nothing
Set newPowerPoint = Nothing

Next

End Sub

[JosephP]

Please Login or Register  to view this content.

not

Please Login or Register  to view this content.

:-)

[Joaoack]

I tried it

Then I get the message "Compile error: Method or data member not found" on the line where is written "'Set the title of the slide the same as the title of the chart
activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text"

[JosephP]

you have to use

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

[Joaoack]

Im advancing now. Still getting problems with the following line "ActiveSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select"

I get the message "Runtime error '-2147188160 (80048240)' Shapes (unknown member): Invalid request. The specified data type is unavailable".

I am sorry for bothering you so many times, Joseph. Your help is really being appreciated

[JosephP]

I've never used powerpoint so I don't know-perhaps

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

[Joaoack]

Hmm didnt work.

Should I make a thread regarding this one specific issue?

Anyways, thank you so much for your attention and solutions!

[JosephP]

there's a first time for everything-gimme a few minutes and I'll set up a test rig ;-)

[Joaoack]

Thank you so much JosephP!

[Joaoack]

My guess is that the problem has something to do with the excel.chartobject (as it was first declared) instead of chart

[JosephP]

I don't think so-the code works for me

[Joaoack]

I cant get it to work either

The original code was supposed to work with charts inside worksheets. I modified it and now I cant get it to work ;(

[JosephP]

I can't help-it does work for me and pastes into powerpoint

[Joaoack]

That is so wierd!

Maybe it is something related to PPT itself (layout of the slide maybe?) or the version that I am using.
Did you try using a chart sheet or a chart inside a worksheet?

[JosephP]

a chart sheet

[Joaoack]

Wierd... No clues right now

[Joaoack]

Found some stuff saying that the problem is related to Excel losing focus when it opens PPT

[JosephP]

that didn't happen for me-win 7. does it fail on the first chart for you?

[Joaoack]

It does fail on 1st chart.