使用VBA将Excel中的单元格内容导入PowerPoint演示文稿。

huangapple go评论65阅读模式
英文:

Use VBA to direct cell contents from Excel into PowerPoint presentation

问题

You can try the following VBA code to direct the cell contents to the correct placeholder in the PowerPoint template. Replace "Title" and "Description" with the actual names of your placeholders in the PowerPoint template:

Sub LoopRowsSelected()

    Dim DataRange As Range
    Dim DataRow As Range
    Dim AppPPT As Object ' Use Object data type for late binding
    Dim Pres As Object
    Dim Sld As Object
    
    ' Create PowerPoint application
    Set AppPPT = CreateObject("PowerPoint.Application")
    AppPPT.Visible = True
    
    ' Open the PowerPoint template
    Set Pres = AppPPT.Presentations.Open("C:\Test\Sample.potx")
    
    ' Set the data range from Excel
    Set DataRange = Selection
    
    ' Loop through each row in the data range
    For Each DataRow In DataRange.Rows
        ' Add a new slide using the first layout (you can change this as needed)
        Set Sld = Pres.Slides.Add(Pres.Slides.Count + 1, 1) ' 1 represents the layout index
        
        ' Replace "Title" and "Description" with your actual placeholder names
        Sld.Shapes("Title").TextFrame.TextRange.Text = DataRow.Cells(1, 1).Value
        Sld.Shapes("Description").TextFrame.TextRange.Text = DataRow.Cells(1, 2).Value
    Next DataRow
    
    ' Release PowerPoint objects
    Set Sld = Nothing
    Set Pres = Nothing
    Set AppPPT = Nothing

End Sub

This code should add new slides to the PowerPoint presentation and populate the "Title" and "Description" placeholders with the respective cell contents from your Excel spreadsheet. Make sure to replace "Title" and "Description" with the actual names of your placeholders in your PowerPoint template.

英文:

I am using VBA code in Excel to create a slide in PowerPoint for each row of the spreadsheet. The following code works correctly in opening a new presentation and creating a new slide for each row as highlighted in my spreadsheet. It places the 1st cell of the row within the Title placeholder of the new slide:

Sub LoopRowsSelected()

Dim DataRange As Range
Dim DataRow As Range
Dim DataColumn As Range


Dim AppPPT As PowerPoint.Application
Dim Prs As PowerPoint.Presentation
Dim Sld As PowerPoint.Slide

Set AppPPT = New PowerPoint.Application
Set Pres = AppPPT.Presentations.Open("C:\Test\Sample.potx")

AppPPT.Visible = True

Set DataRange = Selection

For Each DataRow In DataRange.Rows
    
    Set Sld = Pres.Slides.AddSlide(Pres.Slides.Count + 1, Pres.SlideMaster.CustomLayouts(1))
 
        Sld.Shapes.Title.TextFrame.TextRange.Text = DataRow.Cells(1, 1)

    Next DataRow

End Sub

I had thought by naming each placeholder within the template differently, and then by copying this line:

Sld.Shapes.Title.TextFrame.TextRange.Text = DataRow.Cells(1, 1)

And adding it to look like this:

Sld.Shapes.Description.TextFrame.TextRange.Text = DataRow.Cells(1, 2)

It would insert the second cell of the row into the placeholder named "Description". It comes back with an error stating "Compile error: Method or data member not found". Obviously, I'm barking up the wrong tree in thinking "Title" is the actual "Title" selection object name in the template.

My question is, what do I need to do differently to direct the cell contents to the correct placeholder in the PPT template? Mind you (as you've already figured out), I'm not a programming, VBA, or Excel expert. I do not have a very good understanding of methods and hierarchy of elements, which I'm sure is causing me issues.

答案1

得分: 0

使用以下代码:

Sld.Shapes("描述").TextFrame.TextRange.Text = DataRow.Cells(1, 2)

当你在母版幻灯片中命名占位符时,这些名称不会自动传递到创建的幻灯片。你必须知道这些占位符的名称,以便在VBA中正确编码。如果是常规文本框,第一个文本框很可能是“TextBox #”,其中“#”符号将是递增的数字,随着更多对象放入母版幻灯片。你可以通过转到功能区并选择 格式 -> 选择窗格,然后在选择窗格中选择要知道名称的对象,以便在其中突出显示它来找出这些名称。

英文:

Use the following:

 Sld.Shapes("Description").TextFrame.TextRange.Text = DataRow.Cells(1, 2)

When you name the placeholders in the Master Slide, the names do not automatically transfer over to a created slide. You'll have to know what those placeholder names are in order to code it correctly in the VBA. If a regular text frame, the first of them will most likely be "TextBox #" where the "#" symbol would be the incremented number as more objects get put into the master slide. You can figure out what the names are by going to the ribbon and selecting Format -> Selection Pane, then selecting the object you want to know the name of in order to highlight it in the selection pane.

huangapple
  • 本文由 发表于 2023年5月22日 23:00:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76307481.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定