import excel macro

[13lack13lade]

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

[water]

Can't test but maybe it's:

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

[13lack13lade]

No dice unfortunately!

Can anyone help at all?

Edited August 1, 2013 by 13lack13lade

[water]

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

[13lack13lade]

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 August 1, 2013 by 13lack13lade

[water]

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

[13lack13lade]

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

[water]

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

[13lack13lade]

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

Sub test()

 

MsgBox ("Hello world")

 

End Sub

[water]

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

[13lack13lade]

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 August 2, 2013 by 13lack13lade

[13lack13lade]

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

[Myicq]

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.

[coffeeturtle]

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

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

[water]

Great job as usual, Water!

Thanks for the compliment