VBA code to convert (or make duplicate of) Excel files in a folder into respective PowerPoint presentations

Question

I am pretty new to Excel, and I am trying to write a macro to convert multiple Excel spreadsheets to multiple PowerPoint slides. So far, I have found a way to make individual slides from individual Excel sheets from a website:

Option Explicit
Sub ExcelRangeToPowerPoint()

Dim rng As Excel.Range
Dim PowerPointApp As PowerPoint.Application
Dim myPresentation As PowerPoint.Presentation
Dim mySlide As PowerPoint.Slide
Dim myShapeRange As PowerPoint.Shape

'Copy Range from Excel
  Set rng = ThisWorkbook.ActiveSheet.Range("A1:G17")

'Create an Instance of PowerPoint
  On Error Resume Next

    'Is PowerPoint already opened?
      Set PowerPointApp = GetObject(class:="PowerPoint.Application")

    'Clear the error between errors
      Err.Clear

    'If PowerPoint is not already open then open PowerPoint
      If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

    'Handle if the PowerPoint Application is not found
      If Err.Number = 429 Then
        MsgBox "PowerPoint could not be found, aborting."
        Exit Sub
      End If

  On Error GoTo 0

'Make PowerPoint Visible and Active
  PowerPointApp.Visible = True
  PowerPointApp.Activate

'Create a New Presentation
  Set myPresentation = PowerPointApp.Presentations.Add

'Add a slide to the Presentation
  Set mySlide = myPresentation.Slides.Add(1, ppLayoutTitleOnly)

'Copy Excel Range
  rng.Copy

'Paste to PowerPoint and position
  mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
  Set myShapeRange = mySlide.Shapes(mySlide.Shapes.Count)

    'Set position:
      myShapeRange.Left = 150
      myShapeRange.Top = 186

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

I am just trying to figure out, is there a macro that can loop through a given folder/directory and convert all the Excel files to PowerPoint presentations?

Answer 1

DO you understand the code? It will create a single powerpoint slide from a single range of cells. What if your workbook file has many sheets? What if the ranges are different on every sheet.

You code will need to accomodate this.

So you would write code that

1. Loops through every excel file found in a folder
2. loops through every sheet in file
3. adds a new slide to one powerpoint file for the current sheet
4. finds the range for the current sheet and adds the range to the slide

Are you rally desperate to automate this? If you only need to do this once, you might be best of doing it manually? Depending on the number of files and how often you do it of course.

To answer you question see here which uses dir to find files in a folder.. But there is a lot more code needed to do it!

There may be a tool available to do this on the web...eg here I've not sued it but it's a common need.