Workbook_Open()

[aroV]

I’m opening a workbook from Access, populating and formatting some cells and saving the workbook. I need to put some code in workbook sub so when user opens the spreadsheet code will be triggered. Below is the code that I use to create the workbook.

Dim xlExcel As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
 
Set xlExcel = CreateObject("Excel.Application")
Set xlWorkbook = xlExcel.Workbooks.Open(strFilePath)
 
'do some formating
'need to change or put some code in Workbook_Open() 
 
 
xlWorkbook.Save
xlWorkbook.Close
xlExcel.Application.Quit
Set xlWorkbook = Nothing
Set xlExcel = Nothing

Please let me know if that is possible to modify the code in the Workbook_Open(). Thanks for your help.

[DKenny]

Welcome to the forums!

This code snipped should give you a start...

Dim sCode As String
    Dim lNextLine As Long
 
    sCode = "Private Sub Workbook_Open()" & vbCrLf
    sCode = sCode & "MsgBox ""Hi aroV"" " & vbCrLf
    sCode = sCode & "End Sub"
    
    With xlWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        lNextLine = .CountOfLines + 1
        .InsertLines lNextLine, sCode
    End With

[aroV]

Thanks DKenny,
I have tried to execute this code. I’m getting a runtime error 1004 – “Programmatic access to Visual Basic Project is not trusted”. Thanks again.

[aroV]

Below is the resolution from Microsoft:

For any Automation client to be able to access the VBA object model programmatically, the user running the code must explicitly grant access. To turn on access, the user must follow these steps:
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.
3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

I’m wondering this change can be done programmatically?

[westconn1]

you can put most of the code from the macro into your vbcode (with some editing to tell it it is the excel object) then you don't need to call the macro at all,

pete