Export to Excel 2003, 2007

[Trillium97]

I have a Access 2003 database running fine for a long time. Two new users
started using the database, since they are new they get Office 2007,
including Excel 2007. A routine in the Access database exports to a
formatted excel file (that is, I can't just use the DoCmd.TransferSpreadsheet
line, that's too simple). I use the reference to Microsoft Excel 11.0 Object
Library, and the two machines do have that (they have both 11 and 12 in
there). On the machines that have 2007 installed, the routine error at this
line:
ActiveCell.Value = strWorksheetName
It doesn't matter what I put in the value, it could be "here". The line
above activates the cell. It works fine every time on the machine that has
only 2003 installed, so I believe my code is OK. I just can't figure out
what I can do.

Upgrading everyone to 2007 is NOT an option. Any other suggestions? Thanks
so much.

 

[Alex Dybenko]

Hi,
try to change
ActiveCell.Value = strWorksheetName
to
oApplication.ActiveCell.Value = strWorksheetName

where oApplication - is a Excel.Application object

Firthermore - in order to have your program work both in excel 2003 and
2007 - you have to use late binding

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Trillium97]

I can try that - what did actually work for some reason was to compile the
database code on her computer (the one with 2007). I'm thinking that was the
root cause rather than the fact she has 2007. I don't quite understand why,
but another guy there said that doing that often works. Normally I do the
database changes on my own machine only, and the database is stored on the
network.

 

[Alex Dybenko]

Hi,
then you have to use late bindings

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com