Excel and Word VBA over multible versions

[cllpmc]

Hi all,
I need some help.
Problem: I have written a code in a Excel97 worksheet, which calls Word97. This have been working for half a year on daily use. Now some users have new versions installed: Excel 2002 and Word 2002. When used from Excel 2002 (works out fine) and properly saved (part of the code) the reference to the Word Library is changed from 8.0 to 10.0 (the other referenced libraries do not change), and after that it won't work from Excel97.
I have read a lot about late binding, early binding etc. and have changed my code to :

Dim wrd As Object
Set wrd = CreateObject("Word.Application.8&quot

but it dos'nt work.

Question: How can I force the code to remain on 8.0 Library??
I have following Libraries checked:
Visual Basic For Applications
Microsoft Excel 8.0 Object Library
Microsoft Word 8.0 Object Library
OLE Automation
Microsoft Forms 2.0 Object Library
Microsoft Office 8.0 Object Library

Hoping that som of you outthere can HELP.

Best regards
Claus

 

[RobBroekhuis]

In your CreateObject statement you don't need to specify the version number. Then you can use late binding to refer to all of the Word objects. It's a pain in VBE, because you don't get confirmation whether the properties and methods you're trying to use are correct - and the associated constants (e.g. wdLine) are not defined. But it will work, as long as you refer only to properties and methods common to both versions of Word.
Rob

 

[cllpmc]

Thanks Rob,
but it does'nt matter if I specify version number or not!
The code is rather simple, using .Documents.Add, .Selection.GoTo, .Selection.InsertAfter, .ActiveDocument.Printout and .ActiveDocument.Close.
Could you give an examplecode that from Excel opens a Word doc, print it and close it, and saves the Excel workbook. And that will work from 97 to 2002 and viseversa ??
I would be very thankfull.

Best regards
Claus

 

[RobBroekhuis]

This example works for me:

Sub trywordlatebinding()
Dim wapp As Object
Set wapp = CreateObject("word.application&quot
wapp.documents.Add
wapp.Selection.typetext "Hello"
wapp.PrintOut
wapp.activedocument.Saved = True
wapp.Quit
Set wapp = Nothing
End Sub

What is the code that's not working for you, and what errors or symptoms are you getting?
Rob

 

[cllpmc]

Hi Rob,
Great, this works for me too, and I notised that no reference to Word 8.0 Library is needed. I've unchecked that Library in my code and it seems to work out fine. I must make some further testing tomorrow.
A problem is still there: Even though I save my Excel workbook in FileFormat xlExcel9795 from 2002 I get a warning in 97 that the file was saved in a new version:

' If saved from 2002
If Excel.Application.Version = "10.0" Then
ActiveWorkbook.SaveAs FileFormat:=xlExcel9795
Else
' If saved from 97
ActiveWorkbook.Save
End If

Can you help me out of that too?

Best regards and thanks
Claus

 

[RobBroekhuis]

I think you may need to set application.displayalerts=false when you open the file in XL97, just to avoid the warning. Make sure to set it back to true right afterwards.
Rob

 

[cllpmc]

It was a good try, BUT as the default for the warning is not to save, I won't get my file saved - and thats no good!

(The full text of the warning:
"(i) Microsoft Excel
This file is created in a newer version of Microsoft Excel. If you save the file from Microsoft Excel 97, you risk to loose data, created with functions in the new version.
- If you want to keep the datas in the original file, you should click No and use the command Save As (File menu) to save this file with a new name.
- Click Yes, if you want to continue with Save.
Do you want to continue with the function Save?
.Yes .No (default)&quot.

- Also the SaveAS override warning have default no!

Claus

 

[RobBroekhuis]

I can't test this (I have only one version of Excel), but it strikes me as VERY odd that the .save method would NOT work if called when application.displayalerts=true, regardless of what the standard response to the alert box is. Have you actually tested this and confirmed that the save does not occur?
Rob

 

[cllpmc]

If Application.DisplayAlerts=True (as it is by default without setting it) it is possible to choose No in the alert box and this will cause no saving (tested). When closing the workbook I get another chance (alert box) where I can choose No, causing no saving (tested). It does'nt seems odd to me - or am I missing something?
What I want is to ensure that when a user have made changes it will be saved, without confusing him with alertboxes (some users panic for less).
Maybe I can use the .Close True in combination with DisplayAlerts?

Claus

 

[RobBroekhuis]

I guess this alert message is an exception to the usual case where the default action is the desired action (for example, in deleting a worksheet, the default button is OK). That does pose you with an interesting problem. I wish I could help, but without being able to reproduce the situation, I can't. Good luck finding a solution
Rob

 

[cllpmc]

Exactely - but thanks anyway for your great help!!

Claus