使用VBA从Excel文件中的powerpoint打印内容
问题描述:
我正在寻找一种方法将PowerPoint幻灯片的第一个元素的文本复制到Excel文件中。我得到了下面的代码打印出第一个框的文本:使用VBA从Excel文件中的powerpoint打印内容
Sub getText
Dim sld As Slide
Set sld = Application.ActiveWindow.View.Slide
For Each sld In ActivePresentation.Slides
With sld.Shapes(1)
myInput = .TextFrame.TextRange.Text
MsgBox (myInput)
End With
Next
End sub
现在,下一步我要带是将数据添加到Excel文件。因此,我尽量做到以下几点:
Sub getText()
Dim xlApp As Object
Dim xlWorkBook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("~\PROJECTEN\Lopend\office_VA\macroStore.xlsx", True, False)
xlWorkBook.sheets(1).Range("A2").Select
Dim sld As Slide
Set sld = Application.ActiveWindow.View.Slide
For Each sld In ActivePresentation.Slides
With sld.Shapes(1)
myInput = .TextFrame.TextRange.Text
ActiveCell.Text = myInput
End With
Next
End Sub
然而,当我现在尝试它,它得到的错误:“所需的对象”。有关如何更改我的代码的任何想法?
答
您的问题是,你引用ActiveCell.Text但VBA没有线索那是什么。此外,您还没有声明您的myInput变量。
试试这个宏,而不是选择单元格,我只是给它分配文本值。另外,如果您要编写多个值,您的代码将继续写入同一个单元格。在下面的代码我已经添加了一对夫妇会写你的文字落笔列A.
Sub getText()
Dim xlApp As Object
Dim xlWorkBook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("~\PROJECTEN\Lopend\office_VA\macroStore.xlsx", True, False)
Dim xlWorkSheet As Object ' Create a worksheet object
xlWorkSheet = xlWorkBook.sheets(1) ' Set the sheet you activate to that object
Dim iRow As Long ' Create a variable to store row number
iRow = 1 'Set the first row that you want to start writing data on
Dim sld As Slide
Dim myInput As String
Set sld = Application.ActiveWindow.View.Slide
For Each sld In ActivePresentation.Slides
With sld.Shapes(1)
myInput = .TextFrame.TextRange.Text
xlWorkSheet.Cells(iRow, "A") = myInput 'Using .Cells() you can specify the (row, column) location
iRow = iRow + 1 'increment by one for next line of text
End With
Next
End Sub
@Philip,非常感谢您的回复彻底线。但是,当我尝试它:我得到故障91错误这一行:xlWorkSheet = xlWorkBook.sheets(1)。它说,对象变量或变量与配置不好....任何thougts? –
而不是使用xlWorkBook.sheets(1)尝试xlWorkBook.sheets(“Sheet1”)或任何工作表名称在工作簿中。 – pheeper
尝试了它,现在我得到一个438错误,指出此对象不支持此方法/配置 –