Excel ObjGet Windows 10

[jaja714]

For years, I have been running a script using ObjGet to open an Excel workbook but, after upgrading from Win7 to Win10, ObjGet is now returning with an error.  The only way I can get it to work on Win10 is to actually open the workbook manually myself and then run the script.

[JLogan3o13]

@jaja714 what would you like us to do to assist without seeing the code you're using?

[water]

Why do it "manually"? AutoIt comes with an Excel UDF.

[jaja714]

Ahah, I am always open to a better way.  here is what I have been doing for many years ... until Win10

 

$FileName = "C:\Users\Public\Music\The Beatles\The Beatles.xlsx"
$CellRange="songs"                                      ; Change this to the range of cells you want to modify

if not FileExists($FileName) then                       ; Just a check to be sure..
    Msgbox (0,"loadBeatles","Error: Can't find file " & $FileName)
    SetError(1)
    Return
endif

$oExcelDoc = ObjGet($FileName)                          ; Get an Excel Object from an existing filename
                                                        ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
If (not @error) and IsObj($oExcelDoc) then              ; Check again if everything went well
    $oDocument=$oExcelDoc.Worksheets(1)                 ; We use the 'Default' worksheet
    $aBeatles=$oDocument.range($CellRange).value        ; Retrieve the cell values from given range

    ; The data should now be in the 2-dimensional array: $aBeatles
    If Not IsArray($aBeatles) And Ubound($aBeatles,0) < 5 Then
        Msgbox (0,"loadBeatles","Error: Could not retrieve data from cell range: " & $CellRange)
        SetError(1)
    EndIf

    $oExcelDoc.saved=1              ; Prevent questions from excel to save the file
    $oExcelDoc.close                ; Get rid of Excel.
Else
    Msgbox (0,"loadBeatles","Error: Could not open "& $FileName & " as an Excel Object.")
    SetError(1)
Endif

Return $aBeatles

 

[water]

Did you just upgrade the OS or Office as well?

[jaja714]

Duh ... yes ... Office 2016!  I did em both the same day.

 

[water]

So something has changed when upgrading Office.
Can you elaborate on which error you get? What's the value of @error? Do you get error messages?

[water]

Is this a trial version of Office 2016 or did you upgrade from a trial version?

 

[jaja714]

Is this a trial version of Office 2016 or did you upgrade from a trial version?

 

This is the real version of Office 2016.

I didn't think the value of @error was important as the documentation simply says it will be non-zero when failing.  For what it's worth though, the value of @error is -2147221018.

[water]

When converted to hex -2147221018 is equal to 0x800401E6 and stands for "MK_E_INVALIDEXTENSION Bad extension for file." - Whatever this means.
https://msdn.microsoft.com/en-us/library/cc704587.aspx

[water]

When searching the web for "excel hresult 0x800401E6" it seems that the error tells you that the file you want to access is already opened by another application:
https://stackoverflow.com/questions/24323167/waiting-open-excel-when-program-is-writing-data-to-excel

So I suggest to use the Excel UDF to work with Excel workbooks.

[jaja714]

Well, the file is certainly not open by another applications and further, the extension .xlsx is perfectly fine as well.

On the contrary, I am actually able to get my script to run successfully if I actually do open the file before running the script.  Strange.

Sounds like ObjGet and Office2016 are not playing nice.  Let me look into the whole Excel UFD thing...

[jaja714]

.

Edited November 7, 2015 by jaja714

[water]

_Excel_Open, _Excel_BookOpen and _Excel_RangeRead should do what you need.

[jaja714]

What?  Still getting the same error as before!  The only difference is that it is _Excel_Open returning the error instead of ObjGet.

Also, just like before, _Excel_Open works fine if the workbook in question is already open.

NOTE: @extended = -2147221164

$oExcel = _Excel_Open(False,False,False,False,True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "loadBeatles", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$oWorkbook = _Excel_BookOpen($oExcel, $wbBeatles, Default, Default, True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "loadBeatles", "Error opening workbook '" & $wbBeatles & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

$aBeatles = _Excel_RangeRead($oWorkbook, Default, $cellRange)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "loadBeatles", "Error reading range " & $cellRange & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

Edited November 9, 2015 by jaja714
add @extended

[water]

This error code stands for:
0x80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
Seems that Excel is not installed or not available to the user who runs the script.
Do you run the script from the task scheduler or as user System?

[jaja714]

When I build an EXE and run as admin, I get the same error as above.  Am I the first Autoit programmer to use Excel UDF for Office 2016?

[water]

This is not my area of expertise but I think it is a problem with Excel not being registered for all users.
I'm sure you will find a solution on the web.
Or maybe another user knows how to solve this problem.

[jaja714]

I'm not sure what "registered for all users" means.  I am the only user and, further, Excel 2016 shows me as "signed in" whenever I open it.

What in the world should I search for on the web?

Would it be easier to simply debug the Excel UDF on my machine?

[jaja714]

Ok, so I did some debugging of the existing Excel UDF and ... we are right back where we started ... ObjGet and ObjCreate!!!

ObjGet returns 0 for both @error and @extended while ObjCreate returns @error = -2147221164 and @extended = 0.

 

p.s. how do you look up the values of these error codes?  It would be nice if there were some sort of ShowError and ShowExtended functions!