MATLAB: Is it possible to retrieve the names of open Microsoft Excel workbooks in MATLAB 7.6 (R2008a)

MATLAB

I often write GUIs/functions for programs that read and write to Excel for various users. It is a requirement that when using XLSWRITE function, the target Excel workbook must be closed. Hence, I would like to retrieve the names of open workbooks so that the my application users can be reminded to close them before proceeding. Is there a MATLAB function that provides this functionality?

Best Answer

A function to check retrieve the names of open Microsoft Excel workbooks is not available in MATLAB.
To work around this issue, you can setup a VBA macro in an Excel workbook to detect all open workbooks. You can execute this VBA macro from MATLAB by:
1. Instantiating Microsoft Excel as a COM server.
2. Calling Excel's EXECUTEEXCEL4MACRO COM API on this COM server instance, as shown below:
h = actxserver('Excel.Application');
h.ExecuteExcel4Macro('!f_Is_WkBk_Open()');
The VBA code to identify whether a workbook is open is shown below:
Public Function f_Is_WkBk_Open(ByVal f_sWkBk As String) As Boolean
Dim oWkBk As Workbook
Dim bIsOpen As Boolean
bIsOpen = False
For Each oWkBk In Application.Workbooks
If InStr(f_sWkBk, oWkBk.Name) > 0 Then
bIsOpen = True
Exit For
End If
Next oWkBk
f_Is_WkBk_Open = bIsOpen
Set oWkBk = Nothing
End Function
Sub test()
Dim btest As Boolean
btest = f_Is_WkBk_Open("H:\Documents\MATLAB\dta\NewFolder13\Book1.xls")
If btest Then
MsgBox "found"
Else
MsgBox "not found"
End If
End Sub
Related Question