Automating Excel - Programming the VB IDE

[thebatfink]

Hi,
I have been doing this for a while within Excel using VBA. I'm now trying to perform the same function but using VB.NET.. So the code below (although modified to suit .NET) *used* to do what I wanted it to. Basically I am deleting a procedure from the ThisWorkbook codemodule and inserting a new procedure.

Dim ExcelApp As Excel.Application = New Excel.Application
Dim PlanWorkBook As Excel.Workbook
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

PlanWorkBook = ExcelApp.Workbooks.Open(PlanFileName)
VBProj = PlanWorkBook.VBProject
VBComp = VBProj.VBComponents("ThisWorkbook")
CodeMod = VBComp.CodeModule

Dim ProcName As String = "Workbook_Open"
With CodeMod
    Dim StartLine As Integer = .ProcStartLine(ProcName, VBIDE.vbext_ProcKind.vbext_pk_Proc)
    Dim NumLines As Integer = .ProcCountLines(ProcName, VBIDE.vbext_ProcKind.vbext_pk_Proc)
    .DeleteLines(StartLine:=StartLine, Count:=NumLines)
End With

With CodeMod
    Dim LineNum As Integer = .CreateEventProc("Open", "Workbook")
    LineNum = LineNum + 1
    InsertLines(LineNum, "msgbox ""hi""")
End With

The problem appears to be VBComp = VBProj.VBComponents("ThisWorkbook") is expecting an integer for the component, not the components name. With Excel this would be "ThisWorkbook" or "Module2" etc etc. An integer is fine I guess, but how do I find out what the integer value is for the component I want?

Help would be much appreciated and may save what little hair I have left from being pulled out!!
Thanks

 

[JohnH]

VBComp = VBProj.VBComponents.Item("ThisWorkbook")

 

[thebatfink]

Ahh thank you very much. So simple yet so frustrating difficult to figure out!

I do have another question if you don't mind. After running my code, the VB IDE is left open? I also seem to always be left with an instance of Excel running, but I thought I was disposing of ExcelApp correctly. The full sub is below.. Thanks again.

Public Sub UpdateHMPlans()
        Dim ExcelApp As Excel.Application = New Excel.Application
        Dim PlanWorkBook As Excel.Workbook
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim InitialPath As String
        Form1.ProgressBar1.Value = 0
        Form1.ProgressBar1.Step = 100 / (UBound(FileList) + 1)
        Form1.TextBox4.Text = "Begining Plan Update.."
        Application.DoEvents()
        For i = LBound(FileList) To UBound(FileList)
            PlanFileName = FileList(i)
            PlanWorkBook = ExcelApp.Workbooks.Open(PlanFileName)
            SaveFileName = PlanWorkBook.FullName
            Form1.TextBox4.Text = "Updating " & PlanWorkBook.Name
            Application.DoEvents()
            VBProj = PlanWorkBook.VBProject
            VBComp = VBProj.VBComponents.Item("ThisWorkbook")
            CodeMod = VBComp.CodeModule
            Dim ProcName As String = "Workbook_Open"
            With CodeMod
                Dim StartLine As Integer = .ProcStartLine(ProcName, VBIDE.vbext_ProcKind.vbext_pk_Proc)
                Dim NumLines As Integer = .ProcCountLines(ProcName, VBIDE.vbext_ProcKind.vbext_pk_Proc)
                .DeleteLines(StartLine:=StartLine, Count:=NumLines)
            End With
            With CodeMod
                Dim LineNum As Integer = .CreateEventProc("Open", "Workbook")
                LineNum = LineNum + 1
                InsertLines(LineNum, "MsgBox ""Hi""")
            End With
            VBProj = PlanWorkBook.VBProject
            VBComp = VBProj.VBComponents.Item("Module3")
            VBProj.VBComponents.Remove(VBComp)
            InitialPath = "C:\mypath\"
            PlanWorkBook.VBProject.VBComponents.Import(InitialPath & "Module3.bas")
            PlanWorkBook.Save()
            PlanWorkBook.Close()
            PlanWorkBook = Nothing
            Form1.ProgressBar1.PerformStep()
        Next i
        Form1.TextBox4.Text = "Update Complete.."
        Application.DoEvents()
        ExcelApp.Quit()
        PlanWorkBook = Nothing
        ExcelApp = Nothing
    End Sub

 

[JohnH]

So simple yet so frustrating difficult to figure out!

The Office interop libraries can be quirky sometimes.

After running my code, the VB IDE is left open?

I noticed that too, first time I've automated the VBA modules, so I don't know about that. Didn't see an immediate solution other than calling this after code modifications:

ExcelApp.VBE.MainWindow.Visible = False

Just a flash of that window was seen then, setting it before didn't have any effect.

I also seem to always be left with an instance of Excel running, but I thought I was disposing of ExcelApp correctly.

When application closes I see the Excel instance is removed too, to have it release earlier I could call this code after Quit call:

        System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
        GC.Collect()

 

[thebatfink]

Thanks very much for your time. Really helped.

Yeah I noticed garbage was sorted when the app closed, but didn't understand why it held an instance open until the app was explicitly closed. I think I'll use the GC.Collect you suggest if only to help me remember its function! always nice to have a better understanding of things (I have previously read it's bad practise to do this as it consumes unnessicary resource).

Again, thanks