By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Late Bind MSO

zmbd
Expert Mod 5K+
P: 5,287
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:
Expand|Select|Wrap|Line Numbers
  1. <doesn’t work>
  2. Dim objFD as Object
  3. Set objFD = CreateObject(“Office.Application”)
  4.  
  5. <doesn’t work>
  6. Dim objFD as Object
  7. Set objFD = CreateObject(“Office.FileDialog”)
  8.  
  9. <doesn’t work>
  10. Dim objFD as Object
  11. 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
Aug 16 '12 #1

✓ answered by 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 msoFileDialogViewDetails 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):

Expand|Select|Wrap|Line Numbers
  1. Public Function fFileDialog(Optional Filename = "", Optional FolderOnly As Boolean = False)
  2.  
  3. 'Does not require reference to Microsoft Office Object Library MSO.DLL
  4.  
  5. Const msoFileDialogFolderPicker = 4
  6. Const msoFileDialogFilePicker = 3
  7. Const msoFileDialogViewDetails = 2
  8.  
  9. Dim fd As Object
  10.  
  11. Dim varItems As Variant
  12. Dim flPath As Variant
  13. If FolderOnly Then
  14.     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  15. Else
  16.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  17. End If
  18.  
  19. With fd
  20.     .AllowMultiSelect = True
  21.     .InitialView = msoFileDialogViewDetails
  22.     .InitialFileName = Filename
  23.     If .Show = True Then
  24.         For Each varItems In .SelectedItems
  25.             flPath = varItems
  26.         Next
  27.     End If
  28. End With
  29.  
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

Share this Question
Share on Google+
6 Replies


ariful alam
100+
P: 185
please, have a look @ this -> http://www.tech-archive.net/Archive/...4-07/0359.html

hope works for you. :)
Aug 16 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 16 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
Each one of these can potentially be used to create a file dialog.
Expand|Select|Wrap|Line Numbers
  1. UserAccounts.CommonDialog
  2. MSComDlg.CommonDialog
  3. MSComDlg.CommonDialog.1
  4. Word.Application
  5. SAFRCFileDlg.FileOpen
  6. InternetExplorer.Application
Oddly enough, I find that the most reliable one amongst the different versions of windows is the internet explorer object.
Aug 16 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
So this is what I've been able to do as P.O.C.
Expand|Select|Wrap|Line Numbers
  1. Function Z_File_Dialog()
  2. '
  3. 'Pulled these here for latter use to compare against the 
  4. 'code I am currently useing.
  5. '
  6. Const msoFileDialogFilePicker As Integer = 3
  7. Const msoFileDialogFolderPicker As Integer = 4
  8. Const msoFileDialogOpen As Integer = 1
  9. Const msoFileDialogSaveAs As Integer = 2
  10. '
  11. '
  12. Dim objFileOpen As Object
  13. Dim varSelectedItem As Variant
  14. '
  15. 'assign the object
  16. Set objFileOpen = Application.FileDialog(msoFileDialogFilePicker)
  17. '
  18. 'set the options
  19. objFileOpen.AllowMultiSelect = False
  20. '
  21. 'show the form
  22. objFileOpen.Show
  23. '
  24. 'now in this case I'm using the file picker so let's see if
  25. 'a file was returned and return a false if not
  26. If objFileOpen.SelectedItems.Count > 0 Then
  27.     For Each varSelectedItem In objFileOpen.SelectedItems
  28.         Z_File_Dialog = varSelectedItem
  29.     Next varSelectedItem
  30. Else
  31.     findthefile = False
  32. End If
  33. '
  34. 'cleanup
  35. Set objFileOpen = Nothing
  36. 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
Aug 17 '12 #5

Expert Mod 2.5K+
P: 2,545
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 msoFileDialogViewDetails 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):

Expand|Select|Wrap|Line Numbers
  1. Public Function fFileDialog(Optional Filename = "", Optional FolderOnly As Boolean = False)
  2.  
  3. 'Does not require reference to Microsoft Office Object Library MSO.DLL
  4.  
  5. Const msoFileDialogFolderPicker = 4
  6. Const msoFileDialogFilePicker = 3
  7. Const msoFileDialogViewDetails = 2
  8.  
  9. Dim fd As Object
  10.  
  11. Dim varItems As Variant
  12. Dim flPath As Variant
  13. If FolderOnly Then
  14.     Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  15. Else
  16.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
  17. End If
  18.  
  19. With fd
  20.     .AllowMultiSelect = True
  21.     .InitialView = msoFileDialogViewDetails
  22.     .InitialFileName = Filename
  23.     If .Show = True Then
  24.         For Each varItems In .SelectedItems
  25.             flPath = varItems
  26.         Next
  27.     End If
  28. End With
  29.  
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
Aug 18 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 18 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.