Workaround for Access 2003 vs 2007 Automation of Excel

[EagleOne@microsoftdiscussiongroups]

2007/2003

THe following code works fine with Access/Excel 2007 but not in Access 2003

Sheets("Temp").Sort.SortFields.Add Key:=Range("V2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Error description "Variable not defined" for:
xlSortOnValues
xlSortNormal

The Excel 11 library is referenced.

Any Workarounds?

TIA,

EagleOOne

 

[EagleOne@microsoftdiscussiongroups]

Got some ideas from:
http://www.mjtnet.com/blog/category/success-stories/

"Named Constants

Named constants that belong to the application will also mean nothing to
Macro Scheduler/VBScript. Inside of the application they belong to they are
exposed to VBA. They mean something to VBA inside of the application. But
take the code outside of the application and the names are meaningless.

For example the code above uses three named constants: xlSortOnValues,
xlAscending and xlSortNormal. As these are declared automatically within
Excel they mean something to VBA. Outside of VBA, in VBScript/Macro Scheduler
they will cause errors because they are undeclared.

We need to declare these:

xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0

I know what you’re thinking - how do I know that xlSortOnValues equals 0,
xlAscending is 1 and xlSortNormal is 0? Well, you could look them up. But I
don’t bother doing that. I use the VBA debugger. Open up the Visual Basic
Editor and hit CTRL+G to open up the “Immediate†pane - it may already be
visible. Inside the “Immediate†pane type:"

Any other areas where Access 2007 and 2003 differ re Automation of Excel?

BTW, I am now going to test the above idea.

EagleOne

 

[sivak]

This is a good reference.

http://www.microsoft.com/technet/scriptcenter/resources/officetips/jul05/tips0719.mspx