I need to add a code module to Excel from Access via automation.

I've looked at these 2 sites for help:

http://www.j-walk.com/ss/excel/tips/tip96.htm
http://www.cpearson.com/excel/vbe.htm

For starters, I'm trying to simply add a module to an Excel workbook from a
standard Excel module.

The code below returns this:

This workbook has 6 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed

Is there something wrong with the way I'm instantiating newmod?
Any guidance would be appreciated.
Thanks in advance.

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) = 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & vbpCt & " modules."
End If
End If
Exit_He
Exit Function
HandleErr:
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
End Function

I tried this from Excel, and after renaming the variable vbpCpt to intCpt,
it ran fine for me.

If running this from Excel, you need to set a variable object to the Excel
app, and qualify each Excel object with that app object. Not sure if that
applies equally to VBE objects, never tried it from another app, but try it
anyways.

--

HTH

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


"deko" wrote in message
. ..
I need to add a code module to Excel from Access via automation.

I've looked at these 2 sites for help:

http://www.j-walk.com/ss/excel/tips/tip96.htm
http://www.cpearson.com/excel/vbe.htm

For starters, I'm trying to simply add a module to an Excel workbook from
a
standard Excel module.

The code below returns this:

This workbook has 6 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed

Is there something wrong with the way I'm instantiating newmod?
Any guidance would be appreciated.
Thanks in advance.

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) = 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual
Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & vbpCt & " modules."
End If
End If
Exit_He
Exit Function
HandleErr:
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
End Function

I tried this from Excel, and after renaming the variable vbpCpt to intCpt,
it ran fine for me.

What did you try? There was no variable named vbpCpt in my code.

If running this from Excel, you need to set a variable object to the Excel
app, and qualify each Excel object with that app object.

Do you mean "if running this from Access"... ??

Not sure if that applies equally to VBE objects,
never tried it from another app, but try it anyways.

hmmm... do I need to instantiate VBE objects differently?

"deko" wrote in message
m...
I tried this from Excel, and after renaming the variable vbpCpt to
intCpt,
it ran fine for me.

What did you try? There was no variable named vbpCpt in my code.

Okay, so I got the name slightlyu wrong. This is the code, it should have
been vbpCt

Debug.Print "This workbook has " & vbpCt & " modules."

If running this from Excel, you need to set a variable object to the
Excel
app, and qualify each Excel object with that app object.

Do you mean "if running this from Access"... ??

Or an other automation client.

Not sure if that applies equally to VBE objects,
never tried it from another app, but try it anyways.

hmmm... do I need to instantiate VBE objects differently?

Don't know as I said I have never done it, but I would expect so.

Ah, I see...

Debug.Print "This workbook has " & vbpCt & " modules."

Yes, that was a mistake in my code... should be intCt

Still, the problem I had was instantiating newmod. You say that code
instantiated newmod for you?

I have that code in a standard module named "Module1". I tried putting the
code in "ThisWorkbook" as well as "Sheet1" but then the code would not run
(from the immediate window using ?AddModuleToExcel).

I tried the below revised code can got this:

This workbook has 5 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed
Error Number 91: Object variable or With block variable not set
This workbook has 5 modules.

What I'm expecting to see is this:

This workbook has 5 modules.
This workbook has 6 modules.

And then, of course, see an additional module in the project from the IDE.
The next step would be finding a way to get the actual code in the module,
and also getting the code to run via automation (as you mentioned, this
should simply be a matter of creating an object to hold reference to the
Excel application object and using something like xlapp.vbp = Active
Workbook.VBProject).

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) = 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
'Set newmod =
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
End If
End If
Exit_He
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function

Got it working. I put the below code in a standard module named "Module1"
and run it from the immediate window:

?AddMod
This workbook has 6 modules.
This workbook has 7 modules.

This KB helped: http://support.microsoft.com/?kbid=245801

The key was setting a reference to Microsoft Visual Basic for Applications
Extensibility.

But this raises a new question: Can I programmatically set the reference?
My code prompts the user to change security settings if necessary, which
gets around one potential hurdle, but how do I set the required reference?

Public Function AddMod()
On Error GoTo HandleErr
Dim vbcoms As VBComponents
Dim vbp As Object
Dim objMod As Object
Dim intCt As Integer
If Val(Application.Version) = 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
Set vbcoms = Application.VBE.ActiveVBProject.VBComponents
intCt = vbcoms.Count
Debug.Print "This workbook has " & intCt & " modules."
Set objMod = vbcoms.Add(vbext_ct_StdModule)
objMod.Name = "MyNewModule"
intCt = vbcoms.Count
Debug.Print "This workbook has " & intCt & " modules."
End If
End If
Exit_He
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function
'http://www.j-walk.com/ss/excel/tips/tip96.htm
'http://www.cpearson.com/excel/vbe.htm
'http://support.microsoft.com/?kbid=245801

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

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