Sign in to follow this  
Followers 0
13lack13lade

import excel macro

15 posts in this topic

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

Share this post


Link to post
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 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)

No dice unfortunately!

Can anyone help at all? :(

Edited by 13lack13lade

Share this post


Link to post
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 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)

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

Share this post


Link to post
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 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

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

Share this post


Link to post
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 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

For testing purposes i imagine this would be all you would need?

Sub test()
 
MsgBox ("Hello world")
 
End Sub

Share this post


Link to post
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 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

#11 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

Am i able to use the RunAs command to bypass this or no?

Share this post


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

Share this post


Link to post
Share on other sites

Looks like I'm too late for the party, BlackBlade! :)

Glad you got a working solution!  Great job as usual, Water!

Share this post


Link to post
Share on other sites

Great job as usual, Water!

Thanks for the compliment :)


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