error opening excel file

[kenguru]

Hello,

I have a project which has to open an excel file. On the client machine it gives the following error:
{Project_name}has encountered a problem and needs to close. We are sorry for the inconvenience.

This happens when with a common dialog box start to browse for an excel file and then when it is clicked the excel file it gives the error.

Client is unhappy

Any suggestion?
Kenguru

 

[HughLerwill]

You had better supply the calling code from 'Project_name' so we can understand the situation more fully.

 

[kenguru]

For the open excel button:
On Error Resume Next
'if there are open workbooks they are closed
If Is_Open Then Workbooks.Close
dlg.ShowOpen
'if there is no file, exist the sub
If Err Then Exit Sub
'if there is, than we open it
Workbooks.Open dlg.FileName
Is_Open = True

where dlg is a common dialog boxe

 

[HughLerwill]

Are you writing this code in vb6 or in MS Office VBA?

If this is vb6 there must be more code above this where you set up your Excel application object.

Your references into Excel eg.

Workbooks.Close

dlg.ShowOpen

Workbooks.Open dlg.FileName

are unqualified with reference to a previously created Excel Application object ie. they have no dots preceded with an Application Object in front of them.

Above your code you should have something like;

dim ObjXl as object

Set ObjXl = CreateObject(,"Excel.Application")

then your code should be something like;

With ObjXl
On Error Resume Next
'if there are open workbooks they are closed
If Is_Open Then .Workbooks.Close
.dlg.ShowOpen
'if there is no file, exist the sub
If Err Then Exit Sub
'if there is, than we open it
.Workbooks.Open .dlg.FileName
Is_Open = True
End With
set ObjXl = Nothing

Doing what I have posted above will not necessarily solve your problems, I have not tested it, but you must always qualify your references into Excel when automating it from vb6. Note the With/ End With block and the dots in front of the Excel KeyWords.

HTH Hugh,

 

[BobRodes]

Look, ken, you've been putting up a bunch of threads(thread222-1314027, thread222-1314042 as well as this one), and I suspect they are related. I've explained the nature of the problem in detail on one of your other threads.

As well as the problem Hugh describes here, I suspect in this case you aren't including the Excel.Application files. You may well solve all of your unhappy client's problems by assimilating the information I gave you in your other thread and using someone's setup utility to make sure you deploy all of the dependent files to your client.

HTH

Bob

 

[SBerthold]

>and then when it is clicked the excel file it gives the error

Is the error happening here?:
Workbooks.Open dlg.FileName


kenguru, because you cannot ship Excel with your program using the VB 6 or Excel license, and therefore because Excel MUST be already be on the client, you need to verify versions installed, (along with the proper coding for that version).

There could be conflicts with trying to open a newer xls file with an older Excel version, or if that is not the case, there could be a problem with certain other compatibility issues (different/missing methods and property names, or binary compatibility problems).

The first thing is to find out the EXACT line of code where the error is happening, and if there are any arguments/parameters and variables used, what these equate to at run time (such as dlg.FileName)

The next things to find out are the OS versions on the client, and the the differences between the Excel version on your test machine (where it is working?), and on the client machine where it is not working.

This is of course after making sure the VB program is properly installed using an installation program as mentioned by BobRodes.

Just do NOT ship any Excel objects with your installation program. You may mess up the Excel version on the client's machine, along with possibly violating licensing agreements.

 

[kenguru]

Thanks Bob for the detailed response you gave me.

I rewrote the code in the following way:
Dim xl As Object
Set xl = CreateObject("Excel.Application")

On Error Resume Next
If Is_Open Then Workbooks.Close
dlg.ShowOpen
If Err Then Exit Sub
xl.Workbooks.Open dlg.FileName
Is_Open = True

Set xl = Nothing

The interesting thing was, that for me it works, on another computer it works, where VB never was installed, and on the client computer doesn't.
I know that it uses XP, but since than i didn't speak with him to asked him the questions you suggested.

SBerthold what did you mean by:
"Just do NOT ship any Excel objects with your installation program. You may mess up the Excel version on the client's machine, along with possibly violating licensing agreements."

I should not send him excel files?
In the program there are excel object, but i have to create them to be able to work with them.

Thank you,
Kenguru

 

[SBerthold]

>"Just do NOT ship any Excel objects with your installation program.
>I should not send him excel files?

I did say "Excel objects", (Excel Components) - The Excel Object (and it's dependecies) listed under the menu item "Project-References" or "Project-Components".

The excel Work files *.xls were not what I meant.

 

[kenguru]

Sberthold,

In the project-references i have Excel 11 object which i had to include, other ways i couldn't work whit the excel object.
You are suggesting to exclude that way i send the project to the client? Will than work for him?

Thank you for your time.

 

[SBerthold]

I said do not ship it!
You can include it in your references - but just do not actuall ship this referenced file (Excel 11 object) with your installation package, or whatever.

 

[HughLerwill]

Bert,
The OP is using late binding in his example and so a Project Ref into the Excel library may be convienient(for editing with intellisense in the vb6 IDE, and provision of xlConstants) but is not actually required.

Ken,
1) You should have an xl. infront of your Workbooks.Close
too.
I am suprised you say it works at all because I would not expect Excel to become visible unless you included a line xl.Visible=True in your code, try doing that after the CreateObject line

2) Please tell us NOW; Are you using an installer package to install your app on the clients machine or are you just copying over the exe file?

regards Hugh,

 

[SBerthold]

>The OP is using late binding in his example and so a Project Ref into the Excel library may be convienient

I realize this,(and using late binding also may help solve binary compatibility issues), and there is nothing wrong with that, but that is not what I am getting at.

 

[kenguru]

1) What does your line do?
2) I don't use an installation package, BUT I do have send the client the missing ocx file, what he copied into the correct directory(C:\Windows\System32)

Kenguru

 

[SBerthold]

What is the name of the "missing ocx"?

 

[kenguru]

The file is: comdlg32.ocx

 

[SBerthold]

And on which line is the error happening?

If it actually has to do with the comdlg32.ocx, then you have probably sendt a wrong version for that OS - hense BobRodes post.
You need to use the most recent MS Merge file, for the common dialog, in your installation package when doing this (COMDLG32.MSM).

So, then the easiest way at this point (out side of a proper error handler) to find out if the error is happening because of the comdlg32.ocx, or because of the Excel code.

Copy the Excel file to be opened into your app'S Exe folder, and remove everywhere in this piece of code where you are referencing the common dialog (dlg), and then hard code the file name into a variable, and pass the file name to the Excel code, as follows:

Dim sFilePathAndName as string

sFilePathAndName = app.path & "\TheFileName"

(Replace "TheFileName" with the actual name.)


replace "dlg.FileName" with the new variable: sFilePathAndName


If you have done every right as mentioned and still get the same error, then the problem is with your Excel code, and probably not the common dialog.

If you get a different error, then the problem is probably with your Excel code and probably the Common Dialog.

If you do not get an error at all, then the problem is probably just with the Common Dialog, and you have sent them possibly a wrong version.

 

[HughLerwill]

Ken,

<<1) What does your line do?>>
It will make the instance of Excel you have created visible onscreen.

<<2) I don't use an installation package>>
Please go to vb6 Help and Search it for 'Distributing your Applications'.

 

[HughLerwill]

Bert,
comdlg32.ocx is an ActiveX control and as far as I am aware there are not specific versions of it for different versions of Windows.

Ken,
There may however be older and newer versions of comdlg32.ocx, a proper installer will determine if the version you are deploying has a later version than one already existing on the clients machine, if it has (or the file does not exist on the target machine) then the file you are deploying gets installed, if not the existing one remains.

 

[BobRodes]

<comdlg32.ocx is an ActiveX control
comdlg32.ocx is the common dialog control.

kenguru, you need to use an installation package, rather than manually shipping dependent objects. The point is that it isn't enough to just have the .ocx file or whatever on the client machine, it also has to be registered. An installation program (setup.exe, for example) will handle this. Trust me, you do not want to be involved in manually registering stuff on your client's machine, even if you do know how to use regsvr32.exe.

SBerthold, I was unaware that Microsoft doesn't allow the use of the Excel object libraries to provide automated Excel solutions on machines that don't have Excel installed on them. However, it makes sense really. Can you point me to any doc on the subject?

Bob

 

[SBerthold]

>that Microsoft doesn't allow the use of the Excel object
>libraries to provide automated Excel solutions on machines
>that don't have Excel installed on them

Just to clarify: It is allowed to reference the components in your VB project and use them in your VB code - just not to re-distribute the referenced Excel components through the VB or Excel application license.

If the client has a licensed version of Excel on the machine, then there is no problem referencing the Excel objects in the VB app, because they will(may) work on the client, but you still cannot redistribute those Excel components (ocx, dlls, exe, etc.).
If they do not have Excel, then the VB application which references the Excel objects will not work at those referenced points.

In this situation you are not allowed to distribute the Excel components in order to get your application to work on the client - regardless if they would work or not after distribution (I think anyways that even when distributing the referenced objects, it will not work).

The only things which you are allowed to redistribute with VB are those components in the Redist.txt (See the Eula.Txt for this statement), in the VB directory, which explicitly say so, and for any other additional components not delivered with VB, but for which your license for the non-VB product allows a distribution for. (The only "Excel" file listed in the Redist.Txt is the msexcl35.dll, which is the ISAM driver used to access Excel worksheets through Jet)

While the MS Office Developers Edition or the Microsoft Visual Basic for Applications Software Development Kit may allow different distribution rights, the Excel desktop application license, installed on the VB 6 developers machine, does not allow this (See Excel Eula.Txt).

If a client wants to view Excel Worksheet or Access Reports without these licensed products on the machine, they can download the free Viewers from MS.
However, editing the Worksheet data is not possible.

So, you are allowed to redistibute only those components for which you have a specific license to redistribute, thus (with VB) those listed in the Redist.Txt referred to in the Eula.Txt in your MS VB 6 folder.

The VB 6 license does not allow this for Excel components, which are not even delivered in the VB 6 product or license or listed in the VB Eula.Txt, and the desktop Excel application license possibly installed on the VB developers machine does not allow this distribution either.

Because you would be then redistributing Excel components installed under the Excel Eula, and not the VB Eula, you would need to check the Excel Eula for any possible distribution rights.
This holds true for ANY product components not delivered with VB and thus not listed for redistribution in the Redtist.txt - you need the redistribution rights of the additional product, whether Excel, Word, Access, or xyz.exe

(Sorry if I repeated myself several times here)