Insert Excel Code Using Access

[JesseH]

I can write cells to an Excel worksheet. Carrying this a bit further, it stands to reason that I can write VBA code to an excel worksheet. The problem: in what location do I place it in the worksheet for excel to recognize as code (emulate Alt F11 in excel).

Thanks in Advance,

JesseH

 

[PHV]

You have to play with the VBProjects collection of the excel application VBE object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PeasNCarrots]

Use the recordmacro method. Make sure you reference Excel Object Library.

 

[JesseH]

Dear PNC, Do you happen to have an example?

Thanks
JesseH

 

[PeasNCarrots]

You may have to change security settings in EXCEl to allow trusted access to visual basic project.

Sub AddcodetoExcel()

Dim objXl As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet


Set objXl = New Excel.Application
objXl.Visible = True
Set objWkb = objXl.Workbooks.Add

Dim xlModule As Object
Set xlModule = objWkb.VBProject.VBComponents.Add(1)

' Add a macro
Dim strCode As String
strCode = _
"sub MyMacro()" & vbCr & _
" msgbox ""LOOK AT THIS"" " & vbCr & _
"end sub"
xlModule.CodeModule.AddFromString strCode

End Sub

 

[PeasNCarrots]

Let me know if it works for you.