2

UFT Version: 12.02(Build: 2374)

OS: Windows 7(64-Bit)

In my application, there is a DataGrid and an "Export to Excel Button". When I click on that Button, an excel file(Data.xlsx) is opened containing all the data present in the Grid. I just need to save this excel File to a location. I am using the 'GetObject' method to get the reference to the opened Excel File as shown in the below code.

Issue: When I run this code in Debug Mode, it works fine, I do not get any error and the excel gets saved at correct location successfully. But when I run the same code(Normal Run-not in Debug), I get the Error Code: 429(ActiveX Component Can't Create Object') as shown below. I am not sure why I am getting this issue in Normal Run.

Code:

Dim l_objExcel, l_objExcelBook
Execute "Set l_objExcel = GetObject(,""Excel.Application"")"    'getting error 429 here in Normal Run

'I could have just used- Set l_objExcel = GetObject(,"Excel.Application") but it doesn't seem to work. It throws the syntax error(Expected Expression) at this line

Set l_objExcelBook = l_objExcel.ActiveWorkBook
l_objExcelBook.SaveAs strSavePath              'strSavePath contains the File save location
l_objExcelBook.Close
l_ObjExcel.Quit
Set l_objExcelBook = Nothing
Set l_objExcel = Nothing

EDIT1: If I use the following line in the code, I get a syntax error shown in attached image.

Set l_objExcel = GetObject("",""Excel.Application"")

enter image description here

EDIT 2: Just a summary,

  1. If I use the below line, Ii don't get any error during debug but get the error 429 during the normal run.

    Execute "Set l_objExcel = GetObject(,""Excel.Application"")"

  2. If I use the below line, I get the syntax error "Expected Expression"

    Set l_objExcel = GetObject(,"Excel.Application")

  3. If I use the below line, I do not get any error at this line, but start getting the error 424(Object required) in the line where I am trying to save the File.

    Set l_objExcel = GetObject("","Excel.Application")
user692942
  • 16,398
  • 7
  • 76
  • 175
Gurmanjot Singh
  • 10,224
  • 2
  • 19
  • 43
  • Does the debug process and the main process run under different architectures, 32 bit v 64 bit? – user692942 May 10 '17 at 09:20
  • 1
    `UFT` is funny about its `GetObject` call for Excel, try it like this: `Set l_objExcel = GetObject("",""Excel.Application"")` – Dave May 10 '17 at 09:22
  • @Lankymart No, both processes run under the same architectures - it's just a foible of the system really :) – Dave May 10 '17 at 09:23
  • @Dave You sure [about that](http://stackoverflow.com/a/40530956/692942)? Only time I ever see this error in VBScript is when it can't find the `ProgId` in the relevant architecture registry in Windows. – user692942 May 10 '17 at 09:24
  • @Lankymart Well, now I'm not... But I got the same error previously when using a `GetObject` for Excel from within UFT and that was how I got past it without any further problems. – Dave May 10 '17 at 09:30
  • 1
    @dave sounds promising then, I've never used UFT just know *usually* what that error means. One example I can think of is if the OP is using Excel 32 Bit but UFT is running the script 64 Bit, it won't find the 64 Bit Excel `ProgId` in the 64 Bit registry because only the 32 Bit version is installed. – user692942 May 10 '17 at 09:33
  • 2
    [This answer](https://stackoverflow.com/a/35985827/692942) deals with COM in a Web Environment but the principles are the same *(just ignore the web specific stuff)*, take a look at the "What If You Don't Know Where the DLL Is Located?" and "How to register COM DLL with Windows" sections for a breakdown of how COM is registered in the Windows Registry. – user692942 May 10 '17 at 09:41
  • @Dave Please see the post update. If I use Double-Quotes twice around Excel.Application, sadly it throws another syntax error[image attached]. Also, If I surround it by double-quotes only once, I do not get any error at that line. But, in that case, I start getting the Error 424(Object Required) at the line where I am trying to save the document. – Gurmanjot Singh May 10 '17 at 09:52
  • 1
    @GMan, I apologise, I copied your `GetObject` call directly from your post - I meant to suggest you use `Set l_objExcel = GetObject("","Excel.Application")` and not leave in the extra set of double quotes (which you only needed because the `Execute` call takes a string parameter) – Dave May 10 '17 at 09:55
  • @Dave Yes. I tried doing that too.In this case, I do not get any error at that line. But I start getting the Error 424(Object Required) at the line where I am trying to save the document – Gurmanjot Singh May 10 '17 at 09:56
  • 1
    @Gman looks to me like your escaping the quotes in the script but also during the debug, the line in the debug should be `Set l_objExcel = GetObject("","Excel.Application")` no need for the escaping `""`. – user692942 May 10 '17 at 10:00
  • @Lankymart Point Noted. – Gurmanjot Singh May 10 '17 at 10:03
  • 1
    @Gman Once you have stepped through the point of setting `l_objExcel` to the Excel application object, check your Local Variables view tab (Under View -> Debug menu options if it's not already visible) and ensure that you can see the variable and that it's set as an object? – Dave May 10 '17 at 10:10
  • @Dave I checked. It is NOT set as object. Its value is Empty. Also, Isobject(l_objExcel) is returning FALSE. – Gurmanjot Singh May 10 '17 at 10:20
  • 1
    `""Excel.Application""` isn't a valid string – SierraOscar May 10 '17 at 10:20
  • @MacroMan Thanks. That was just a mistake. I am not using that anymore. – Gurmanjot Singh May 10 '17 at 10:21
  • 1
    Perhaps then it's time to look more at the comments made by @LankyMart regarding the registration of Excel objects? – Dave May 10 '17 at 10:28
  • @Dave I am looking into them now. – Gurmanjot Singh May 10 '17 at 10:36
  • Shouldn't make a difference, but what if you use `Set l_objExcel = GetObject(vbNullString,"Excel.Application")` There's really no reason why that line shouldn't work unless Excel isn't actually running at the time when that code gets executed – SierraOscar May 10 '17 at 10:37
  • @MacroMan I tried that just now. Again, I got the error mentioned in EDIT2, Point 3. 424: Object required. At the line, wherein the file is being saved. I made sure that Excel is running when that line was executed. – Gurmanjot Singh May 10 '17 at 10:41
  • What are you running the script from? Is it running naturally from WScript? – SierraOscar May 10 '17 at 10:46
  • I can't help [but feel I'm repeating myself](https://stackoverflow.com/questions/43886260/script-working-in-debug-mode-but-not-in-normal-run-error-code-429activex-com#comment74811247_43886260), the likely cause is a mismatch of architecture can you let us know what architecture versions of UFT and Excel you are using? – user692942 May 10 '17 at 10:47
  • @MacroMan I thought we had established it's running inside UFT? – user692942 May 10 '17 at 10:49
  • @MacroMan I am running the script from inside the UFT(by pressing the run Key-F5) – Gurmanjot Singh May 10 '17 at 10:51
  • @Lankymart I don't know UFT, so don't know if it's still using WScript at some level - sounds like that's what you're already getting at though - I was wondering about COM visibility etc. – SierraOscar May 10 '17 at 10:51
  • 1
    @MacroMan I don't either, but from what I've seen of these types of questions in the past UFT is a desktop program that acts like the WSH for VBScript but with some bells and whistles. Which is why it is likely an architecture mismatch between the context of the script execution and Excel. If they have 32 Bit Excel installed no amount of calling `GetObject()` from a 64 bit environment is going to find the 32 bit COM instance of Excel. – user692942 May 10 '17 at 10:54
  • @Lankymart Yup sounds like it's something along those lines. I'll butt out lol you've already got that covered. – SierraOscar May 10 '17 at 10:58
  • @Gman do you have a 32 bit version of UFT you can run i.e `C:\Program Files (x86)\...` *(32 bit programs are usually stored here in 64 bit versions of Windows)*, try running the script from there if you have it. – user692942 May 10 '17 at 11:01
  • @LankyMart Windows-64 Bit; UFT: 32 Bit; Excel - 32 Bit – Gurmanjot Singh May 10 '17 at 11:06
  • 1
    @Gman in which case that isn't the issue. You could have saved me a lot of wasted time if you'd just answered that in the first place. What permissions are you running UFT elevated? – user692942 May 10 '17 at 11:12
  • @Lankymart My Apologies. Even if there was a mismatch in architectures of UFT and Excel, how would you explain the script running fine in Debug Mode then? Shouldn't one expect to get the same issues in Debug mode also? – Gurmanjot Singh May 10 '17 at 13:13
  • 1
    @Gman, your both line of code, with and w/o `Execute...` works perfectly fine on my system. I do have UFT 14, but I don't think it has to do anything with UFT version. I even put your code in vbscript file and ran w/o any issue. Try to run your code on other system to see if its working there. If its working than you might want to compare both system's settings. – ManishChristian May 15 '17 at 18:40
  • @ManishChristian I apologize for the late reply. I have now tried this code on a number of machines. Only in 1 machine the code worked fine(both in normal run and in debug mode). The system on which the code worked fine also has 32-bit UFT and 32-Bit Excel installed as in other systems where the code is failing. Could you please specify which other system settings are you talking about? I need to find the root cause behind this weird behavior. – Gurmanjot Singh May 19 '17 at 19:46
  • 1
    I would suggest to run [**`Utility`**](http://www.ftporasi.com/EyeOnTesting/getData/OrasiSysInfo.zip) from Orasi on both machines where the script is working and not and compare the report. You might see the differences. – ManishChristian May 19 '17 at 20:02
  • Just a general question, have you put sufficient wait before you try to access the file? Try and put a wait of == >Wait(5), Wait(10). – Mithilesh Indurkar Jun 22 '17 at 06:30
  • Yes. I tried that. Did not work. – Gurmanjot Singh Jun 22 '17 at 06:50

1 Answers1

0

Just use 

Set l_objExcel = GetObject( ,"Excel.Application")

it will show syntax error, but ignore it , your script will run fine.

Chintan Vaghasiya
  • 46
  • 3