Want a VBA code in powerpoint to show all charts in excel to different slides in ppt

Question

I have developed vba code in excel to show all charts in excel to different slides in ppt. But i want the vba code to be implemented in powerpoint instead of excel so that i can create an addin in powerpoint with that macro. I have tried to implement the excel vba code in powerpoint but that doesnot work in ppt. The problem is that it is copying the charts from the excel to the ppt slides.`I have used the following code in ppt but with no success.

Sub Button1()

    Set pptApp = New PowerPoint.Application
    Set pptPres = pptApp.ActivePresentation

    Dim xlApp As Object
    Dim xlWorkBook As Object
    Dim wb As Workbook

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True
    Set wb = xlApp.Workbooks.Open("C:\Users\tonmoy.roy\Desktop\Meeting Files\Monthly Review July 10.xls", True, False)

    Dim WAIT As Double

    WAIT = Timer
    While Timer < WAIT + 10
        DoEvents  'do nothing
    Wend

    wb.Activate

    Dim ws As Worksheet
    Dim intChNum As Integer
    Dim objCh As Object

    'Count the embedded charts.
    For Each ws In wb.Worksheets
        intChNum = intChNum + ws.ChartObjects.Count
    Next ws

    'Check if there are chart (embedded or not) in the active workbook.
    If intChNum + ActiveWorkbook.Charts.Count < 1 Then
        MsgBox "Sorry, there are no charts to export!", vbCritical, "Ops"
        Exit Sub
    End If


    'Loop through all the embedded charts in all worksheets.
    For Each ws In wb.Worksheets
        For Each objCh In ws.ChartObjects
            Call pptFormat(objCh.Chart)
        Next objCh
    Next ws

    'Loop through all the chart sheets.
    For Each objCh In wb.Charts
        Call pptFormat(objCh)
    Next objCh

    'Show the power point.
    pptApp.Visible = True

    'Cleanup the objects.
    Set pptSlide = Nothing
    Set pptPres = Nothing
    Set pptApp = Nothing

    'Infrom the user that the macro finished.
    MsgBox "The charts were copied successfully to the new presentation!", vbInformation, "Done"

End Sub

Private Sub pptFormat(xlCh As Chart)
    'Formats the charts/pictures and the chart titles/textboxes.

    Dim chTitle As String
    Dim j As Integer

    On Error Resume Next
    'Get the chart title and copy the chart area.
    chTitle = xlCh.ChartTitle.Text
    xlCh.ChartArea.Copy

    'Count the slides and add a new one after the last slide.
    pptSlideCount = pptPres.Slides.Count
    Set pptSlide = pptPres.Slides.Add(pptSlideCount + 1, ppLayoutBlank)

    'Paste the chart and create a new textbox.
    pptSlide.Shapes.PasteSpecial ppPasteJPG
    If chTitle <> "" Then
        pptSlide.Shapes.AddTextbox msoTextOrientationHorizontal, 12.5, 20, 694.75, 55.25
    End If

    'Format the picture and the textbox.
    For j = 0 To pptSlide.Shapes.Count
        With pptSlide.Shapes(j)
            'Picture position.
            If .Type = msoPicture Then
                .Top = 87.84976
                .Left = 33.98417
                .Height = 422.7964
                .Width = 646.5262
            End If
            'Text box position and formamt.
            If .Type = msoTextBox Then
                With .TextFrame.TextRange
                    .ParagraphFormat.Alignment = ppAlignCenter
                    .Text = chTitle
                    .Font.Name = "Tahoma (Headings)"
                    .Font.Size = 28
                    .Font.Bold = msoTrue
                End With
            End If
        End With
    Next j
End Sub

Answer 1

Private Sub pptFormat(xlCh As Chart) should be :

Private Sub pptFormat(xlCh As Excel.Chart).

PowerPoint has a Chart in its Object Model, so you need to change it to explicitly say Excel.Chart

I am assuming you already have the references

If intChNum + ActiveWorkbook.Charts.Count < 1 Then should be:

If intChNum + wb.Charts.Count < 1 Then

Also your variables aren't declared properly as far as I can see in the pptFormat function. Dim them and use Option Explicit in your coding.

Option Explicit helps in long run more than any inconvenience of having to type out decs.

Answer 2

Tonmoy Roy, You should ask your second question in another thread. But here is some code to have you select a file and get it's name, path or just the entire name/path

Set XLapp = New Excel.Application
'choose the data file
     With Application.FileDialog(msoFileDialogFilePicker)
       .AllowMultiSelect = False
               ' Set the title of the dialog box.
      .Title = "Select the Data File (Data File.xlsx)."
        'clear filters so all file are shown
      .Filters.Clear
             ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
      If .Show = True Then
        FullName = .SelectedItems(1) 'name and path 
      End If
   End With
   fname = Dir(FullName) ' gets just the file name and not the path
   XLapp.Visible = True
Set xlWorkBook = XLapp.Workbooks.Open(FullName, False, True)  'Opens the data xlsx file