excel - 显示需要的对象和未定义的子和功能 Powerpoint VBA

Question

您好我正在尝试将 excel 文件读入 Powerpoint VBA。但我得到错误对象需要并且子和函数未定义

一行正在工作，并且在执行组代码时显示错误

Public pName(), pMat(), pGrade(), pHDT(), pTemp(), planeName(), ledName() As Variant
Public pCount, planeCount, ledCount As Long


Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)
xlWorkBook.sheets(1).Range("A8").Value = "Hello" ' **This Line working**


'------------------------------------------ **this section not working**
pCount = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column
ReDim pName(pCount - 1), pMat(pCount - 1), pGrade(pCount - 1), pHDT(pCount - 1), pTemp(pCount - 1)
For i = 1 To pCount
    pName(i - 1) = Cells(5, i).Value
    pMat(i - 1) = Cells(6, i).Value
    pGrade(i - 1) = Cells(7, i).Value
    pHDT(i - 1) = Cells(8, i).Value
    pTemp(i - 1) = Cells(9, i).Value
Next
'------------------------------------------------


xlWorkBook.Close savechanges:=False
xlApp.Quit
Set xlApp = Nothing
Set xlWorkBook = Nothing
End Sub

标签： excelvbapowerpoint

Answer 1

ActiveSheet并且Cells是特定于 Excel 的。当您在 Excel 中运行 VBA 代码并编写ActiveSheet时，它将被隐式转换为Application.ActiveSheet. 类似地，Cells在不指定任何工作表的情况下使用，它将被隐式翻译为Application.ActiveSheet.Cells. 应用程序对象的类型Excel.Application

在 Powerpoint 中，这是行不通的。Powerpoint-VBA 中有一个Application-object，但它包含 PowerPoint 特定的属性和方法（类型为Powerpoint.Application）。但是，您已经在使用Excel.Application-object，您将它分配给变量xlApp。

我建议将您要使用的工作表分配给一个Excel.Worksheet变量（您已经声明了一个但您没有使用它）。我还建议您使用Early Binding，只需添加对Microsoft Excel Library. 它可以帮助您编写代码，因为 Intellisence 可以向您显示有效的方法和属性——如果您使用Object它，它无法帮助您。

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Dim xlWorkBook As Excel.Workbook   
Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)

Dim xlSheet As Excel.Worksheet
Set xlSheet = xlWorkBook.sheets(1)
' Or, if you insist on ActiveSheet 
' (but honestly, you shouldn't, always specify a sheet explicitly in Excel)
' Set xlSheet = xlApp.ActiveSheet

' Now, work with the variable. Note the `.` in front of `.Range` and `.Cells`
With xlSheet  
    .Range("A8").Value = "Hello"
    pName(i - 1) = .Cells(5, i).Value
    (...)
End With

一个额外的说明：如果你写Public pCount, planeCount, ledCount As Long，只有ledCount定义为Long，所有其他变量都定义为Variant。在 VBA 中，您需要明确指定每个变量的类型：

Public pCount As Long, planeCount As Long, ledCount As Long

更新使用后期绑定，您只需更改变量声明。优点：您不需要参考。缺点：没有智能，编译只能在运行时捕获错误，例如错误输入方法或属性，而不是在编译时

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Dim xlWorkBook As Object
Set xlWorkBook = xlApp.Workbooks.Open(ActivePresentation.Path & "\PPT_INPUT.xlsm", True, False)

Dim xlSheet As Object
Set xlSheet = xlWorkBook.sheets(1)

With xlSheet  
    .Range("A8").Value = "Hello"
    pName(i - 1) = .Cells(5, i).Value
    (...)
End With