Inject dymatic macro into excel

[Noddle]

Hi,

I wanting a way to inject a dymatic macro into excel,

this is the code I want to inject,

at the moment it just spits it out to console,  and I copy paste it into excel

any help would be appreciated

Nigel

Func _WriteMacro()
ConsoleWrite ('Private Sub Worksheet_Change(ByVal Target As Range)' & @CRLF )
ConsoleWrite (chr(9) & 'NeedtoPay = Range("A1").Value' & @CRLF )
ConsoleWrite ('Select Case Target.Address' & @CRLF )

For $x = 3 to $_Repayment + 3
    ConsoleWrite (chr(9) & 'Case "$E$' & $x & '"' & @CRLF )
    ConsoleWrite (chr(9) & chr(9) & "Beep" & @CRLF )
    ConsoleWrite (chr(9) & chr(9) & 'If UCase(Range("E' & $x & '").Value) = "N" Then' & @CRLF)
    ConsoleWrite (chr(9) & chr(9) & chr(9) & 'Range("D' & $x & '").Value = 0' & @CRLF)
    ConsoleWrite (chr(9) & chr(9) & chr(9) & 'Total = Application.Sum(Range(Cells(3, 4), Cells(32, 4)))' & @CRLF )
    ConsoleWrite (chr(9) & chr(9) & chr(9) & 'NextToPay = Range("D' & $x+1 & '").Value'  & @CRLF)
    ConsoleWrite (chr(9) & chr(9) & chr(9) & 'Range("D' & $x+1 & '").Value = (NeedtoPay - Total) + NextToPay' & @CRLF)
    ConsoleWrite (chr(9) & chr(9) & "End If" & @CRLF)
Next

ConsoleWrite ("End Select" & @CRLF)
ConsoleWrite ("End Sub" & @CRLF)
EndFunc

[water]

Please have a look at the MacroRecord method.

[Noddle]

Please have a look at the MacroRecord method.

Does the autoIT "program" I build need to be running for this to work ?

since I will only be using excel and the spreadsheet I created, 

sorry for my ignorance,   I rarely use excel,  except to do simple recording of data.

Nigel

[water]

You run the AutoIt script once to inject the macro into the Excel workbook. That's all.

[Noddle]

Hi,

Is there a way to inject my macro into the  Current Sheet,  "Worksheet_Change" area

if I use this it does not work,

$oExcel.VBE.ActiveVBProject.VBComponents("Sheet1").Import(@ScriptDir & "\Test.bas")

if I use this,  It goes into the "Module1"

$oExcel.VBE.ActiveVBProject.VBComponents.Import(@ScriptDir & "\Test.bas")

I'll be wanting a different macro in each sheet, on the "worksheet_change" function,

this is the  tes code i'm playing with at the moment, till I can get it to work,

Thanks in advance for any help,  Nigel

expandcollapse popup

#cs
Test.bas  < -   file name of  macro file.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$3"
        If UCase(Range("E3").Value) = "N" Then
            Beep
        End If

End Select
End Sub
#ce

#include <excel.au3>
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Global $oExcel = _ExcelBookNew()

$oExcel.VBE.ActiveVBProject.VBComponents("Sheet1").Import(@ScriptDir & "\Test.bas")
; $oExcel.VBE.ActiveVBProject.VBComponents.Import(@ScriptDir & "\Test.bas")

;  $oExcel.Run("Worksheet_Change")
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

[water]

After import set the name of the macro using:

$oExcel.VBE.ActiveVBProject.Name = "Worksheet_Change"

[Bert]

Excel has a macro recorder you can use and then simply you assign it a hotkey to the macro. That would be LOTS simpler for you can keep it in Excel and it writes the VBA code for you. I use Excel all the time and write in VBA. If you really want to get fancy there are several good Excel forums you can reference for solutions. I personally like MrExcel for there are hundreds of users there and the site gets lots of traffic.

Edited February 22, 2014 by MBALZESHARI

[water]

Noddle,

are we talking about a single workbook with multiple sheets or about many workbooks with multiple sheets?

If you just want to insert the macro into a single workbook then I would use MBALZESHARIs approach.

Edited February 22, 2014 by water

[Noddle]

Noddle,

are we talking about a single workbook with multiple sheets or about many workbooks with multiple sheets?

If you just want to insert the macro into a single workbook then I would use MBALZESHARIs approach.

 

It will be multiple workbooks with multiple worksheets,

the macro i want to inject into each worksheet, will be dynamically created,  depending on the data I will be injecting ( different data per worksheet )

Nigel.

[Noddle]

Hi,

Maybe I should not have said "inject dynamic macro", but instead "inject dynamic VB code",  which is what I want to do,

the picture may explain better where I want to inject my code,

The green arrow / box  shows where I want to inject the code,  at the moment, I can get it into the "red" arrow area, but i'm not wanting it there.

[Bert]

if that is the case then just specify the sheet in the vba code.

[Noddle]

if that is the case then just specify the sheet in the vba code.

 

To be hounest,  I did not know how to do that,  I have no / little experence with using VB, excel, and autoit and excel,  till a few weeks ago,

but with my mate "google", I found what I needed,

there may be a easier, better way to do this, but this works.

here's some code, incase someone else is looking at doing this,

Nigel

expandcollapse popup

#include <excel.au3>
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
$oExcel = ObjCreate("Excel.Application")
;$oExcel.Visible = 0
$oExcel.Visible = 1

 $FileName = FileOpenDialog("Select Excel File", "C:\", "Excel Workbooks (*.xls)", 1 )
    If @error Then Exit
 $oExcel.WorkBooks.Open($FileName)

$ModuleName = "Sheet1"
$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

For $oModule in $oModules
    If $oModule.Type = 100 And $oModule.Name = $ModuleName Then
        $oModule.CodeModule.AddFromString ( _
                                            'Private Sub Worksheet_Change(ByVal Target As Range)' & @CRLF & _
                                            'Select Case Target.Address' & @CRLF & _
                                            '    Case "$E$3"' & @CRLF & _
                                            '        If UCase(Range("E3").Value) = "N" Then' & @CRLF & _
                                            '            Beep'  & @CRLF & _
                                            '        End If' & @CRLF & _
                                            'End Select' & @CRLF & _
                                            'End Sub' _
                                            )
        ConsoleWrite ("Writing Code")
    EndIf
Next

;$oExcel.ActiveWorkbook.Save
;$oExcel.ActiveWorkbook.Close
;$oExcel.Quit
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

Edited February 26, 2014 by Noddle