HELP NEEDED ASAP PLEASE!!!!!!!!!!!!!!!!!!!!

I need to find an answer asap or i am screwed :-(

I am trying to loop through a VBA project in excel ( from VB6 ) and find out how many components there are. My ultimate goal is to search each component for a specific string.

I am using the following code which is resulting in an error

:Error 430 Class does not support automation

I have references to both word and excel object libries and VBA extensibility library.

Code:

---

Sub main()

On Error GoTo err_handle
Dim appMsExcel As New Excel.Application
Dim appMsWord As New Word.Application
Dim vbp As VBProject
Dim vbc As VBComponent

appMsExcel.Visible = True
appMsExcel.Workbooks.Open "c:\locallinktools\linktools.xls"
appMsExcel.Workbooks("linktools.xls").Activate
Set vbp = appMsExcel.ActiveWorkbook.VBProject
'I get the error here

For Each vbc In vbp.VBComponents
        MsgBox vbc.Name
Next

---

Any ideas would be great

It looks like what you are trying to do is iterate through a collection of workbooks. But you are trying to get only the ActiveWorkbook which is not a collection...so I'm not exactly sure what you're after. Anyways:

Change this:

Code:

---

Dim vbp as VBProject
Dim vbc as VBComponent

---

To:

Code:

---

'this is late bound so you won't get intellisense:
Dim vbp as object
'or try: Dim vbp as new appMsExcel.Workbook
'for early-binding

---

THEN do this to get the Active workbook:

Code:

---

Set vbp = appMsExcel.ActiveWorkbook
'I get the error here

msgbox vbp.Name

---

I'm going by memory here and it's been a while. But that should get you on the right track.

Whay i am trying to do is loop through the collection of VB components in the activeworbook VBproject.

I can loop through the currently active code panes in the workbook using the following code:



[CODE]
Sub main()
On Error GoTo err_handle
Dim vbp As VBProject
Dim vbc As Codepane
Dim appMsExcel as new excel.application

appMsExcel.Visible = True
appMsExcel.Workbooks.Open "c:\locallinktools\linktools.xls"
appMsExcel.Workbooks("linktools.xls").Activate

debug.print appMsExcel.activeworkbook.vbprojet.vbe.codepanes.count

For Each vbc In appMsExcel.activeworkbook.vbprojet.vbe.codepanes
MsgBox vbc.codemodule
Next


What i need to do is iterate through all vbcomponents in a vbproject. Please help. Its URGENT!!!!!!