Micro Focus QTP (UFT) Forums
Check an Excel workbook availability - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Check an Excel workbook availability (/Thread-Check-an-Excel-workbook-availability)

Check an Excel workbook availability - utestqa - 05-28-2013

Hi all,

This is my first post. Please let me know if I'm doing things the wrong way or if you need any other informations.

I am using QTP 11 and excel 2007.

How is it possible to indentify if an excel workbook is already open elsewhere? I have some tests that use the same workbook and they are running in parallel. If a test open a workbook, I need the other one to wait for it to be available before using it.



Any help or solution insight would be greatly appreciated.

Regards,
François

This is what I have right now....

Code:
public Sub verification_ouverture_DT(Workbook_path,  Workbook,  DT_sheet)
dim  iteration,
iteration = 0
       Do
        On Error Resume Next
        Datatable. ???workbookavailability????  Workbook_path, Work
book, DT_sheet        
        If  err.number = 0 Then
          Exit Do
        end if
        wait(10)
        iteration = iteration + 1

        If iteration = 30 Then
            Reporter.ReportEvent micFail, "Workbook not available", "Workbook not available"  & err.description
            exittest ()
        End If
  loop until iteration = 30        
End Sub

verification_ouverture_DT "C:\fichier.xls", 1 , 1


RE: Check an Excel workbook availability - utestqa - 05-29-2013

Found a partial answer to my quesiton, I can search for the ~$ file which is created in the same folder by Windows when it is open by an other user
http://stackoverflow.com/questions/15555522/how-to-tell-if-an-excel-2007-spreadsheet-is-open-and-who-has-it-open-using-vbscr/16820357#16820357

Code:
testWorkbookLockFile = "path\~$file.xlsx"
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(testWorkbookLockFile) Then
(testWorkbookLockFile)
    WshShell.Echo "The file is locked by " & GetFileOwner(testWorkbookLockFile)
Else
    WScript.Echo "The file is available"
End If

Function GetFileOwner(strFileName)
'    http://www.vbsedit.com/scripts/security/ownership/scr_1386.asp
    Set objWMIService = GetObject("winmgmts:")
    Set objFileSecuritySettings = _
    objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")
    intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)

    If intRetVal = 0 Then
       GetFileOwner = objSD.Owner.Name
    Else
       GetFileOwner = "Unknown"
    End If
End Function

Unfortunatly the
Code:
WshShell.Echo "The file is locked by " & GetFileOwner(testWorkbookLockFile)
part won't work since QTP can't execute any WScript.

Would anyone have an alternative for WshShell.Echo in order to notify the user using the file ?

Thanks a lot

RE: Check an Excel workbook availability - supputuri - 05-30-2013

if you are using qtp then you can use "MSGBOX", else you can create a wshell object and use Popup method.

RE: Check an Excel workbook availability - utestqa - 05-30-2013

Thanks,

I am using qtp.

I'd like my script to notify a user to close the excel file.

Is there any way to pop a message on a remote computer in the same network with my script?

Regards,
François

RE: Check an Excel workbook availability - supputuri - 05-30-2013

Yes Francois it is possible. There are 2 ways

1) Using WMI and send message
2) Create Wshell object pointing to the remote box where the file is opened and then use popup method.

Though I never used these (I'm not had this kind of situation) I am sure this will work .... let me know if you need any further info

RE: Check an Excel workbook availability - utestqa - 05-30-2013

Hi!

Thank you supputuri,

Your help is greatly appreciated.

After navigating in forums for a day, it seems that wscript and cscript are unavailable in QTP because they are "Host specific object ". I guess this would discard the Wshell solution?
Quote:2) Create Wshell object pointing to the remote box where the file is opened and then use popup method.


For your first option, the WMI, it seems the right way to go.
Quote:1) Using WMI and send message

I already have some wmi in my script

Code:
Function GetFileOwner(strFileName)
'    http://www.vbsedit.com/scripts/security/ownership/scr_1386.asp
     Set objWMIService = GetObject("winmgmts:")
     Set objFileSecuritySettings = _
     objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")
     intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)

The thing is, I really have no clue how to code this. Sad

May I ask for your help? Is there something I could work out of the above code to send a message?

Also, in case someone would be interested, forgot to mention I took my code here http://stackoverflow.com/questions/15555522/how-to-tell-if-an-excel-2007-spreadsheet-is-open-and-who-has-it-open-using-vbscr/16820357#16820357


Thanks,
François

RE: Check an Excel workbook availability - utestqa - 05-30-2013

Any idea?