Create VBA project in Excel 2

[RonRepp]

Hi all:

I knew how to do this once, but that was years ago. I'd like to write a VBA project in Excel from a VB6 app.

Can someone point the way?



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[TysonLPrice]

Here's a good tutorial:

http://www.vbforums.com/showthread.php?t=391665

 

[RonRepp]

Tyson:

This basically tells me how to do it within Excel. I want to create a project from a VB6 app and insert it into a running version of Excel.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[DjangMan]

I think this sort of 'injection' has been locked up somewhat in new versions of Excel.

What are you trying to accomplish?

 

[RonRepp]

I simply want to "inject" a project into a user's Excel.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[DjangMan]

Sorry - I'm not following. Are you wanting to add a/several module/s to the workbook?

 

[RonRepp]

Kind of like this; but I'm getting an error:

Dim XL as Object
Sub AddProject()
    Set XL = CreateObject("Excel.Application")
    XL.Application.VBE.VBProjects.Add
    With XL.Application.VBE.ActiveVBProject
        .BuildFileName = "testProject"
        .Filename = "C:\TestProject.bas"
        .Name = "ThisTestProject"
    End With
    
End Sub

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[RonRepp]

OK, I was missing an argument:

Sub AddProject()
    Application.VBE.VBProjects.Add vbext_pt_HostProject
    
    With Application.VBE.ActiveVBProject
        .BuildFileName = "testProject"
        '.Filename = "C:\TestProject.bas"
        .Name = "ThisTestProject"
    End With
    
End Sub

However, I get an error message that it's not trusted, even though I tried it inside Excel's VBE.

Just a thought...a bad one, I guess.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[HughLerwill]

Writing/ injecting code with code. Does this help at all?

a$ = "Private Sub Workbook_Open()" & vbCr & _
"Worksheets(1).Unprotect" & vbCr & _
"Worksheets(1).Range(""B9"").value = Replace$(Name,"".xls"","""")" & vbCr & _
"Worksheets(1).Range(""A7"").value = ""Current filename "" & Path & ""\"" & Name" & vbCr & _
"Worksheets(1).Protect" & vbCr & _
"Saved = True" & vbCr & _
"End Sub"
.ActiveWorkbook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString (a$)

My contents of a$ are just an example of something I have running(in VB6); please replace it with something which is useful to you. Watch out for those double double quotes!

To get around the 'Trusted' issue go Excel-Tools menu-Macros submenu-Security-Trusted Publishers tab-and check Trust access to Visual Basic Project.

 

[RonRepp]

Hugh:

Awesome...that's exactly what I'm looking for.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[HughLerwill]

Earlier I was a bit pushed for time. The following creates a new standard module and pops a sub into it.

Private Sub Command1_Click()

Dim objXl As Object
'no references required into Excel library or Excel Extensibility library because it's late bound
With CreateObject("Excel.Application")
With .Workbooks.Add.VBProject.VBComponents.Add(1) '1=vbext_ct_StdModule
.Name = "MyStandardModule"
.CodeModule.AddFromString "Private Sub MySub()" & vbCr & "Beep" & vbCr & "End Sub"
End With
.Visible = True
End With

End Sub

Seems to be some good further reading for you at

http://www.cpearson.com/excel/vbe.aspx

 

[RonRepp]

Hugh:

Thanks again.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[HughLerwill]

Ron:
Very pleased to help you.