Late Bind MSO

[zmbd]

Ok,
I’ve an old version of a nice little file dialog that works well; however, it was developed using the “Microsoft Office 10 Objects Library” reference using early binding…
Now each time we’ve upgraded, for some reason I’ve had to go in to the database and make sure that the reference is set… (last time I used Microsoft Office 11 Objects Library)
The real pain is that with every new database I create and import this code into, I have to go back and make sure that there is a reference to the MSO…

Then a 3W Bulb went off, I thought… If I can late bind Excel, Word, VBScript, etc… and the code already works, then let’s late bind this monster too!!!

Simple, or so I thought… I have tried:

<doesn’t work>
Dim objFD as Object
Set objFD = CreateObject(“Office.Application”)

<doesn’t work>
Dim objFD as Object
Set objFD = CreateObject(“Office.FileDialog”)

<doesn’t work>
Dim objFD as Object
Set objFD = CreateObject(“MSO.Application”)

Puzzled, I turned to the books… no luck there…. So I’ve tried the normal Google and Bing and Yahoo and you name it searches… tried the three dozen forums I’ve read for years… seen a lot of the same question… and yet, no answer
How do we late bind the "Microsoft Office ## Object Library" so that vba module uses the latest version as we can simularly do with Excel/Word/Outlook/VBscript?

-z

[ariful alam]

please, have a look @ this -> http://www.tech-archive.net/Archive/...4-07/0359.html

hope works for you. :)

[zmbd]

Ariful Alam, unfortunately this is not an issue with “Word Object Library” late binding. There is no issue with being able to late bind to the other office applications such as Word, Excel, Outlook, etc… nor is there an issue with VBScript.

What I cannot do is late bind the Microsoft Office Object ## Library that would find located at:
C:\Program Files\Common Files\Microsoft Shared\OFFICE14 \MSO.DLL
In a typical MSOffice2010 local PC installation.
(in 2003 I believe the subfolder was OFFICE11)

What I need is the late bind to the MSO.DLL
-z

[Rabbit]

Each one of these can potentially be used to create a file dialog.

UserAccounts.CommonDialog
MSComDlg.CommonDialog
MSComDlg.CommonDialog.1
Word.Application
SAFRCFileDlg.FileOpen
InternetExplorer.Application

Oddly enough, I find that the most reliable one amongst the different versions of windows is the internet explorer object.

[zmbd]

So this is what I've been able to do as P.O.C.

Function Z_File_Dialog()
'
'Pulled these here for latter use to compare against the 
'code I am currently useing.
'
Const msoFileDialogFilePicker As Integer = 3
Const msoFileDialogFolderPicker As Integer = 4
Const msoFileDialogOpen As Integer = 1
Const msoFileDialogSaveAs As Integer = 2
'
'
Dim objFileOpen As Object
Dim varSelectedItem As Variant
'
'assign the object
Set objFileOpen = Application.FileDialog(msoFileDialogFilePicker)
'
'set the options
objFileOpen.AllowMultiSelect = False
'
'show the form
objFileOpen.Show
'
'now in this case I'm using the file picker so let's see if
'a file was returned and return a false if not
If objFileOpen.SelectedItems.Count > 0 Then
    For Each varSelectedItem In objFileOpen.SelectedItems
        Z_File_Dialog = varSelectedItem
    Next varSelectedItem
Else
    findthefile = False
End If
'
'cleanup
Set objFileOpen = Nothing
End Function

Works without reference to the object libraries as is required in my current code. Still an aggravation that using the "Office" name doesn't work as expected (that is the name that VBA returns from the reference collection when the MSO is selected) so the codeing has to change a tad from early binding (which makes the intelisense available while in development) as the are some differences in what is available for properties.

Still looking for a way to late bind the MSO

-z


-z

[Stewart Ross]

You don't need to bind an instance of an object to MSO to use its services at all, Z. Some of MSO's methods are common across the Office applications, and where applicable have already been instantiated by the application itself. They are available to you explicitly through the Application object, as you have found yourself in post #5, so this is not about early or late binding of the MSO library as such.

What setting an explicit reference to the MSO library does is to allow you to use intellisense whilst developing, and it provides access to the values of the mso constants such as msoFileDialogVi ewDetails defined by the DLL. It makes no difference to your ability to use the methods concerned at all.

As you found for yourself in post #5, you can use the FileDialog method in your code without setting a reference to the MSO object, as long as you substitute or define actual values for the mso constants.

For example, the following function returns the paths of files selected by the user with no reference set to MSO in the calling application (which I've tested in both Excel and Access):

Public Function fFileDialog(Optional Filename = "", Optional FolderOnly As Boolean = False)

'Does not require reference to Microsoft Office Object Library MSO.DLL

Const msoFileDialogFolderPicker = 4
Const msoFileDialogFilePicker = 3
Const msoFileDialogViewDetails = 2

Dim fd As Object

Dim varItems As Variant
Dim flPath As Variant
If FolderOnly Then
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Else
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
End If
 
With fd
    .AllowMultiSelect = True
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = Filename
    If .Show = True Then
        For Each varItems In .SelectedItems
            flPath = varItems
        Next
    End If
End With

To sum up, the file dialog method provided by the MSO DLL is already instantiated and available through the Application object. It is not necessary to bind the MSO library itself to your code in order to use it (regardless of early or late binding) - but adding a reference for early binding allows the VBA editor to assist you with intellisense and gives you access to all the pre-defined constants.

The advantage of NOT specifying a reference to the MSO object is that your file dialog code will work across multiple Office versions with no reference changes required, as long as you define the constants you use.

-Stewart

[zmbd]

Stewart,
This is as I was begining to suspect.
It is really very nice to confirm this information. I really did read over 100 different threads thruout the web without a single person, even the MVPs and other MS people, ever making this one statement very clear!
It would be nice if the MSO would follow the same conventions as the other references!

I may clean my code up a tad and make a nice generic now that I have this information!!

Thnx
-z