Automation Error (430)

[Brent McLaren]

Hi,

I am trying to search for some text in the VBA code of Excel 97 files
from within VB6.0 using the code below. I copied most of the code
from an MSDN article but i keep getting an Automation Error (430). I
know other's have had the same problem in this group but i have not
been able to find a solution.

I have tried the code using VB6.0 and from within Excel 97 itself and
i get the same error. I have also tried referencing both "Microsoft
Visual Basic for Applications Extinsibility" and "Microsoft Visual
Basic for Applications Extinsibility 5.3". I am also referencing
"Microsoft Excel 8.0 Object Library".

If anyone has a solution to my problem your help would be greatly
appreciated.

Thanks,
Brent


Private Sub Command1_Click()
'Declare variables to access the Excel workbook
Dim objXLApp As Excel.Application
Dim objXLWorkbooks As Excel.Workbooks
Dim objXLABC As Excel.Workbook

'Declare variables to access the macros in the workbook
Dim objProject As VBIDE.VBProject
Dim objComponent As VBIDE.VBComponent
Dim objCode As VBIDE.CodeModule

'Declare other miscellaneous variables
Dim arrXLFileNames As Variant
Dim varFileName As Variant
Dim strFileName As String

'Empty the list box
List1.Clear

'Open Excel, and open the workbook
Set objXLApp = New Excel.Application
Set objXLWorkbooks = objXLApp.Workbooks

arrXLFileNames = Array("ImportReport97.xls", "DJEnergyIndex1.xls")

For Each varFileName In arrXLFileNames

strFileName = "\Brents Work\xlFiles\" & varFileName
Set objXLABC = objXLWorkbooks.Open(strFileName)

'Get the project details in the workbook
Set objProject = objXLABC.VBProject

'Iterate through each component in the project
'---------------------------------------------
'NEXT LINE CAUSES ERROR 430
'---------------------------------------------
For Each objComponent In objProject.VBComponents

'Find the code module for the project
Set objCode = objComponent.CodeModule

'Scan through the code module, connection string
If objCode.Find("Provider=""MSDORA""", 1, 1, -1, -1, True,
False, False) Then
List1.AddItem objXLABC.FullName
Exit For
End If
Next
Next

End Sub

 

[Jim Cone]

Brent,

Are you sure the files are being opened?
It appears that the file path you are using is incomplete.
It should look something like this...
strFileName = "C:\Documents and Settings\UserABC\My Documents\ _
Brents Work\xlFiles\" & varFileName

Also the file path is usually not necessary, just the file name, so
the following should also work...
Set objXLABC = objXLWorkbooks.Open(varFileName)

If the file contains links then an additional argument is necessary...
Set objXLABC = objXLWorkbooks.Open(varFileName, UpdateLinks:=False)

Of course, it may be some other issue.

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)

'*************************************

 

[Brent McLaren]

Hi Jim,

Thanks for your reply. Unfortunately, I have tried both of your
suggestions and it has not resolved my problem. I'm pretty sure the
file is being opened properly. If i put in an incorrect path i get a
run-time error '1004' ("File could not be found"), so i assume that
the workbook is being opened properly when i put in the correct path
and do not get this error.

Also, the line after the Open statement seems to work ok (Set
objProject = objXLABC.VBProject) and i would imagine this would not be
the case if objXLABC had not been properly set to a workbook object.

Any further suggestions would be appreciated.
Thanks,
Brent