You're right I didn't give much of an expalnation so here goes:
I've made a userform for putting data into our standard fax template - name
of the company and person you're sending the fax to, the number, and the
subject.
It also gets the consecutive reference number from Excel. A button calls up
a function that opens an Excel workbook, goes to the end of column one and
copies the formula to the cell below starting a new row. The value of this
new cell is saved as a string. Then the info already put in the Word form is
copied into other cells in that row, the workbook saved and Excel closed. A
final button on the form puts the various data collected into bookmarks on
the fax.
It all worked fine on my office 2003 but the binding with Excel didn't work
on the office 2000 machines once I updated the shared template on the server.
I've developed this form gradually one piece of the puzzle at a time over a
period of 2 years. Previously the button just had a hyperlink to open the
Excel workbook and the user had to put in the data manually, remember the
reference number and go back to Word. The automated link to Excel was the
latest step and the only new element plus the rest of the form still worked
it was only that button that would close the form.
The sub contains a series of Excel-only commands like End(xlUp) and
ActiveCell.Offset so if the binding to the Excel library hadn't worked, an
error message would appear and the form would be closed.
I'd read a load of posts here and various other sites about getting Word and
Excel to collaborate and the only certain solution opinion seems to be to
compile the project under office 2000 with a reference in Word VB to that
Excel library (version 9) instead of the 2003 edition (version 11). So in the
end that's what I did. It took about 30 seconds and now works on all the
machines - no rewriting just a click in the reference checkbox! The only
problem could be if in future developments I use any 2003 specific functions
that don't have 2000 equivalents - by chance this first time I hadn't, so I
was lucky.
If anyone else finds this post and they're trying to get different versions
of Office applications to exchange data, I can only repeat the advice I found
(but stubbornly kept trying to work around because I thought it would be hard
work) - Office is nearly always backward compatible so develop using the
oldest version that's still in use in the company.
(Hopefully Office 2007 will do the same but I don't plan getting it before
the Summer - I'll leave the teething pains to other people thankyou)
Thanks for your offers of assistance but I was probably trying to do
something unnecessarily convoluted when there was a simple solution.
David
