Jump to content

Inject dymatic macro into excel


Noddle
 Share

Go to solution Solved by Noddle,

Recommended Posts

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
Link to comment
Share on other sites

Please have a look at the MacroRecord method.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

#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
Link to comment
Share on other sites

After import set the name of the macro using:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by MBALZESHARI
Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Pic1.jpg

Link to comment
Share on other sites

  • Solution

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

#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 by Noddle
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...