You don't need to set a reference to that library, that is early binding, it
is just as simple with late binding. Just change this line

Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)

to

Set newmod = vbp.VBComponents.Add(1)

To add code, try something like

'----------------------------------------------------------------
Sub AddModuleProc()
'----------------------------------------------------------------
Dim StartLine As Long
Dim cLines As Long

With ActiveWorkbook.VBProject.VBComponents("Module2").C odeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"Sub myProc()" & Chr(13) & _
" Msgbox ""myProc installed"" " & Chr(13) & _
"End Sub"
End With
End Sub

adapted to your code of course.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" wrote in message
...
Third time's a charm...

The below code adds a standard module to Book1.xls from Access via
automation. This assumes that "Trust access to Visual Basic Project" is
checked on the Trusted Publishers tab of the Security Dialog in Excel
(accessed from Tools Macro Security). I've omitted the code that
alerts the user with a message box if this is not the case.

The reference I was worried about (Microsoft Visual Basic for Applications
Extensibility) I don't need in the Excel workbook - I just set it in
Access
(which I have control over). Now if I can just figure out how to get my
code into the module...

Public Function AddMod()
On Error GoTo HandleErr

Dim xlapp As Excel.Application
Dim strXlsPath As String
Dim strXlsFile As String
Dim intCt As Integer

strXlsPath = "C:\Book1.xls"
strXlsFile = "Book1.xls"
Set xlapp = CreateObject("Excel.Application")
xlapp.Workbooks.Open (strXlsPath)
intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count
Debug.Print strXlsFile & " has " & intCt & " modules."
xlapp.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
intCt = xlapp.VBE.ActiveVBProject.VBComponents.Count
Debug.Print strXlsFile & " has " & intCt & " modules."

Exit_He
xlapp.Workbooks(strXlsFile).Save
xlapp.Workbooks(strXlsFile).Close
xlapp.Quit
Set xlapp = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": ";
Err.Description
Resume Next
End Select
End Function