13lack13lade 22 Posted July 31, 2013 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
water 2,365 Posted July 31, 2013 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
13lack13lade 22 Posted July 31, 2013 (edited) No dice unfortunately! Can anyone help at all? Edited August 1, 2013 by 13lack13lade Share this post Link to post Share on other sites
water 2,365 Posted August 1, 2013 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
13lack13lade 22 Posted August 1, 2013 (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 August 1, 2013 by 13lack13lade Share this post Link to post Share on other sites
water 2,365 Posted August 1, 2013 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
13lack13lade 22 Posted August 1, 2013 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
water 2,365 Posted August 1, 2013 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
13lack13lade 22 Posted August 1, 2013 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
water 2,365 Posted August 1, 2013 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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites
13lack13lade 22 Posted August 2, 2013 (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 August 2, 2013 by 13lack13lade Share this post Link to post Share on other sites
13lack13lade 22 Posted August 2, 2013 Am i able to use the RunAs command to bypass this or no? Share this post Link to post Share on other sites
Myicq 20 Posted August 2, 2013 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
coffeeturtle 6 Posted August 2, 2013 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
water 2,365 Posted August 2, 2013 Great job as usual, Water! Thanks for the compliment My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites