Jump to content

Recommended Posts

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.

Link to comment
Share on other sites

  • Moderators

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Did you just upgrade the OS or Office as well?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

_Excel_Open, _Excel_BookOpen and _Excel_RangeRead should do what you need.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by jaja714
add @extended
Link to comment
Share on other sites

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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...