Jump to content
Becca

Invoking vba script via autoit Run not working

Recommended Posts

Becca

I am trying to create an autoit script that will create and save an Excel workbook, import a vba script/macro and then open the workbook and run the script. I have it working up to the Run at which point I get an error. My autoit script and the vba code are below.  Googling and posted samples have helped me get to where I am now but I am stuck getting the macro to run. The error I get (on the last line) is "The requested action with this object has failed." Any help would be greatly appreciated. Thank you.

autoit script:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Create a new workbook and save it
; Create the new workbook
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Save the workbook (replacing existing file)
_Excel_BookSaveAs($oWorkbook, @DesktopDir & "\MyNewExcel2.xls", Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error saving workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oExcel.VBE.ActiveVBProject.VBComponents.Import(@DesktopDir & "\Module1.bas")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookSave Example 1", "Error importing." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_BookOpen($oExcel,@DesktopDir & "\MyNewExcel2.xls")
;$oExcel.Run("Module1.bas")
$oExcel.Application.Run("MyNewExcel2.xls!Module1.bas")

macro code:

Attribute VB_Name = "Module1"
Sub MergeWkbks()
Path = "C:\Users\rebecca.bryant\Desktop\MergingExcelTest\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

 

Share this post


Link to post
Share on other sites
Juvigy

Try it like this:   $oExcel.Application.Run("Macro")  Where 'Macro' is the name of your macro. Also i see that your macro code is wrong or incomplete. Does it run when you manually start it?

Share this post


Link to post
Share on other sites
Becca

Thank you for your reply Juvigy. I have tried  $oExcel.Application.Run("Module1.bas") and that doesn't work either. And yes, the macro does run if I manually start it. What are the errors that you see in the macro?

Thanks again

Share this post


Link to post
Share on other sites
Becca

I have made some changes to the macro as per an amended version that I found on stackoverflow:

Option Explicit
Const path As String = "C:\MergingExcelTest\"
Sub GetSheets()
Dim FileName As String
Dim wb As Workbook
Dim sheet As Worksheet

FileName = Dir(path & "*.xls*")
  Do While FileName <> ""
  Set wb = Workbooks.Open(FileName:=path & FileName, ReadOnly:=True)
     For Each sheet In wb.Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next sheet
     wb.Close
     FileName = Dir()
  Loop
End Sub

Share this post


Link to post
Share on other sites
Subz
Posted (edited)

This worked fine for me, in Office 2013 SP1 32 bit.

#include <Excel.au3>

Local $sModule = @TempDir & "\Module.bas"
Local $sMacro
    $sMacro &= 'Attribute VB_Name = "Module1"' & @CRLF
    $sMacro &= 'Sub MergeWkbks()' & @CRLF
    $sMacro &= 'Path = "' & @DesktopDir & '\MergingExcelTest\"' & @CRLF
    $sMacro &= 'Filename = Dir(Path & "*.xlsx")' & @CRLF
    $sMacro &= '    Do While Filename <> ""' & @CRLF
    $sMacro &= '    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True' & @CRLF
    $sMacro &= '            For Each Sheet In ActiveWorkbook.Sheets' & @CRLF
    $sMacro &= '            Sheet.Copy After:=ThisWorkbook.Sheets(1)' & @CRLF
    $sMacro &= '    Next Sheet' & @CRLF
    $sMacro &= 'Workbooks(Filename).Close' & @CRLF
    $sMacro &= 'Filename = Dir()' & @CRLF
    $sMacro &= 'Loop' & @CRLF
    $sMacro &= 'End Sub' & @CRLF
    Local $hFileOpen = FileOpen($sModule, 2)
    FileWrite($hFileOpen, $sMacro)
    FileClose($hFileOpen)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
$oExcel.VBE.ActiveVBProject.VBComponents.Import($sModule)
_Excel_BookSaveAs($oWorkbook, @DesktopDir & "\MyNewExcel2.xls", Default, True)
$oExcel.Application.Run("MergeWkbks")

@Becca can you place your code between <> just makes it easier to read.

Edited by Subz
  • Like 1

Share this post


Link to post
Share on other sites
Juvigy
12 hours ago, Becca said:

Thank you for your reply Juvigy. I have tried  $oExcel.Application.Run("Module1.bas") and that doesn't work either. And yes, the macro does run if I manually start it. What are the errors that you see in the macro?

Thanks again

Your error is that you try to run the file name. You need to run the macro/function name.  I would guess you need to use " GetSheets "

Share this post


Link to post
Share on other sites
Becca

Thank you Subz. That worked!!! And thank you Juvigy also. (And in the future I will use <> for posting code.)

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

×