Test if an Excel file is open from Cad

[KaiBo]

I am trying to write a function that checks if an Excel workbook is open (read only or not), and if it’s open assign a variable to it.

I have found many examples of how to test if an Excel workbook is open from excel VBA, but can’t get it to work in AutoCad VBA. Any help in getting this code to work in Autocad would be appreciated.

 

This is the Testing function I have so far:

 

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False

Dim Wbk As Workbook

'On Error GoTo WorkBookNotOpen

If Len(Excel.Application.Workbooks(WorkBookName).Name) > 0 Then
MsgBox "WorkbookOpen = True", , "Testing MsgBox"

WorkbookOpen = True

Exit Function
End If

MsgBox "WorkbookOpen = False", , "Testing MsgBox"
WorkBookNotOpen:
End Function

 

Thanks,

Kai

[borgunit]

This returns an error which you can capture...

 

 

Public Function GetOpenExcelWorksheet() As Excel.Worksheet
'------------------------------------------------------------------------------
'
'Arguments: None
'Returns:   Excel worksheet
'
'Example:
'Dim xlObj As Excel.Application
''''''''''''''''''''''''''''''''''''''''
'Set xlObj = GetOpenExcelWorksheet
'------------------------------------------------------------------------------
On Error GoTo ErrHandler
Dim sPath As String
Dim xlApp As Excel.Application
'''''''''''''''''''''''''''''''''''''''

Set xlApp = GetObject(, "Excel.Application")
'Set gWorkbook = gExcelApp.Workbooks.Open(sPath, , True)
Set GetOpenExcelWorksheet = xlApp.ActiveSheet
If (GetOpenExcelWorksheet Is Nothing) Then Err.Raise vbObjectError + 513
ErrHandler:

Select Case Err.Number
Case 429
   MsgBox "Excel Is Not Open"
Case -2147220991
   MsgBox "Excel Has No Active Worksheet"
Case Else
   
End Select
Err.Clear
End Function

[KaiBo]

Thanks Borgunit’s,

Perhaps I was not clear, I am trying to determine if a specific workbook is open. Not whether any workbooks are open. The point of all this is I am trying to avoid opening several versions of the same workbook.

In trying to alter Borgunit’s code to check if a specific workbook is open, I get error 9 as long there is any workbooks open.