Version of Ms Excel - using excel programming via vb 6

[Guest]

Hello everybody,

I formatted my notebook because it had problems.
Before I had Win2000 and Office 2000, now I have Win XP and Office 2003.

The problem is that I have various projects in which I used following VB 6
code to open dinamically Excel and write in.

"..
'I try open excel
Set XlsApp = GetObject(, "Excel.Application") 'look for a running copy
of Excel

If Err.Number <> 0 Then 'If Excel is not running then
Set XlsApp = CreateObject("Excel.Application") 'run it
End If
Err.Clear ' Clear Err object in case error occurred.

On Error GoTo Error_Excel_Mng

...."

I have seen that recompiling (make Exe) and releasing the software on
customer's pc having Win 2000 and especially Office 2000 my program no more
work correctly.
I think because the vb reference is 'Microsoft Excel 11.0 Object Library'.

Our helpline says that I can't install Office 2000 after Office 2003.

I ask you which is the better solution that I can use.
I prefer not to return to have my laptop with previous versions vecchie or
to need another old pc.

Thank you very much.

Andy

 

[Guest]

You seem to have mixed 'late' and 'early' binding.
1. In VB remove the regerence to the Microsoft Excel ? Library.
2. Replace any named constants (such as XLR1C1 etc) by their corresponding
values. You can get the values from within Excel: Alt + F11, open the
Immediate window & type ?xlR1C1 to see what the value is etc.
3. Recompile your VB exe.

Does it compile?

 

[Bob Phillips]

Just change

Dim XlsApp As Excel.Application

to

Dim XlsApp As Object

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

By the way, the problem is probably caused by the fact that Excel 2003 has a
different version of the object library, and your code references the old
version.

Moving to late binding, as I suggest, overcomes this sort of problem.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,

I tried to declare as Object as advised by another friend.
I don't understand if I also have to the reference to the Microsoft Excel 11
Library as AA2e72E suggest or it is sufficient to change only the
declaration.

Thank you

 

[Guest]

Thank you for your reply.

1)
I don't know if these settings must applied in addition to the one written
by Bob Phillips in another post to my question.

What do you think?

2)
Do you say to replace, for example

With XlsApp.Selection.Borders(xlEdgeBottom)

with

With XlsApp.Selection.Borders(9)

where 9 is obtained by Immediate window of Excel?

Andy

 

[Bob Phillips]

Andy,

If you change all of the object

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Andy,

Try again...

You have 2 choices really. Either update the reference to the latest Excel
library (VBE Tools>references), or go late binding wholesale (not, it's all
or nothing).

To go late binding, you need to change all object variables to an object
type (Excel.Application, Worksheet, Workbook, Range etc.), but not other
types (string, long, etc.). You will also need to remove any reference to
Excel constants (such as vbCrLf, xlRed, etc.) and replace these by their
numeric equivalent. If you do this, the code should then work with Excel
2000, Excel 2003, et al.

It would be best to get the Excel constant values in Excel by typing
?constant_name in the immediate window.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Sorry but I don't understand.

Do I have to remove also the reference and replacing the variables with
constants?
For example :
With XlsApp.Selection.Borders(xlEdgeBottom) -> With
XlsApp.Selection.Borders(9)

Is it better or not?

Thank you

 

[Guest]

Sorry again but I don't understand if your first post is alternative to the
one of AA2e72E.
Or one or the other or is it better applying each one for having the
better-secure solution?

 

[Bob Phillips]

Yes, you do need to replace the Excel constants with the actual values.

As to better or not, that depends. Late binding is slower, as the type
library is accessed each time via the registry, but it can be more flexible.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Andy,
'Early' binding: if you check in a reference with Project + References
in VB, you are binding a specific version of it to your code. Should you
compile the code & try to run it on another PC, the EXE will look for an
exact match of the reference. In your code you would use Dim obj as new
Project.Class
'Late' binding: you do not use Project + Reference. Insead you use
set obj = CreateObject("Project.Class")
For example, set xl = CreateObject("Excel.Application"). When you move your
EXE to anothe PC, the EXE looks for the presence of a (compatible) version of
Excel.Appplication. This removes the problem that arises when diferent
versions are in use. This is NOT full proof: for example, if you develop on
Excel 2003 and use Excel 2000 on another PC, you might have used Excel2003
features absent in Excel2000: this will cause problems.

Is 'Early' better than 'late': depends on the definition of 'better'. 'Early
is supposed to be faster than 'late' but for Excel automation, I do not think
that you will notice any difference. The real difference is that 'Early'
binding enables Intellisence to pickup the syntax of methods of the
automation object and you can refer to constants by name. With 'Late'
binding', Intellisense does not work & constants have to be enumerated i.e
cannot be referred to by name.

As I suggested: remove the reference, resolve the constants & your EXE
should work unless you are using 'exclusive' features/properties of Excel on
the development PC.

 

[Jon Peltier]

Andy -

A word to the wise. If your customers are using Office 2000, you should be
developing in Office 2000. If you have a reference to an older object model and the
project is opened on a newer version, the newer version will be used instead. The
reverse is not true: your 2003 reference was not compatible with the older client's
2000 version.

It's possible, though a little flaky, to run multiple Office installations on one
machine. Things will work better if you install the oldest version first, and work
your way forward.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______