Need Office Version Constant

[Jamie Carper]

Due to version differences I need to run some compiler directives that based
on the Office version instantiate the proper objects. (See example below)

Application.Version is useless for this purpose because compiler directives
will only accept a constant. I need a constant that either directly or
indirectly informs me of the Officer version I am currently running in. Is
there such an animal?

e.g.

#IF OfficeVersion > 9 then
Dim MyFilelDialog as FileDialog
Set MyFileDialog = Application.Dialogs(msoFileDialogFolderPicker)
#ELSE
Dim MyFolderDialogas FileDialog2000
Set MyFolderDialog= New FileDialog2000
#END IF

MyFolderDialog.InitialFileName = "C:\TestPage.xls"
MyFolderDialog.Title = "My Personal Folder Browser"
If MyFolderDialog.Show Then
MsgBox "Folder selected: " & MyFolderDialog.InitialFileName
End If

 

[Jamie Carper]

e.g.

#IF OfficeVersion > 9 then
Dim MyFolderDialog as FileDialog
Set MyFolderDialog = Application.Dialogs(msoFileDialogFolderPicker)
#ELSE
Dim MyFolderDialog as FileDialog2000
Set MyFolderDialog= New FileDialog2000
#END IF

MyFolderDialog.InitialFileName = "C:\TestPage.xls"
MyFolderDialog.Title = "My Personal Folder Browser"
If MyFolderDialog.Show Then
MsgBox "Folder selected: " & MyFolderDialog.InitialFileName
End If

 

[Jamie Carper]

I came up with a viable solution for now:

The compiler directives were of no use to me. Instead I used a late binding
approach to the declaration. And then renamed my class object to a unique
name "FileDialog2000". Then instantiated the object using either
my class or the Office class depending on the version found.

Dim MyFolderDialog as Object

If Application.Version <= 9 then
Set MyFolderDialog = New FileDialog2000
Else
Set MyFolderDialog = Application.FileDialog(msoFileDialogFolderPicker)
End If

MyFolderDialog.InitialFileName = "C:\TestPage.xls"
MyFolderDialog.Title = "My Personal Folder Browser"
If MyFolderDialog.Show Then
MsgBox "Folder selected: " & MyFolderDialog.InitialFileName
End If

 

[Tushar Mehta]

You are close but even that won't work with say XL97 because
msoFileDialogFolderPicker is not defined.

What you need to do is isolate all version specific code in a module
that will never be compiled in an older version of the program.

Create a module, say mod2002OrLater, and put the following in it:

Option Explicit

Function FileDialog2002() As Object
Set FileDialog2002 = Application.FileDialog( _
msoFileDialogFolderPicker)
End Function

Now, in the main module use:

Option Explicit
Sub testIt()
Dim MyFolderDialog As Object

If Val(Application.Version) <= 9 Then
Set MyFolderDialog = New FileDialog2000
Else
Set MyFolderDialog = FileDialog2002
End If

MyFolderDialog.InitialFileName = "C:\TestPage.xls"
MyFolderDialog.Title = "My Personal Folder Browser"
If MyFolderDialog.Show Then
MsgBox "Folder selected: " & MyFolderDialog.InitialFileName
End If
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions