[RESOLVED] error on oXL.Workbooks.Open

[arie ribbens]

I've made a form in VB6 and compiled an exe file. Clicking on the .exe file opens the form, clicking on the button starts excel and opens a .xls file.
The code is:


Option Explicit



Private Sub Command1_Click()

Dim oXL As Excel.Application

Dim oWB As Excel.Workbook

Dim MyPath, MyName, MyFile As String



' Start Excel and get Application object.

Set oXL = CreateObject("Excel.Application")

oXL.Visible = True



' Get file



MyName = "\MySubFolder\MyXls.xls"

MyPath = App.Path

MyFile = MyPath & MyName

oXL.Workbooks.Open MyFile

oXL.Application.Visible = True



Exit Sub

Err_Handler:

MsgBox Err.Description, vbCritical, "Error: " & Err.Number



End Sub



I get an error when excel is trying to open the .xls file (i'm sorry i can't describe the exact error, but i think it was something like a critical error in Myfile.exe caused .... in module <unknown> at....).
the error occurs when the script is excecuting oxl.workbooks.open

This happened on two different machines running on windows98 and office2000.

It works fine on my PC running on windows XP and office 2002 and on my pc at work running on windows2000 and office 2002.

I've tried different codes for the line oxl.workbooks.open like:

oXL.Workbooks.Open (FileName:=MyFile)
oXL.Workbooks.Open ("C:\MySubFolder\MyXls.xls")
set oWB = oXL.Workbooks.Open(FileName:=MyFile)
set oWB = oXL.Workbooks.Open ("C:\MySubFolder\MyXls.xls")

None worked :(

Please help!

Thanks in advance!

Cheers,
Arie

[CyberJar]

Hi,

I think the problem lies in this line:


Dim MyPath, MyName, MyFile As String


In VB you declare it as:

Dim MyPath As String, MyName As String, MyFile As String



The way you have it, only MyFile is defined.

CyberJar

[RobDog888]

If that was the issue then why does it work on some systems
and not others. Wouldn't it fail al the time?

What about oXL.Workbooks.Open ("C:\MySubFolder\MyXls.xls")
no use of MyFile at all and arie ribbens said that failed also.

What about the reference to Excel in VB. The version of Excel that
was compiled with was Excel 2002. i know that the .Open method
is present in 2000 but could be work looking at. Try late binding
for your Excel object vars.

Also was the program installed (setup package) on the other
systems or just copied over? Could be a versioning issue???

I think we need a little more info to solve the issue?

HTH

[arie ribbens]

thank you for replying.

I will post a screendump with the error message on it so you have some more detailed information.

@RobDog888: thanks for really reading my problem and your reply.
i'm still pretty new to vb so i don't really understand what you mean with:

Try late binding for your Excel object vars.

do you mean something like:
Dim oXL as Object
Dim oWB as Object
?
the program is very tiny atm. Just to code posted here. Its just one form with one button to test the code. I compiled the project to a .exe file and copied it with the subfolder including the .xls file to a floppy :). Took the floppy to the win98 machine with office 2000 and run it which results in the error.
This is the easiest way to change code and test quickly.
I used to make a setup package and install on the win98 machine but that took too much time and the error message is the same.

I'll keep you updated!

Cheers,
Arie

[RobDog888]

Yes, that is late binding.

Can you try compiling the code on a system with vb and office 2000?

HTH

[arie ribbens]

i tried the late binding, but still the same error.
I'll try to compile on the win98/office2000 machine this evening as that machine is at home.

Here's a picture with the error-message, hope this will make it clearer for you:

http://www.w-e.nl/error.jpg

Cheers,
Arie

[RobDog888]

Ok, here is what I did. The modified code below is the working
version, I think. Your code was crashing on our 2000 Terminal
server with the same sub folders/file, etc. So I did the late
binding and changed the oXL.Application.Visible = True to
oXL.Visible = True and set the objects to nothing after we are
through with them. Also, I took out the reference to Excel in the
project.

I compiled it, copied it to our 2000 Terminal server and I got the
workbook to open. I cant test on 98, but if there were issues on
2000 Terminal server then perhaps they are fixed for 98 as well.

Option Explicit

Private Sub Command1_Click()

Dim oXL As Object
Dim oWB As Object
Dim MyPath As String
Dim MyName As String
Dim MyFile As String

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Get file
MyName = "\MySubFolder\MyXls.xls"
MyPath = App.Path
MyFile = MyPath & MyName
oXL.Workbooks.Open MyFile
oXL.Visible = True
Set oWB = Nothing
Set oXL = Nothing
Exit Sub

Err_Handler:

MsgBox Err.Description, vbCritical, "Error: " & Err.Number

End Sub

HTH

[arie ribbens]

thank you sooo much RobDog888!!
I'll give it a try when i get home from work. I'll let you know if it worked.

Cheers,
Arie

[arie ribbens]

i'v tried your code RobDog888 but it didn't work. :confused: Now i get a different error on the win98/office2002 machine:
http://www.w-e.nl/error2.jpg

Which pops-up before excel can open.
I think it has something to do with the late binding.
When i replace
Dim oXL As Object
with
Dim oXL As Excel.Application

i get the first error again.

Code works fine on the XP/office2002 system..offcourse.

This is really starting to get frustrating :mad:
I mean it's just a couple of lines of code....AAarrgh.

Cheers,
Arie

[RobDog888]

Quick question, you state that the systems have Office installed,
but do they have Excel installed? Runable?

[arie ribbens]

yes i have...

[arie ribbens]

found out there was something wrong with one of the two win98/office2000 systems i was testing on. The following code worked fine on XP/officeXP, win98/office2000 and win95/office97 machines.
Thanks to everyone who helped out:


Option Explicit

Private Function Str_BuildPath(LeftPath As String, _
RightPath As String)
Dim strLeft As String
Dim strRight As String

If Right$(LeftPath, 1) = "\" Then
strLeft = LeftPath
Else
strLeft = LeftPath & "\"
End If

If Left$(RightPath, 1) = "\" Then
strRight = Right(RightPath, Len(RightPath) - 1)
Else
strRight = RightPath
End If

Str_BuildPath = strLeft & strRight
End Function


Private Sub Command1_Click()
Dim oXL As Object
Dim oWB As Object
Dim MyName As String
Dim MyFile As String


On Error Goto Catch

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

' Get file
MyName = "\MySubFolder\MyXls.xls"
MyFile = Str_BuildPath(App.Path, MyName)
' (See pvt. routine, above.)

oXL.Workbooks.Open MyFile
MsgBox "User: Please Click Ok to make Excel Close."
Exit Sub


GoTo Finally

Catch:
Dim Err_Num As Long
Dim Err_Desc As String
Err_Num = Err.Number
Err_Desc = Err.Description
Resume Finally

Finally:
On Error Resume Next
Call oWB.Close(SaveChanges:=False) '<-- ** or True **
Call oXL.Quit
Set oWB = Nothing
Set oXL = Nothing

' Report Error if we had one:
If Err_Num <> 0 Then
On Error GoTo 0
Call Err.Raise(Err_Num, "Command1_Click", Err_Desc)
End If
End Sub



Cheers,
Arie

[RobDog888]

Glad to hear you figured it out, but "win95/office97". Didn't know
95 was still alive.

:)

[arie ribbens]

Glad to hear you figured it out, but "win95/office97". Didn't know 95 was still alive.

Thought so too, but i guess some people still have old crappy systems and only win95 can run on it :D
Well i wasn't testing my program for win95, but it's good to know it works on really old systems as well.
I'm going to add some more functionality soon, so maybe i'll have to post here again (hope not).

Cheers,
Arie