[RESOLVED] Writing Macro to Another Spreadsheet

I have a spreadsheet which is run each day, putting its results into a
monthly results file, Thus the results for October will be in a file
Results200610October.xls

The results are then actioned by a user, with details being updated in
the results spreadsheet.

I need to run a macro in the spreadsheet when a user who has it open,
closes it, provided it is not ReadOnly.

I had this in the ThisWorkbook code pane of a spreadsheet called Book4.xls
I also had Book5.xls open. This worked fine and I was able to step
through using the Debugger easily, and it worked.
I have Microsoft Visual Basic for Applications Extensibility reference
Library selected.

VB Code:

Sub Test()
Const Book = "Book5.xls"
 
Workbooks(Book).VBProject.VBE.CodePanes.Item(1).CodeModule.InsertLines
1, "option explicit"
 
Workbooks(Book).VBProject.VBE.CodePanes.Item(1).CodeModule.InsertLines
2, "SUB Test"
 
Workbooks(Book).VBProject.VBE.CodePanes.Item(1).CodeModule.InsertLines
3, "   MsgBox ""Hello World"""
 
Workbooks(Book).VBProject.VBE.CodePanes.Item(1).CodeModule.InsertLines
4, "end sub"
End Sub

Then I converted it into a separate module in my App, Please note that
TargetWbk is the filename of the Spreadsheet I am creating. It is declared
as a Global and when I debug has the correct filename in.

VB Code:

Option Explicit
Sub FillThisWorkbookCode()
    AddLines 1, "Option Explicit"
    AddLines 2, "' These routines run through updating the Status."
    AddLines 3, "Private Sub Workbook_BeforeClose(Cancel As Boolean)"
    AddLines 4, "    ' Before Closing we will Update the Index with the
relevant Status, then we will"
    AddLines 5, "    ' Promote these out to the other sheets."
    AddLines 6, "    If Not ThisWorkbook.ReadOnly Then"
    AddLines 7, "        ThisWorkbook.Save"
    AddLines 8, "    End If"
    AddLines 9, "End Sub ' Workbook_BeforeClose"
End Sub ' FillThisWorkbookCode
 
Sub AddLines(LineNumber As Long, LineText As String)
Dim mmmm As Object
    ' Microsoft Visual Basic for Applications Extensibility
 
Workbooks(TargetWbk).VBProject.VBE.CodePanes.Item(1).CodeModule.InsertLi
nes LineNumber, LineText
End Sub

The problem is that the above code is not adding the Macro to the
ThisWorkbook code of TargetWbk, but to the Actual Module it self in front of
the code itself.

In addition it is not letting me Break to examine the program flow in
the debugger.

Can anyone advise me as to what is causing these problems.

you are addressing codepane.item(1), which is the front codepane window, try something like

VB Code:

Application.VBE.VBProjects(1).VBComponents("Module7").CodeModule.DeleteLines 7

For more examples on writting VBA code via VBA/VB 6 see my Code Bank thread - http://vbforums.com/showthread.php?t=313861

Thanks westconn1,

It works wonderfully now.

Apologies for not replying to your post sooner, disk problems meant I was unable to test until Friday.

Thanks for the link RobDog, proved very informative. :wave: