Saving Excel 2007 sheet with VBA and running with Excel 2003

S

SteveB

I have a problem with object library references being changed to V12 in
Excell 2007 and causing an error when the macro runs in Excel 2003.

The sequence is:
Spreadsheet started life in Excel 2003 as a template
Load into Excel 2007 (same problem whether saved as a 2003 or 2007 template)
Create a file from the template - the VBA macro is of course attached
The macro attached to the file runs OK (behind the scenes, two references
(Tools->References) are changed, these are Microsoft Word 11.0 Object
Library and Microsoft PowerPoint V11.0 Object Library. The change is to xxxx
12.0 xxxx in both cases)

These libraries are required by the macro

Now the file created is sometimes saved in Excel 2003 format as it is used
by some people with the older system.
The problem now is Excel 2003 systems do not have the xxx 12.0 xxx versions
of the object libraries and the macro fails (even if the compatibility pack
is loaded)

A workaround is to un-check the xxx 12.0 xxx libraries and check the xxx
V11.0 xxx versions. Unfortunately the people using this are not macro
literate and I'm looking for a more elegant/automated solution
 
G

Gary''s Student

How about testing which version of Excel is running (say parse
Application.Path to see if the folder-name contains Office11 or Office12) and
then dynamically linking in the references?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Embedded Excel into Word with Tables 0
Problems starting Word from Excel VBA 3
Embed the same Excel multiple times 0
Word macro that can call cells out of Excel for a find and replace 0
Running Excel 12.0 Application on Office 2003 2
Object Libraries and Macro Campatibility 1
Open Word file with Excel VBA 2

Top