Sign in to follow this  
Followers 0
dustinp4340

Having an issue with the Excel Run() command -- returning weird error

13 posts in this topic

Hi all-

I am using AutoIT 3.3.12.0.

I'm struggling to get AutoIT Excel to work properly for me. I am sure it's operator error, but after several hours of googling and reading through the documentation I'm still at a loss.

Core Need

*******************

I have a set of Excel files. They all have a button "Refresh" on Worksheet "Home" that is tied to the macro "TicketDetailTableRefresh". I need to click that so the Excel sheet updates.

Problem

*******************

Two problems really.

First, _Excel_SheetActivate($oExcel, $sSheet) keeps giving me "undefined function". _ExcelSheetActivate gives me the same error.

Second, when I try to use Run it fails. Specifically, I get this error from my error code:

Error Excel.Run: TicketDetailTableRefresh @error = -2147352570, @extended = 0

The code is below:

#include <Constants.au3>
#include <Excel.au3>


Local $sFilePath1 = $CmdLine[1]
Local $sSheet = $CmdLine[2]
Local $sButton = $CmdLine[3]


Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)


Local $oExcel = _Excel_BookOpen($oAppl, $sFilePath1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookOpen: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)


;_Excel_SheetActivate($oExcel, $sSheet)
$sButton = "TicketDetailTableRefresh"
$oExcel.Run($sButton)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error Excel.Run: " & $sButton & @CRLF & "@error = " & @error & ", @extended = " & @extended)


_Excel_BookClose($oExcel, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookClose: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)


_Excel_Close($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

To call this script, I use this:

%AUTOIT_CMD% %AUTOIT_SCRIPT% "%WORKBOOK_DIR%\Workbook_Daily.xlsm" "Home" Workbook_Daily.xlsm!TicketDetailTableRefresh"
Actually, this worked before, kind of. I had a slightly older version of AutoIT. However, I upgraded and I started changing the code to use the new function names and boom.

 

Share this post


Link to post
Share on other sites



_Excel_SheetActivate no longer exists. It was a one line function and can be replaced by

$oWorkbook.Sheets.Item(x).Activate ; x can be the index or name of the sheet to activate

Details about removed/renamed functions can be found here.

-2147352570 (decimal) means: HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME)

Are you sure that is the correct name of the macro?


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

#3 ·  Posted (edited)

Well, what the heck.
 
I just tried these names:
 
Autotask_Service_Workbook_Daily.xlsm!TicketDetailTableRefresh
TicketDetailTableRefresh
 
In Excel, I am doing this to get the name:

  •  
  • Open workbook.
  • Click on worksheet.
  • Right-click on button.
  • Select "Assign Macro.."
  • Grabbing the macro name.

See screenshot. [Wait. How do I add a screenshot?]
 
Am I doing something wrong?

Edited by dustinp4340

Share this post


Link to post
Share on other sites

Any help on this last part? I am 100% stuck. Thanks.

Share this post


Link to post
Share on other sites

Add

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

so you will get more detailed error information in a separate window.


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 get this:

@@ DEBUG COM Error encountered in UpdateWorkBook.au3 (66) :
Number         = 0x80020006 (-2147352570)
WinDescription = Unknown name.
Description    = 
Source         = 
HelpFile       = 
HelpContext    = 
LastDllError   = 0
Retcode        = 0x00000000
@@ DEBUG COM Error encountered in UpdateWorkBook.au3 (22) :
Number         = 0x80020006 (-2147352570)
WinDescription = Unknown name.
Description    = 
Source         = 
HelpFile       = 
HelpContext    = 
LastDllError   = 0
Retcode        = 0x00000000
>>>>>> Please close the "Report Log Window" to exit <<<<<<<

Share this post


Link to post
Share on other sites

The first COM error on line 66 is being expected.

But what is on line 22 of your script?


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 just did a clean run. Here is the script:

#include <Constants.au3>
#include <Excel.au3>


#include <Debug.au3>
_DebugSetup()
_DebugCOMError()


Local $sFilePath1 = $CmdLine[1]
Local $sSheet = $CmdLine[2]
Local $sButton = $CmdLine[3]


Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)


Local $oExcel = _Excel_BookOpen($oAppl, $sFilePath1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookOpen: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)


$oExcel.Sheets.Item($sSheet).Activate
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error Excel.Sheets.Item.Activate: " & $sSheet & @CRLF & "@error = " & @error & ", @extended = " & @extended)


$sButton = "Autotask_Service_Workbook_Daily.xlsm!TicketDetailTableRefresh"
$oExcel.Run($sButton)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error Excel.Run: " & $sButton & @CRLF & "@error = " & @error & ", @extended = " & @extended)


_Excel_BookClose($oExcel, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookClose: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)


_Excel_Close($oAppl)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Here is the debug output:
 
@@ DEBUG COM Error encountered in UpdateWorkBook.au3 (22) :
Number         = 0x80020006 (-2147352570)
WinDescription = Unknown name.
Description    = 
Source         = 
HelpFile       = 
HelpContext    = 
LastDllError   = 0
Retcode        = 0x00000000
>>>>>> Please close the "Report Log Window" to exit <<<<<<<
 

Share this post


Link to post
Share on other sites

As an FYI, I am getting the macro name in two ways to verify I'm not nuts. The second and obvious method is to open the Excel file in Excel 2013 and click View->Macros->View Macros. I then select the macro "TicketDetailTableRefresh" and then click Run. It runs properly and everything updates.

If I do $oExcel.Run("TicketDetailTableRefresh") I get the error listed.

Driving me bonkers.

Share this post


Link to post
Share on other sites

Quick update. I ran this VBS and it worked:

Option Explicit


    LaunchMacro


    Sub LaunchMacro() 
      Dim xl
      Dim xlBook      
      Dim sCurPath


        sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
        Set xl = CreateObject("Excel.application")
        Set xlBook = xl.Workbooks.Open("E:\AutoITJobs\AutotaskWorkbooks\WorkBooks\Autotask_Service_Workbook_Daily.xlsm", 0, True)      
        xl.Application.Visible = True
        xl.Application.run "Autotask_Service_Workbook_Daily.xlsm!TicketDetailTableRefresh"
        xl.DisplayAlerts = False        
        xlBook.saved = True
        xl.activewindow.close
        xl.Quit


        Set xlBook = Nothing
        Set xl = Nothing


End Sub 

Share this post


Link to post
Share on other sites

Could you please try this version?

#include <Constants.au3>
#include <Excel.au3>

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

Local $sFilePath1 = $CmdLine[1]
Local $sSheet = $CmdLine[2]
Local $sButton = $CmdLine[3]

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookOpen: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$oWorkbook.Sheets.Item($sSheet).Activate
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error Excel.Sheets.Item.Activate: " & $sSheet & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$sButton = "Autotask_Service_Workbook_Daily.xlsm!TicketDetailTableRefresh"
$oExcel.Run($sButton)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error Excel.Run: " & $sButton & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_Excel_BookClose($oWorkbook, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_BookClose: " & $sFilePath1 & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_Excel_Close($oExcel)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

The naming of the Application and Workbook objects led my to the wron assumtions.

You need to use the Run method on the Apllication object, not te Workbook.

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

Success!

Share this post


Link to post
Share on other sites

So the error message "Unknown name" was not related to the macro but to the run method which is not available for the Workbok object.

Once again we can see how important a consistent naming scheme is :)


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

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
Sign in to follow this  
Followers 0