getObject excel - Not working properly

Hello,

 

I am attempting to create a script in which an excel file is downloaded/opened via the browser. The script completes that part easily, however I then need to grab values from the excel file and this is where I am having a problem. I have tried every variant of CreateObject and GetObject I could think of and have run out of ideas on what I am doing wrong.

 

Code as of now:

 

Set objExcel = getObject("", "Excel.Application")
Set objExcelwb = objExcel.ActiveWorkbook
Set objExcelsh = objExcelwb.ActiveSheet

intexcel = objExcel.Cells( 2, 8 ).Value

 

 

 

I have tried objExcelwb and objExcelsh at different points in time on the .Cells call.

 

I get one of a few errors depending on what I have tried. Either:

 

on the "getobject" line - ActiveX cannot create the object

on the "set objExcelsh" line - Object Required

on the "intexcel =" line - General Runtime Error //or// Object Required

 

I do have a 5 second wait before the GetObject call to make sure the excel file is fully open before hand.

 

Also, the "intexcel" line is part of a Sub that is at the top of the script. GetObject is called in main and the Sub is called after that.

 

Thanks for your time.

Parents
  • If you end up getting an "expected statement" error at design time, wrap your GetObject statement in an Execute:

     

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

     

    In the case of Browser initiated Excel apps, I found that the ROT was not populated until Excel was defocused and then refocused. GetObject is looking for the first Excel entry in the ROT, so you will most likely have to do the same.

     

    If you do not require the workbook, you can go straight to the active sheet or a specific sheet name:

     

    Set objSheet = objExcel.ActiveSheet

    or
    Set objSheet = objExcel.Worksheets(sheetname)

     

    The intexcel assignment statement should refer to the sheet object. IE: objExcelsh instead of objExcel

     

    ..Ray


  • wrote:

     

    In the case of Browser initiated Excel apps, I found that the ROT was not populated until Excel was defocused and then refocused. GetObject is looking for the first Excel entry in the ROT, so you will most likely have to do the same.

     


    Thank you for your response!

     

    The section quoted above is the problem I am running into I believe. Getobject throws an "ActiveX cannot create" error because the Excel was opened after the test run began. I understand the idea behind what you've said above, what I am lacking is knowledge on the solution.

     

    How do I defocus and refocus an object/window that I am unable to "get" in the first place?

Reply

  • wrote:

     

    In the case of Browser initiated Excel apps, I found that the ROT was not populated until Excel was defocused and then refocused. GetObject is looking for the first Excel entry in the ROT, so you will most likely have to do the same.

     


    Thank you for your response!

     

    The section quoted above is the problem I am running into I believe. Getobject throws an "ActiveX cannot create" error because the Excel was opened after the test run began. I understand the idea behind what you've said above, what I am lacking is knowledge on the solution.

     

    How do I defocus and refocus an object/window that I am unable to "get" in the first place?

Children
  • Sorry for the slow response~

     

    You can use WSHShell.AppActivate() to move focus between Windows applications without having the object reference.

     

    For example, using ie, you might do something like this to get the Excel App object:

     

    Function xlobjExcel(ByVal stepnum)
        Dim objExcel, sTime, wshShell
        Set objExcel = Nothing
        Set wshShell = CreateObject("WScript.Shell")
        On Error Resume Next
        wshShell.AppActivate("Internet Explorer") 'de-focus Excel to populate ROT
        Wait 2
        wshShell.AppActivate("Microsoft Excel")
        Execute "Set objExcel = GetObject(, ""Excel.Application"")" 
    
        sTime = Timer
        Do While Err
            Err.Clear
            wshShell.AppActivate("Internet Explorer")
            Wait 2
            wshShell.AppActivate("Microsoft Excel")
            Execute "Set objExcel = GetObject(, ""Excel.Application"")"
    
            If Timer - sTime > 20 Then
                Reporter.ReportEvent micFail, "xlobjExcel Step: " & stepnum, "GetObject fail exceeded 20 seconds."
                Exit Do
            End If
        Loop
        On Error Goto 0
        If Not objExcel is Nothing Then
            Set xlobjExcel = objExcel
        Else
            Reporter.ReportEvent micFail, "Get Excel Application Object. Step: " & stepnum, "Excel application object not found."
        End If
        Set objExcel = Nothing
        Set wshShell = Nothing
    End Function