excel - 如何在vba中使用excel对象到另一个程序

Question

我需要使用一个excel对象(在一个过程中设置)到同一模块中的其他过程。但做不到。请帮助我做同样的事情。下面是我的代码。

Public Sub FirstProc() 'Here the excel object is defined

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(ReportTemplateDirectory\Test.xlsx")
xlApp.Visible = True
...
... 'all work goes here FINE
...

SecondProc  'calling second proc
End Sub

下面是我的第二个程序，

Public Sub SecondProc()

' In this procedure i need to delete a sheet of the same excel file which is generated in above procedure.

xlWorkbook.Sheets(4).Delete

End Sub

但我收到运行时错误 424 需要对象。任何帮助将不胜感激。

Answer 1

虽然我只是将该行添加到第一个模块，但您可以尝试类似的操作(在 Outlook 中测试)。

DisplayAlerts已关闭，以避免删除工作表时出现检查消息。

Public Sub FirstProc() 'Here the excel object is defined

Dim xlApp As Object
Dim xlWorkbook As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.displayalerts = False
Set xlWorkbook = xlApp.Workbooks.Add
xlApp.Visible = True

Call SecondProc(xlWorkbook)    'calling second proc
xlApp.displayalerts = True
End Sub


Public Sub SecondProc(xlWorkbook As Object)
' In this procedure i need to delete a sheet of the same excel file which is generated in above procedure.
xlWorkbook.Sheets(2).Delete
End Sub