lilaray7581

Upgraded Excel - now none of my scripts work

11 posts in this topic

I have upgraded my MS Office 2010 to MS Office 2016 and now none of my scripts work as they should. The first process is to open the Excel sheet and I can see that it opens it but it appears to be hidden. This never happened in the previous version of Office. I had changed it from the worksheet being on the network to locally as I thought that perhaps it was a network issue but it does the same thing when the file is located on the desktop or on the network. 

Local $oExcel = _Excel_Open()
            If @error Then Exit _Metro_MsgBox(64, "File Label", "Error creating the Excel application object." _
                     & @CRLF & "Need Excel on this computer. " _
                     & @CRLF & "Please contact Support for assistance. ", 400, 11, $Form1)
            _GUIDisable($Form1, 0, 30)
            Local $sWorkbook = "C:\Users\asmith\Desktop\DataSheet.xlsx"
            Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
            $oWorkbook.sheets("File Label_Network Data").Select
            If @error Then Exit _Metro_MsgBox($MB_SYSTEMMODAL, "File Label_Network", "Error opening '" & $sWorkbook & "'." _
                     & @CRLF & "Missing the necessary documents to run this application. " _
                     & @CRLF & "Please contactfor assistance. ", 400, 11, $Form1)

 

Share this post


Link to post
Share on other sites



Did you upgrade AutoIt 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

I did also upgrade to v3.3.14.2 but it still does not work well with Excel 2016 but Excel 2010 it is fine. 

Share this post


Link to post
Share on other sites

If you upgraded from a version < 3.3.12.0 then the Excel UDF has been rewritten.
Here you see what has changed: https://www.autoitscript.com/autoit3/docs/script_breaking_changes_excel_udf.htm


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

#5 ·  Posted (edited)

I was using 3.3.12.0 previously. I double checked and it seems to be correct but not working with Office 2016. 

Edited by lilaray7581

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Does the workbook get displayed when you open it by hand?

Edited by water

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

Yes, it opens manually. I also removed the snippet where I direct it to open a certain sheet and it opened as well. It seems to not like that part because now it opens and I can manually select the sheet that I want to work on. 

Share this post


Link to post
Share on other sites

I think you should replace Select with Activate:

Local $oExcel = _Excel_Open()
If @error Then Exit _Metro_MsgBox(64, "File Label", "Error creating the Excel application object." _
    & @CRLF & "Need Excel on this computer. " _
    & @CRLF & "Please contact Support for assistance. ", 400, 11, $Form1)
_GUIDisable($Form1, 0, 30)
Local $sWorkbook = "C:\Users\asmith\Desktop\DataSheet.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Metro_MsgBox($MB_SYSTEMMODAL, "File Label_Network", "Error opening '" & $sWorkbook & "'." _
    & @CRLF & "Missing the necessary documents to run this application. " _
    & @CRLF & "Please contactfor assistance. ", 400, 11, $Form1)
$oWorkbook.sheets("File Label_Network Data").Activate

 

1 person likes this

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

I don't know why - or even how - but I went back and wrote one line off and compiled. It errored out and placed the line back and compiled. And it is now working. Perhaps it just wanted a jumpstart. Thanks for your assistance. What would activate do differently than select, if you don't mind me asking?

 

Share this post


Link to post
Share on other sites

When you have used activate this sheet becomes the active sheet and can be accessed by property ActiveSheet.
Select just selects the sheet but does not change the active sheet.
That's how I understand it.
Details can be found here: https://stackoverflow.com/questions/15919455/what-is-the-difference-between-sheets-select-and-sheets-activate


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

Thank you! 

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