open Excel 2003 app from Excel 2010 macro

[Alli]

Hello
Because Excel 2010 can't save DBF format files, I though I could work around that by opening from the working file's macro an instance of Excel 2003. I more-or-less seem to have done that using:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application.11")
'''
xlApp.Workbooks.Open Filename:=FileString, UpdateLinks:=0
Set PESdbf = xlApp.ActiveWorkbook
etc
''
However, once I need to start writing to the file, I can't seem to find a way to make it the active workbook. (I have, of course tried PESdbf.activate)

Has anyone any suggestions?

Thank you

 

[Derek Brown]

I have not tried it but I would expect there to be two different 'instances' of Excel and that it would not be possible do work with them both from the same code (I could be wrong though).
The following may be of interest:
http://www.dbforums.com/microsoft-excel/1635801-excel-2007-export-dbf-howto.html

 

[RoryA]

FYI, CreateObject simply uses the default version of Excel regardless of what you specify in the version number. The only guaranteed way to get the version you want is to Shell it. For example, on my PC at work:

?createobject("Excel.Application.11").version

returns 14.0

 

[Alli]

Thank you Derek and Rorya-you've clarified what I was beginning to suspect. The strange thing was that the Excel icon that popped up when I opened the dbf file in the new 'instance' of excel looked like the 2003 icon, but was in fact, as you point out Rorya, 2010 anyway.
I worked out a way I could write to that file (in the other 'instance' of excel that was 2010 not 2003 as I'd wanted) and also changed the named range for the database to include the new columns, but because it was still 2010 I couldn't save it as .dbf anyway. Grr.
Thanks for the link Derek, I will have a look and report back if there is anything useful.
Regards

 

[Alli]

Hello there

This links provides a free add-in that seems to do the trick. You can't have another excel file open when you use it.

http://www.dbforums.com/microsoft-excel/1644233-excel-2007-add-save-dbf.html

thanks for the help