Jump to content

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


Recommended Posts

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.

 

Link to comment
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 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, 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
Link to comment
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 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 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 <<<<<<<
Link to comment
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 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 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 <<<<<<<
 
Link to comment
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.

Link to comment
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 
Link to comment
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.

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

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

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...