英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论