Adding a macro to Excel from VB

Posted by Antonio Suárez Pozuelo on April 06, 2001 2:20 AM

Hi, there:

I've developed a web application that uses a reporting component to generate Excel documents when requested by clients. The component is invoked from a dll written in VB. Now I need to add some macros to the generated document prior to download; the reporting tool doesn't feature that, but I've been told that it can be done via 'OLE automation'. I'm not quite sure what's that. I'm trying the Microsoft Excel 8.0 object library, but can't find any suitable objects, methods or properties for that. Any ideas? It's important for me, so I'd appreciate your help very much. Thank you in advance.

Antonio

Posted by Dave Hawley on April 06, 2001 4:35 AM

Hi Antonio

OLE Automation is certainly not my area, but I'm confident there is the information out there somewhere. Take a browse through this Google search result and narrow it down.

http://www.google.com/search?sourceid=navclient&q=OLE+automation+Excel

Hope it helps and good luck with your web app.

Dave

OzGrid Business Applications

Posted by Dax on April 08, 2001 4:51 AM

Hi,

The library that you need is the Visual Basic for Applications Extensibility which includes properties and methods for adding VBA components (i.e. forms and code modules) and then manipulating them from another application. There is a small amount of information in the help and probably enough to do what you need. Here is an example of creating a new workbook, adding a module and then adding some code using the AddFromString method. The second example demonstrates the use of the InsertLines method. You'll need to set references to Excel and to VBA Extensibility in order for this code to work.

Regards,
Dax.

Dim xl As Excel.Application
Dim Wb As Excel.Workbook
Dim ModuleComponent As VBComponent

Private Sub AddingFromString()
Dim sProcBody As String

Set xl = New Excel.Application
Set Wb = xl.Workbooks.Add
Wb.SaveAs "Answer.xls"
Set ModuleComponent = Wb.VBProject.VBComponents.Add(vbext_ct_StdModule)

ModuleComponent.Name = "AddedCodeModule"

sProcBody = "Sub TestMacro()" & vbCrLf & "ActiveWorkbook.Sheets.Add" & vbCrLf
sProcBody = sProcBody & "ActiveSheet.Name=" & Chr(34) & "Test Sheet" & Chr(34) & vbCrLf
sProcBody = sProcBody & "MsgBox " & Chr(34) & "New Sheet Added" & Chr(34) & vbCrLf
sProcBody = sProcBody & "End Sub"

ModuleComponent.CodeModule.AddFromString sProcBody
xl.Visible = True
End Sub

Private Sub InsertingALine()
Dim AnotherLine As String

AnotherLine = "ActiveSheet.Cells(1,1).Value=" & Chr(34) & "Insert Lines in action..." & Chr(34)
ModuleComponent.CodeModule.InsertLines 3, AnotherLine

End Sub

Posted by Antonio Suárez Pozuelo on April 09, 2001 5:11 AM

Right, the 'Microsoft Excel Object Library' and the 'Microsoft Visual Basic for Applications Extensibility' object library are actually the way to go. Thanks.