Jump to content

import excel macro


Recommended Posts

Hi Everyone,

I am trying to import an excel macro into a workbook using autoit - I DID originally have this working however accidently deleted the script :(

Cant figure out how to make it work :(

 

local $sFilePath1 = ("calypso.au.flitech.netsqlreptsp_wntvalid.xls")
Local $oExcel = _ExcelBookOpen($sFilePath1)
Sleep(1000)
$oExcel.VBE.ActiveVBProject.VBComponents.Import("fbnecl3DocumentsLoad SupportTomAutomationTurning Codes OfflineOffline.bas")
sleep(1000)
$oExcel.run("Offline.bas")
Link to comment
Share on other sites

Can't test but maybe it's:

$oExcel.Application.VBE.ActiveVBProject.VBComponents.Import("\\fbnecl3\Documents\Load Support\Tom\Automation\Turning Codes Offline\Offline.bas")

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

Can you please add a COM error handler so we get better error information? Please check the help file for ObjEvent for an example.

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

C:UserswebbthDesktopAutomationTurning Expired Codes OfflineAttempt1.au3 (90) : ==> The requested action with this object has failed.:
$oExcel.ActiveVBProject.VBComponents.Import( "fbnecl3DocumentsLoad SupportTomAutomationTurning Codes Offline" & "Offline.bas")
$oExcel.ActiveVBProject^ ERROR
>Exit code: 1    Time: 5.211
 
 
Sorry, im not sure how to do the COM error handler thing even with the help file. ~ not a programmer.
Edited by 13lack13lade
Link to comment
Share on other sites

You need something like this:

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
local $sFilePath1 = ("\\calypso.au.flitech.net\sqlrepts\p_wntvalid.xls")
Local $oExcel = _ExcelBookOpen($sFilePath1)
Sleep(1000)
$oExcel.VBE.ActiveVBProject.VBComponents.Import("\\fbnecl3\Documents\Load Support\Tom\Automation\Turning Codes Offline\Offline.bas")
sleep(1000)
$oExcel.run("Offline.bas")
exit
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

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

copying your code exactly it returned this:

>"C:Program Files (x86)AutoIt3SciTE..autoit3.exe" /ErrorStdOut "C:UserswebbthDesktopAutomationTurning Expired Codes OfflineAttempt1.au3"
err.number is: 169
err.windescription: Variable must be of type 'Object'.
err.description is:
err.source is:
err.helpfile is:
err.helpcontext is: 3014770
err.lastdllerror is: 0
err.scriptline is: 84
err.retcode is: 2097218
 
err.number is: 169
err.windescription: Variable must be of type 'Object'.
err.description is:
err.source is:
err.helpfile is:
err.helpcontext is: 3014770
err.lastdllerror is: 0
err.scriptline is: 86
err.retcode is: 2097218
 
>Exit code: 0    Time: 2.512
Link to comment
Share on other sites

I will do some testing as soon as I am in my office again.

Can you post a small macro I can test with (something like "Hello World")?

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

This works fine for me with Excel 2010 32bit on Windows 7 64 bit and AutoIt 3.3.8.1:

#include <excel.au3>
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Global $oExcel = _ExcelBookNew()
$oExcel.VBE.ActiveVBProject.VBComponents.Import(@ScriptDir & "\Hello_World.bas")
$oExcel.Run("test")
Exit
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

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

Tried your code above and it worked - seems the problem was not with my code as such but the actual location of the excel file.

You sir are a genius! - thanks for the com handler

**Edit**

Found what was causing the error - the file location is in a password protected folder on the server...  is there commands or anything i cna do to accessopen the location?

Edited by 13lack13lade
Link to comment
Share on other sites

Good info.

Just a note to @13lack13lade.. wasn't there also an error in the last line:

$oExcel.run("Offline.bas")

 

..unless the imported macro "Offline.bas" actually contained

Sub Offline.bas()
       msgbox "Hello!"
End Sub

 

in short, failing separation between file containing macro, and procedure/sub names available in that macro.

I am just a hobby programmer, and nothing great to publish right now.

Link to comment
Share on other sites

Great job as usual, Water!

Thanks for the compliment :)

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