Jump to content

Recommended Posts

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.

Share this post


Link to post
Share on other sites
JLogan3o13

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
water

Did you just upgrade the OS or Office as well?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
jaja714

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

 

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
jaja714

.

Edited by jaja714

Share this post


Link to post
Share on other sites
water

_Excel_Open, _Excel_BookOpen and _Excel_RangeRead should do what you need.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
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

  • Similar Content

    • AnonymousX
      By AnonymousX
      Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
      I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
      I saw this code:
      With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
      Is there another include file I need?
      I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
      $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
    • YouriKamperman
      By YouriKamperman
      I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
      This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
      How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
      This is what my script looks like:
      Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
      Local $cDate = StringReplace($Yesterday, "/", "-")
      Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
      Local $oExcel = _Excel_Open()
      Local $oWorkbook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
    • Eminence
      By Eminence
      Hello,
      Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
      Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 
       
      Any help is appreciated. Thanks in advance.
    • antonioj84
      By antonioj84
      Hi all to the forum guru and expert I am trying to  automate this. in the registry  I have the network profile name network 2 and network   I want to  change their  CATEGORY  to Private .  Can someone lead me in the right direction.
      Private is 1 and Public is 0
      #RequireAdmin Global $sHKLMRoot = @OSArch = "x64" ? "HKLM64" : "HKLM" RegWrite($sHKLMRoot &"\SOFTWARE\Microsoft\Windows NT\CurrentVersion\NetworkList\NewNetworks" ,'/v NetworkList /t REG_MULTI_SZ /d 00000000 /f') see  attached picture below
      Much appreaciate
       

    • JNutt
      By JNutt
      I am trying to close an excel file that was not opened with _Excel_Open.  How do I found the excel application object?  I'm new and I am used to files and folders names, so an 'object' is new to me.  I have the info too and simply spy, but I don't know which info is the object name/string.  In the example from help doc's I see the code below and I tried justin pasting it into Scite.
      <
      Local $oExcel1 = ObjCreate("Excel.Application")
      ; Close the Excel instance which was not opened by _Excel_Open
      ; (will still be running because it was not opened by _Excel_Open)
      _Excel_Close($oExcel1)
      If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      Sleep(2000)
      Local $aProcesses = ProcessList("Excel.exe")
      MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 1", "Function ended successfully." & @CRLF & @CRLF & $aProcesses[0][0] & " Excel instance(s) still running.")
      >
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.