Share via

VBA macro error

Anonymous
2018-07-10T16:17:54+00:00

I am trying to run the code below, but a dialog box pops up saying 'Compile error: user-defined type not defined.' It highlights the first line in yellow and points at it. Can someone help me fix this issue? Much thanks!

Sub PrintLinkedPowerpoints_FINAL()

    Dim pptApp As PowerPoint.Application

    Set pptApp = GetObject(, "PowerPoint.Application")

    Dim pptPres As PowerPoint.Presentation

    Set pptPres = pptApp.Presentations("Presentation1.pptx")

    Set nameRng = Excel.Application.Workbooks("MortalityGraphs_RATES_forReport_READY.FOR.CHECK.xlsx").Sheets("All Drug Overdose -Age-Adj Rate").Range("$A$2:$F$7")

    For i = 1 To nameRng.Rows.Count

        groupName = nameRng.Cells(i, 1).Value

        Excel.Application.Workbooks("MortalityGraphs_RATES_forReport_READY.FOR.CHECK.xls").Sheets("All Drug Overdose -Age-Adj Rate").Range("A9") = groupName

        pdf_file = ThisWorkbook.Path & "" & groupName & " MacroTest1.pdf"

        pptPres.UpdateLinks

        pptApp.Activate

        pptPres.SaveCopyAs Filename:=Filename

        pptPres.SaveAs Filename:=pdf_file, FileFormat:=ppSaveAsPDF

    Next i

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2018-07-10T17:49:23+00:00

    If you copy a code from one file to an other you have to a) set the same references as in the original file or b) use late binding code, as shown below.

    Note:

      I've commented out one line, because the variable "Filename" is not set in that code.

    Andreas.

    Sub PrintLinkedPowerpoints_FINAL()

      Dim pptApp As Object 'PowerPoint.Application

      Dim pptPres As Object 'PowerPoint.Presentation

      Dim nameRng As Range

      Dim i As Long

      Dim groupName, pdf_file

      Const ppSaveAsPDF = 32

      Set pptApp = GetObject(, "PowerPoint.Application")

      Set pptPres = pptApp.Presentations("Presentation1.pptx")

      Set nameRng = Excel.Application.Workbooks("MortalityGraphs_RATES_forReport_READY.FOR.CHECK.xlsx").Sheets("All Drug Overdose -Age-Adj Rate").Range("$A$2:$F$7")

      For i = 1 To nameRng.Rows.Count

        groupName = nameRng.Cells(i, 1).Value

        Excel.Application.Workbooks("MortalityGraphs_RATES_forReport_READY.FOR.CHECK.xls").Sheets("All Drug Overdose -Age-Adj Rate").Range("A9") = groupName

        pdf_file = ThisWorkbook.Path & "" & groupName & " MacroTest1.pdf"

        pptPres.UpdateLinks

        pptApp.Activate

        '!! Filename not defined !!

        'pptPres.SaveCopyAs Filename:=Filename

        pptPres.SaveAs Filename:=pdf_file, FileFormat:=ppSaveAsPDF

      Next

    End Sub

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2018-07-16T02:06:16+00:00

    I'm still having trouble running the code. It doesn't read the line pptPres.SaveAs pdf_file, ppSaveAsPDF for some reason. Any help would be greatly appreciated!!

    Sub PrintLinkedPowerpoints_FINAL()

      Dim pptApp As Object 'PowerPoint.Application

      Dim pptPres As Object 'PowerPoint.Presentation

      Dim nameRng As Range

      Dim i As Long

      Dim groupName, pdf_file

      Const ppSaveAsPDF = 32

      Set pptApp = GetObject(, "PowerPoint.Application")

      Set pptPres = pptApp.Presentations("Presentation1.pptx")

      Set nameRng = ThisWorkbook.Sheets("All Drug Overdose-Age-Adj Rate").Range("B3:F7")

      For i = 1 To nameRng.Rows.Count

        groupName = nameRng.Cells(i, 1).Value

        ThisWorkbook.Sheets("All Drug Overdose-Age-Adj Rate").Range("A2") = groupName

        pdf_file = ThisWorkbook.Path & "" & groupName & " MacroTest1.pdf"

        pptPres.UpdateLinks

        pptApp.Activate

        pptPres.SaveCopyAs NewPpt

        pptPres.SaveAs pdf_file, ppSaveAsPDF

      Next

    End Sub

    Was this answer helpful?

    0 comments