developingprogrammer
asked on
prevent compilation of specific lines of code
hey guys, i've got this code in my Access project
some computers have Microsoft Office 2003 and some have 2007. that's why i would like to have have this switch in my code. however because the Excel Object Library reference in my Access project at any one time is either Excel 2003 or Excel 2007, when i try and compile my project or run it, it won't work - rightly so. "Compile error: Method or data member no found"
Question: how can i get the compiler to not compile these lines of code and just run it in runtime?
Homework: i understand i can develop in early binding and change to late binding before deployment as per http://stackoverflow.com/questions/9491314/preventing-excel-vba-compile-errors-due-to-users-having-an-older-version-of-ms-o but i'm wondering if i can just skip those lines of compilation with a # key or something like that - some compiler specific language / signals. thanks guys!! = ))
Select Case xlsApp.Version
Case "11.0" 'excel 2003
xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & xlsWst.Range("A65000").End(xlUp).Row).Select
xlsApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Case "12.0" 'excel 2007
xlsWst.Sort.SortFields.Clear
xlsWst.Sort.SortFields.Add Key:=xlsWst.Range("A2:A" & xlsWst.Range("A65000").End(xlUp).Row), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With xlsWst.Sort
.SetRange xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & _
xlsWst.Range("A65000").End(xlUp).Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Select
some computers have Microsoft Office 2003 and some have 2007. that's why i would like to have have this switch in my code. however because the Excel Object Library reference in my Access project at any one time is either Excel 2003 or Excel 2007, when i try and compile my project or run it, it won't work - rightly so. "Compile error: Method or data member no found"
Question: how can i get the compiler to not compile these lines of code and just run it in runtime?
Homework: i understand i can develop in early binding and change to late binding before deployment as per http://stackoverflow.com/questions/9491314/preventing-excel-vba-compile-errors-due-to-users-having-an-older-version-of-ms-o but i'm wondering if i can just skip those lines of compilation with a # key or something like that - some compiler specific language / signals. thanks guys!! = ))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i took what Pat taught me and modified it for my situation
ASKER
i did quite a bit of reading and i think my situation may be a bit unique, or perhaps i am just not seeing it at the moment, could you help me out?
1) i am in Access.
2) i open Excel through code.
3) i would like to find out which version of Excel i'm opening. i do this through Applicatoin.Version
4a) if it's Excel 2003, i'll compile the Excel 2003 code.
4b) if it's Excel 2007, i'll compile the Excel 2007 code.
the problem come when i've got both Microsoft Office 2003 and 2007 on my computer.
1) i'm using Access 2007 to open my mdb.
2) my mdb's Excel reference is Excel 2003 --> because by compiling with Excel 2003 reference, it will work on both Excel 2003 and 2007
3) i want to use conditional compilation
4) the problem is that conditional compilation requires a compiler constant
i don't want to set the compiler constant manually. i would like to set it at run time based on a condition. something along the lines of
Open in new window
if i can strip away the compiler constant even better (i've tried and tested but can't achieve that)
Question 1: how can set my compiler constant based on a condition?
Question 2: is it possible to use conditional compilation without a compiler constant? something like the below
Open in new window
Open in new window
thanks in advance Pat and everyone who helps! = )