想在VBA中使用全局EXCEL对象,试写下列代码,myerror处提示错误:“对象不支持的属性或方法”,说明下,我是希望只调用GetXlSheet来返回工作表。
请问怎么做才好呢,VBA中有强制类型转换吗?'-----------excel公共对象------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Public Function GetXlApp() As Excel.Application
'代码不安全吧?用windows(name)判断?
If xlApp Is Nothing Then
'创建Excel.application实例
Set xlApp = CreateObject("excel.application")
End If
Set GetXlApp = xlApp
End Function'打开工作薄文件(*.xls)
Public Function GetXlBook(Optional ByVal AXlsFileName As String = cfg_fname) As Excel.Workbook
If GetXlApp().Workbooks("AXlsFileName") Is Nothing Then
Set xlBook = xlApp.Workbooks.Open(AXlsFileName)
End If
Set GetXlBook = xlApp.Workbooks("AXlsFileName")
End FunctionPublic Function GetXlSheet(ByVal AWorkSheetName As String) As Excel.Worksheet
myerror:If GetXlApp().GetXlBook().Worksheets(AWorkSheetName) Is Nothing Then
Set xlSheet = xlApp.GetXlBook().Worksheets(AWorkSheetName)
End If
Set GetXlSheet = xlSheet
End Function
请问怎么做才好呢,VBA中有强制类型转换吗?'-----------excel公共对象------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Public Function GetXlApp() As Excel.Application
'代码不安全吧?用windows(name)判断?
If xlApp Is Nothing Then
'创建Excel.application实例
Set xlApp = CreateObject("excel.application")
End If
Set GetXlApp = xlApp
End Function'打开工作薄文件(*.xls)
Public Function GetXlBook(Optional ByVal AXlsFileName As String = cfg_fname) As Excel.Workbook
If GetXlApp().Workbooks("AXlsFileName") Is Nothing Then
Set xlBook = xlApp.Workbooks.Open(AXlsFileName)
End If
Set GetXlBook = xlApp.Workbooks("AXlsFileName")
End FunctionPublic Function GetXlSheet(ByVal AWorkSheetName As String) As Excel.Worksheet
myerror:If GetXlApp().GetXlBook().Worksheets(AWorkSheetName) Is Nothing Then
Set xlSheet = xlApp.GetXlBook().Worksheets(AWorkSheetName)
End If
Set GetXlSheet = xlSheet
End Function
另外,你可在立即窗口里把出错那行的对象添加监视,一级级监视下去看具体是哪个属性不支持。然后再上来问。
这么说吧,因为getxlapp虽然返回excel.application对象,但是它没有getxlbook方法,所以报错了,我只是想不调用前两个函数,直接使用getxlsheet函数来返回对象。
'XlApp并没有GetXlBook方法