Adding module to Excel from VB6

[nath]

Hello there, hopre you are having a nice day!
Here is my problem: I am in VB6. I create an instance of Excel and a new workbook (or open an existing one). I would like to add a module to the new workbook but it gives me an error message:

"Run-Time error '430' Class does not support Automation or does not support expected interface"

The funny thing is that if I open an existing file which holds a module, I can access the module, read its content and modify it. So I do have a kind of connection to my Excel instance through the Extensibility model, but it seems that the things I can do are limited.

Any one has any idea? For further precision: I have added the references to both the"Excel Object Library" and the"Visual basic for Application Extensibility".

My code looks something like

Sub TestAddModule
    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    
    Set xlapp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("C:\Test.xls")
    
    'I get the error message when executing the next line
    wb.VBProject.VBComponents.Add vbext_ct_StdModule
    

    'etc
End Sub
[\CODE]

Any idea most welcome,

NATH

 

[MichaelRed]

The following shows ONE way to do this. The second procedure is just to construct the "string" to use as the new module.

Sub Export_Excel_VBA(qualifier As String)
    Dim ExApp As Excel.Application
    Dim x As String

    Set ExApp = New Excel.Application
    ChDir "C:\My Documents\"

    x = basString4Excel

    With ExApp
        .Workbooks.Open FileName:=qualifier
        .ActiveWorkbook.VBProject.Name = _
        "Exported_VBA_" & Chr(34) & Left(qualifier, Len(qualifier) - 4) & Chr(34)
        .VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
        .VBE.CodePanes(1).CodeModule.AddFromString x
        .ActiveWorkbook.Save
        .Quit
    End With


End Sub

Public Function basString4Excel() As String

    Dim strX(26) As String
    Dim x As String
    Dim Idx As Integer

    strX(0) = "Public X As String"
    strX(1) = "Sub auto_open()"
    strX(2) = "on error resume next"
    strX(3) = "Call msg"
    strX(4) = "If X = ""Y"" Then"
    strX(5) = "    For i = 3 To 24"
    strX(6) = "        Application.VBE.SelectedVBComponent.CodeModule.DeleteLines 3"
    strX(7) = "    Next i"
    strX(8) = "End If"
    strX(9) = "End Sub"
    strX(10) = ""

    strX(11) = "Sub msg()"
    strX(12) = "    If ActiveSheet.Name = ""Qry_Data_Export"" Then"
    strX(13) = "        a = ""Welcome to the DBS Export File."""
    strX(14) = "        a = a & ""Please feel free to make any updates you wish;"""
    strX(15) = "        a = a & ""however, please do not..."""
    strX(16) = "        a = a & ""1. Delete Row 1"""
    strX(17) = "        a = a & ""2. Alter the Rec_# field (Column A)"""
    strX(18) = "        a = a & ""If you wish to mark a record for deletion,"""
    strX(19) = "        a = a & ""please place a 'XX' in the Rec_Type field (Column C)."""
    strX(20) = "        a = a & ""Do you wish to remove this message from this file?"""
    strX(21) = "        If MsgBox(a, vbYesNo, ""DBS - Import / Export File"") = 6 Then "

    strX(22) = "           X = Y"
    strX(23) = "        End If"
    strX(24) = "    End If"
    strX(25) = "End Sub"
    strX(26) = ""

    For Idx = 1 To 10
        x = x & strX(Idx) & vbCrLf
    Next Idx

    x = x & vbCrLf

    For Idx = 11 To UBound(strX)
        x = x & strX(Idx) & vbCrLf
    Next Idx

    basString4Excel = x

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[nath]

Thanks for your message, however it doesn't solve my problem. I can add procedures if a module exists already, but my problem is to actually insert a module to begin with. In your code it even starts complaining when I try to set the name of the project ...

 

[MichaelRed]

Do you have the excel library in your references?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places