用于将文件夹中的Excel文件转换（或复制）到各自的PowerPoint演示文稿中的VBA代码

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:

我是Excel的新手,我正在尝试编写一个宏来将多个Excel电子表格转换为多个PowerPoint幻灯片。到目前为止,我已经找到了一种从网站上的单个Excel工作表制作单个幻灯片的方法:

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?

我只想弄清楚,是否有一个宏可以循环通过给定的文件夹/目录并将所有Excel文件转换为PowerPoint演示文稿?

1 个解决方案

#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.

你明白这个代码吗?它将从单个单元格范围创建单个powerpoint幻灯片。如果您的工作簿文件有多张表怎么办?如果每张纸上的范围不同怎么办?

You code will need to accomodate this.

你的代码需要适应这个。

So you would write code that

所以你会编写代码

Loops through every excel file found in a folder

循环遍历文件夹中的每个excel文件

loops through every sheet in file

循环遍历文件中的每个工作表

adds a new slide to one powerpoint file for the current sheet

将新幻灯片添加到当前工作表的一个powerpoint文件中

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!

要回答你的问题,请参阅这里使用dir查找文件夹中的文件..但是还需要更多的代码才能完成!

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.

可能有一个工具可以在网上做到这一点......例如,我在这里没有提起诉讼,但这是一个普遍需要。

#1