[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:



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. Now i get a different error on the win98/office2002 machine:


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
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
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