4

I have many excel files (approx 200) in which I have to add VBA code (in the Workbook_Open() method).
I would like to automate that, is there a way to add VBA code to an excel sheet programmatically?

Maybe with python's win32 package?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Leogout
  • 1,187
  • 1
  • 13
  • 32
  • Yes it is possible. Will all the files have the same code? – Siddharth Rout Dec 24 '19 at 13:20
  • The code is almost identical but must contain a unique ID for each excel file, something like: `Public Const ID As String = "TDW...LC2"`. – Leogout Dec 24 '19 at 13:24
  • I can show you an example for 1 workbook. You can use DIR to open the file in a loop and insert the code. Is that ok? – Siddharth Rout Dec 24 '19 at 13:29
  • Does this answer your question? [Excel vba add code to sheet module programmatically](https://stackoverflow.com/questions/34837006/excel-vba-add-code-to-sheet-module-programmatically) And [Cpearson's](http://www.cpearson.com/excel/vbe.aspx) site should also show you in the right direction. – Storax Dec 24 '19 at 13:33
  • @Storax: Just saw your comment. Yes it should point in the right direction. That link by Chip should definitely help but this post is not a duplicate of Dave's post. It may be talking about `CreateEventProc` but it is for `worksheet change`. Further more, there is one important thing which is missing in that post. That is about `Trust access to the VBA project object model`. It also relies on Early Binding whereas this post is not. – Siddharth Rout Dec 24 '19 at 13:43
  • @Siddharth: Yes, certainly not an excat duplicate but IMHO pretty similar. I would expect the OP to do the "transfer" from `worksheet_change` to `workbook_open`. But you are right, the hint regarding _trust access to the VBA Project_ is missing. – Storax Dec 24 '19 at 14:13

2 Answers2

7

You do not need python for this.

Logic

  1. Open the file in a loop.
  2. Use .CreateEventProc to write to the relevant code section. More about .CreateEventProc HERE
  3. Save and close the workbook.

Basic Requirements

You need to enable access to Visual Basic projects.

  1. Click the File tab.
  2. Click Options.
  3. Click Trust Center, and then click Trust Center Settings.
  4. In the Trust Center, click Macro Settings.
  5. Check Trust access to the VBA project object model to enable access to Visual Basic Projects.
  6. Click OK.

Code

Option Explicit

Sub Sample()
    Dim VBP As Object, VBC As Object, CM As Object
    Dim wb As Workbook

    Set wb = Workbooks.Open("C:\Users\routs\Desktop\Sample.xlsm")

    Set VBP = wb.VBProject
    Set VBC = VBP.VBComponents("ThisWorkbook")
    Set CM = VBC.CodeModule

    With VBC.CodeModule
        .InsertLines Line:=.CreateEventProc("Open", "Workbook") + 1, _
        String:=vbCrLf & _
        "    Debug.Print ""This is a sample text"""
    End With

    'wb.Close (True)
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Further hint regarding internationalization: note that the default *CodeName* `ThisWorkbook` called via `VBP.VBComponents("ThisWorkbook")` varies due to regional settings, e.g. `DieseArbeitsmappe` in German :-) – T.M. Dec 26 '19 at 15:30
1

Is there a way to programmatically copy code from one sheet except paste into a specific location of the code in the new sheet?

Basically what I'm trying to accomplish:

I have a couple of conditional formatting rules that I only want to be applicable after I've pressed a keyboard shortcut. I want to still be able to use the undo stack up to a certain point.

This code allows the particular conditional formatting to work the way I want it to, and is the only code on Sheet2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 With ThisWorkbook.Names("CurrentRow")
         .Name = "CurrentRow"
         .RefersToR1C1 = "=" & ActiveCell.Row
End With
End Sub

This is what I have for the code to run the copy

Sub CondFormat()
'
' CondFormat Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim CodeCopy As VBIDE.CodeModule
Dim CodePaste As VBIDE.CodeModule
Dim numLines As Integer

Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
Set CodePaste = ActiveWorkbook.VBProject.VBComponents("Sheet4").CodeModule

numLines = CodeCopy.CountOfLines
If CodePaste.CountOfLines > 1 Then CodePaste.DeleteLines 1, CodePaste.CountOfLines

CodePaste.AddFromString CodeCopy.Lines(1, numLines)
End Sub

Below is the end bits of the Sheet 4 code. I don't want to delete everything on this new sheet, I would want it to appear under "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" :

...

SendKeys "{TAB}", 1
SendKeys "{TAB}", 1
SendKeys "^{v}", 2

Application.Wait (Now + TimeValue("00:00:02"))

SendKeys "{F8}", 1

Application.Wait (Now + TimeValue("00:00:02"))

Loop

SendKeys "{NUMLOCK}", 1

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Dane Ernst
  • 11
  • 1