How to make 2007 talk to 2003

[jitblass]

I have a very unique situation here...

I have a utility at work that generates a spreadsheet. It is an excel 2003 spreadsheet, which is "Book1" and unsaved. The user needs to run my code to grab some data from this spreadsheet and not have to save the excel 2003 workbook to their machine.

I have an excel 2007 workbook which runs VBA code to grab the data it needs from the generated workbook. The issue is, if the generated workbook is not saved and opened in 2007, I can't move to that as the active workbook. If I target Workbook("Book1").Activate or Window("Book1"). Activate it does not see that the 2003 version "Book1" exists.

Is there a way to tell excel "I'm about to go look into a 2003 version workbook"? Having the user save the workbook and reopen in 2007 is not an option, so I'm really limited to finding a way to pull data from a 2003 version of excel from vba code written in the 2007 version workbook. Both files are open when I need this code to run.

Is there some way I could use the GetExcelVersion and then use this information to have the ability to see that excel 2003 workbook? It's as if my VBA code written in 2007 doesn't see any workbooks open that are not 2007 versions. It only realizes the 2007 versions as open workbooks

Thoughts? Really stuck in a dead end here and need a solution. There has to be a way.

Thanks in advance

 

[Andrew Poulsom]

Welcome to MrExcel.

Did you try?

Workbooks("Book1.xls").Activate

 

[jitblass]

I did. It's not saved, however. It isn't "Book1.xls" until it is saved.

But even if I do ".xls" it does not work because 2007 is not seeing the open 2003 versions as current running workbooks.

Any other thoughts?

Thanks for the help, experts!

 

[Andrew Poulsom]

You said that "both files are open". How are you opening the Excel 2003 workbook?

 

[jitblass]

The excel 2003 program itself is running off of a company server. So the actual program titled "Microsoft Excel 2003" is not a program installed on my C drive.

We have an old utility based off Access, and it generates a spreadsheet for you. It does this using excel 2003 off of the server(as I mentioned in the above paragraph of this reply.)

Does that help/make any sense whatsoever?

Thanks!

 

[Andrew Poulsom]

I don't think that's relevant. If both workbooks are open in a single instance of Excel 2007 you shouldn't have a problem.

 

[jitblass]

Sorry, I guess I'm not making myself clear.

Both workbooks ARE NOT open in a single instance of 2007. One is open in 2007, and the other is open in 2003. This is why I need to be able to "cross talk".

Any advice?

 

[jitblass]

any other ideas?

 

[DariaL]

No brilliant ideas, but I'll be interested to see if any turn up. I have very similar problems at my workplace.

 

[Andrew Poulsom]

It sounds like you have 2 instances of Excel running, one Excel 2003 and the other Excel 2007. Why can't your users open your Excel 2007 workbook in Excel 2003? Alternatively why can't your utility open the workbook it creates in Excel 2007?