-1

I have an web automation framework in UFT where I've to update the customized excel report. During the report editing part of code, I am facing a strange issue related to excel process/vba object. I have this code stored in function library (.qfl) inside a function and it is called numerous times. The problem is, sometimes the UFT execution freezes and I see a running process of EXCEL.exe. Nothing happens on the screen for hours until I manually kill the process. 

On error resume next
Dim objExcel, objWorkbook, objWorkSheet
Set objExcel = createobject("excel.application") 
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open("SheetLocation")
Set objWorkSheet = objWorkbook.WorkSheets("SheetName")
intStepRow = objWorkSheet.Cells.Find("WC_01").Row
objWorkSheet.Cells(intStepRow, 7).Value = "SomeValue"
objWorkSheet.Cells(intStepRow, 8).Value = "SomeValue"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close True
objExcel.Quit()      
Set objWorkSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing    
On error goto 0

I tried putting a code at the end of function to kill the process but No luck. I also tried setting excel.Visible and .Display to true to see where exactly things go wrong but I don't see anything. I also tried removing the error handling but same, no luck.

Edit:

After enabling logging for each line, I found that below line is freezing the execution

Set objWorkbook = objExcel.Workbooks.Open("SheetLocation")

Thanks in advance for your time and suggestions :)

yudi2312
  • 323
  • 1
  • 4
  • 21
  • You potentially have an error situation occurring in the `Find` step. If the value you are searching for doesn't exist, your next two steps will halt the code. Have you tried stepping through the code? And definitely remove the `On Error Resume Next` as it's just going to hide any error you run into. – Dave Apr 15 '17 at 21:02
  • Yes i tried removing on error resume next, but no luck. `Find` method will always get a value that exists in the sheet. The halt of code is on random basis. It can occur for any of the value. – yudi2312 Apr 16 '17 at 05:36
  • 1
    Insert logging steps, track each line of code and log if it's successful. That way, when it next stops, you'll have details of exactly where it got to. That will tell you what step went wrong and you will be able to focus on the actual problem. Currently you don't know what the problem is that you need to solve. – Dave Apr 16 '17 at 09:52
  • I inserted logging &remove `On error resume next` and the problem is where the code tried to open the sheet i.e. `Set objWorkbook = objExcel.Workbooks.Open("SheetLocation")` – yudi2312 Apr 16 '17 at 11:50
  • Sounds like the workbook either doesn't exist or perhaps can't be opened (maybe another user already has it open for example). At least now you have an idea of where the problem lies. – Dave Apr 16 '17 at 15:53
  • yea I know where the problem is, but no clue about the solution. Workbook can only be accessed by the system user. So the only reason could be the problem with previous session. – yudi2312 Apr 16 '17 at 18:33
  • Try explicitly killing the process from the task manager after you run the `objExcel.Quit` statement to be sure that the `excel.exe` isn't hanging around causing a problem, maybe? Unless you have an error code or some message that gives more detail on the problem...? – Dave Apr 16 '17 at 21:40
  • I tried killing the process but no luck. And sorry there is no error message. – yudi2312 Apr 17 '17 at 03:57
  • I couldn't get into the depth of your problem but trying to help on the lines of your comment "Nothing happens on the screen for hours until I manually kill the process." as you mention that this function is called multiple times so you are creating multiple instances of excel. in my experience i have always used to system utilities to close the process try using 'SystemUtil.CloseProcessByHwnd(hwnd_Excel)' – Pranav Apr 17 '17 at 07:32
  • Yes I do have multiple instances of Excel but there will be only single instance running at a time. I've also tried killing all the excel processes after `Excel.Quit()` but it didn't work. – yudi2312 Apr 17 '17 at 09:00
  • No suggestions? – yudi2312 Apr 18 '17 at 10:24
  • Your code works perfectly fine. Just close all the Excel instances manually before you run your code or add [**code**](http://stackoverflow.com/a/30974813/1652222) to do it automatically for you. – ManishChristian Apr 18 '17 at 14:46
  • @ManishChristian -> I tried closing excel instance using WMI but no luck. – yudi2312 Apr 19 '17 at 06:00

2 Answers2

0

I faced the same issue, at times UFT stops responding even before the portion of code related to the excel is executed.

I had to save the code in a .vbs file and call it using:

SystemUtil.Run fileName.vbs
Andreas Violaris
  • 2,465
  • 5
  • 13
  • 26
Rawand
  • 1
-1

alternatively i find it interesting, could you try using the objExcel.quit() after you realese the object i.e. after you set wroksheet,workbook, excel = nothing. e.g.

Set objWorkSheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing objExcel.Quit()

Pranav
  • 437
  • 3
  • 19
  • as per my observation, `objExcel.Quit()` is not causing any problem. There is something wrong with objExcel.Workbooks.Open. But I'll give a try to your approach as well. – yudi2312 Apr 17 '17 at 09:01
  • yes. Workbook.Open will create the issue, because you may create multiple instances of excel but cannot access same workbook. so if your earlier process with this workbook was not terminated then it will definitely create an issue when you are access it again. Thats the reason i think if you handle the termination of excel it may solve the issue. – Pranav Apr 17 '17 at 09:26
  • I think Killing the excel process after `objExcel.Quit()` would have helped then. But okay, I'll try this way. – yudi2312 Apr 17 '17 at 09:43
  • 1
    This won't work. If you release the excel object, you can't then call its Quit method... – Dave Apr 17 '17 at 11:00
  • I agree with Dave – yudi2312 Apr 17 '17 at 11:56