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

[dustinp4340]

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.

 

[water]

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

[dustinp4340]

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 July 16, 2014 by dustinp4340

[dustinp4340]

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

[water]

Add

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

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

[dustinp4340]

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

[water]

The first COM error on line 66 is being expected.

But what is on line 22 of your script?

[dustinp4340]

I just did a clean run. Here is the script:

expandcollapse popup

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

 

[dustinp4340]

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.

[dustinp4340]

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 

[water]

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.

[dustinp4340]

Success!

[water]

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