Crash attempting to open Excel Add-in using .dll file comprised of VB6 code

[Sirhc M]

Hi there,

I'm getting a crash when my .dll file (comprising of VB6 6.0.9782 code)
attempts to open an Excel 2000 Add-in (.xla file) comprising of VBA
6.0.8714 code.

Here's the scenario,

1) .dll file is registered with Excel 2000
2) Double clicking on an Excel Workbook in Explorer - Excel Opens,
Workbook Opens
3) .dll file has an appevent associated with the Workbook Open event
4) .dll file triggers the opening of the Excel Add-in

Here's the line where it crashes:

gEventHandler.oXL.Workbooks.Open sXLAPath, 0

where gEventHandler = Event Handler class object
oXl = Excel.Application
sXLAPath = the entire path of the .xla's location (including the
filename.xla)

The crash is something along the lines of "instruction at 0x30033c38
referenced memory at 0x00000002. The memory could not be written"

Any ideas to this problem?
Thansk in advance.
CM

 

[Peter T]

CM,

I don't follow all the description of your setup, how and what you are
doing. However I put the following together which seems to work -

VB6 dll / project named "gEventStuff"
'start code class named "gEventHandler"

Dim WithEvents oxl As Excel.Application
Dim sXLAPath As String

Public Property Set App(x As Excel.Application)
Set oxl = x
End Property

Public Property Let sFile(s As String)
sXLAPath = s
End Property

Sub OpenMyWB()
oxl.Workbooks.Open sXLAPath, 0
End Sub
' end code class named "gEventHandler"

' start code in a normal module of an Excel WB

Public vb6Test As gEventStuff.gEventHandler

Sub auto_open()
Set vb6Test = New gEventStuff.gEventHandler

Set vb6Test.App = Excel.Application

vb6Test.sFile = "C:\Temp\Test.xla"
vb6Test.OpenMyWB
'Set vb6Test = Nothing
End Sub

Sub auto_close()
Set vb6Test = Nothing
End Sub

With no open instance of Excel, I double clicked the saved wb, and my
Test.xla opened via the vb6 dll (test.xla was not installed as an addin).
Seems an odd way of doing things but I guess you have a purpose.

Regards,
Peter T

 

[Sirhc M]

Thanks Peter,

The structure of my .dll file is pretty much the same as what you have,
the only problem is I'm crashing at the line:

Excel.Application.Workbooks.Open XLAPath, 0

With the error message as shown in the subject message.
Any ideas as to why/how I'm getting this error message when attempting
to open the .xla from the .dll?

 

[Peter T]

Not a clue I'm afraid. As I said, my simple example worked for me. From what
you originally posted one line at least is not the same in our methods -

your
gEventHandler.oXL.Workbooks.Open sXLAPath, 0
vs my
oXL.Workbooks.Open sXLAPath, 0

though I don't think that would trigger your particular error, maybe an
object ref not set at most.

How & where is your line that fails being called.
Have you tried calling your dll from a normal macro iso the wb open event
and stepping through into vb6. All ref's OK?

With ref's to your dll commented, I assume simply:
Workbooks.Open XLAPath, 0
in in your wb works OK, right ?

Regards,
Peter T

 

[Sirhc M]

Would there be any issues if:

a) The .dll VB6 file was compiled using Excel 2002,

and

b) I'm applying the .dll for use in Windows NT / Excel 2000?

 

[Peter T]

First, did the checks I suggested previously work?

If you have set a reference in your vb6 to Excel 10, there may indeed be
problems running in XL2000. I doubt the Windows version is relevant unless
you are using some API not available in earlier versions.

I assume you have access to the vb6 project in XL2K as you say you can see
where the code fails. Why not rename a backup of the project, delete any
missing ref's set the correct ref to Excel. With Excel closed, run the
project with F5, open Excel & your file and set the newly named ref to your
vb6 project. Run the code from vba and step through. If this works, rename
as original and compile. This new dll should now also work in the later
version - probably!

Presumably you fully declared oXL as Excel.Application. Depending on what
else you are doing it might be possible to remove all ref's to Excel and
declare oXL as Object.

Regards,
Peter T