Jump to content
Sign in to follow this  
Noddle

Inject dymatic macro into excel

Recommended Posts

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

Share this post


Link to post
Share on other sites
water

Please have a look at the MacroRecord method.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

#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

Share this post


Link to post
Share on other sites
water

After import set the name of the macro using:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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

Pic1.jpg

Share this post


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

#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

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
Sign in to follow this  

×