how to check if file is opened in excel using OLE

问题:

how may I check if a file is already opened in some Excel instance?

I use DXL (DOORS) language but it should be independent from this language.

Is there any OLE method that I can call to check which file is opened and compare that with the path/filename?

and if that is possible, can I close only that worksheet/file in that Excel application?

edit: this is what I got till now, this works but only once. DXL leaves an Excel.exe process open and in next checks that instance is used which has no open workbooks or even no window at all.

        if (confirm "File \"" fPath "\" already exists. Do you want to overwrite it?") {

        // check if file is opened in any Excel instance
        OleAutoObj oleWorkbooks     = null;
        OleAutoObj oleExcel         = null;
        OleAutoObj oleWorkbook      = null;
        OleAutoArgs autoArgs = create;

        oleExcel = oleGetAutoObject("Excel.Application");
        bool opened = false;
        // if excel is opened
        if(oleExcel != null){
            d("Excel is opened");
            // Get workbooks and open file
            oleGet(oleExcel,"Workbooks", oleWorkbooks);

            // compare each open workbook
            int count = 0;
            oleGet(oleWorkbooks,"Count", count);
            string workbookname = "";
            string sPath = replace(fPath, "\\", "/");
            sPath = stripPath(sPath, true);

            while (count > 0) {
                d("checking opened document");
                clear(autoArgs);
                put(autoArgs, count);
                oleGet(oleWorkbooks,"Item", autoArgs, oleWorkbook);
                oleGet(oleWorkbook, "Name", workbookname);
                opened = sPath == workbookname;
                if(opened) {
                    if(confirm "The file is currently opened in Excel. It must be closed. Do you want to close the document?") {
                        clear(autoArgs);
                        oleMethod(oleWorkbook,"Close",autoArgs);
                    }
                    break;  
                }
                count--;
            }
        }
        oleCloseAutoObject(oleExcel);
        oleCloseAutoObject(oleWorkbooks);
        oleCloseAutoObject(oleWorkbook);
        // todo leaves excel process open

        if(!opened) {
            streamOutputData = write fPath;
            streamOutputData << sOutput;
            close streamOutputData;
            return true;    
        }
    }

回答1:

solved it using existing DXL method canOpenFile(string path, bool write):

if (confirm "File \"" fPath "\" already exists. Do you want to overwrite it?") {
        if(canOpenFile(fPath, true)) {
            streamOutputData = write fPath;
            streamOutputData << sOutput;
            close streamOutputData;
            return true;    
        }
        else {
            e("File \"" fPath "\" is opened in another program. Close it! (It's probably Excel ;) )");
        }
    }

回答2:

I don't know DXL but this is what you could do in VBA, I let you adapt it to DXL:

Sub IsXLBookOpen(strName As String) 

     'Procedure designed to test if a specific Excel
     'workbook is open or not.

    Dim i As Long, XLAppFx As Excel.Application 

     'Find/create an Excel instance
    On Error Resume Next 
    Set XLAppFx = GetObject(, "Excel.Application") 
    If Err.Number = 429 Then 
        Set XLAppFx = CreateObject("Excel.Application") 
        Err.Clear 
    End If 
    On Error GoTo 0

     'Loop through all open workbooks in such instance
    For i = 1 To XLAppFx.Workbooks.Count
        If XLAppFx.Workbooks(i).Name = strName Then
           MsgBox("The workbook is open")
           'Close the workbook and ask if user wants to save
           XLAppFx.Workbooks(i).Close
           'Force close and save changes
           XLAppFx.Workbooks(i).Close True
    Next i 
End Sub

Adapted from here