Change Excel Versions Objects Reference in VBA frob VBA

[JW]

We have a macro in project that will populate an excel template that computes
our earned value charts. We are now experiencing a proplem when an
individual with excel 2007 on their machine opens the project and creates the
excel sheet, and then closes and saves the project it retains the the new
excel 2007 reference and not excel 2003. When another individual tries to
open the project that has an excel 2003 it locks the project and will not
allow you to delete/change the missing reference. Can we initially set the
reference in the old excel reference let it update to the new refernence if
the individual has 2007 and then reset the reference or remove it on saving
and closing project when we execute the macro

 

[Rod Gill]

If the template references Excel 2003 then that should not change. Make sure
your template is a .xlt file.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[JW]

Rod

I had hope it would be that simple. My Macro resides in Project and I open
excell and paste the data in Excel. It works fine in both versions. But
when they save out on a machine with Office 2007 on it changes the VBA
refernces to An Excel 12 Object. When It opens on a 2003 machine it shows
the excel 12 object as misiing. When you try to remove it and load excel
object 11 it states that it cannot load the DLL file. The only way tothat I
have found currently is to reopen it on a 2007 machine and remove the
reference totally and then reopen on the older machine and reset the
reference. Any ideals?

Thank You

 

[Rod Gill]

It works for me unchecking Excel 12 then checking Excel 11. If that does not
work for you, your workaround may be necessary unless you want to uninstall
then re-install (don't forget the SPs!)

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[JW]

I do not have that option as the machines are utilizing a corporate image.
I was hoping to do the same thing from within VBA on Project open and close
events.

Thank You for your assistance

 

[joe billo]

I have a related problem. I am writing an Excel 2007 VBA procedure that gets information from Excel 2003. When I look at the list of references in the VBA Tools menu, I see Microsoft Excel 12.0 Object Library but not Excel 11.0. I did a lot of messing around and the procedure worked (twice! in about 1000 failures), so it's possible. But how can I load the Excel 11.0 Object library? When I use Browse in the VBA references dialog box (it seems I should follow the path C\Program Files\Microsoft Office\Office 11\Excel.exe), but when I do, nothing gets added to the list.



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[joe billo]

I have a related problem. I am writing an Excel 2007 VBA procedure that gets information from Excel 2003. When I look at the list of references in the VBA Tools menu, I see Microsoft Excel 12.0 Object Library but not Excel 11.0. I did a lot of messing around and the procedure worked (twice! in about 1000 failures), so it's possible. But how can I load the Excel 11.0 Object library? When I use Browse in the VBA references dialog box (it seems I should follow the path C\Program Files\Microsoft Office\Office 11\Excel.exe), but when I do, nothing gets added to the list.

-- Joe



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[John]

I have a related problem. I am writing an Excel 2007 VBA procedure that gets
information from Excel 2003. When I look at the list of references in the
VBA Tools menu, I see Microsoft Excel 12.0 Object Library but not Excel 11.0.
I did a lot of messing around and the procedure worked (twice! in about 1000
failures), so it's possible. But how can I load the Excel 11.0 Object
library? When I use Browse in the VBA references dialog box (it seems I
should follow the path C\Program Files\Microsoft Office\Office 11\Excel.exe),
but when I do, nothing gets added to the list.



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

Joe,
This is a developer newsgroup dedicated to questions/issues with
Microsoft Project, a planning and scheduling application. I suggest you
post to an Excel developer newsgroup.

John
Project MVP