Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    File Browser on User Form (2000 sp3)

    Is there a way to put a file browser on a UserForm?
    I need to have the user specify a path and file name and I'm not happy with the builtin dialogs. They are more restrictive in Word than they are in Excel. In Excel I can use GetOpenFileName and GetSaveAsFileName and specify the title, filter list, etc. In Word I figured out how to set the default open path and fill in the .name with a string but I still can't change the title or add text. I like the idea of putting everything I need on a UserForm but I can't find any information on the long list of additional controls that are available under the Tools, Additional Controls menu, I don't even know if that's the right place to look.
    Is there a way to use the controls on the builtin dialog boxes on a UserForm?
    Any help you can offer, is most appreciated.
    Thanks

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File Browser on User Form (2000 sp3)

    You may want to check out this MSDN Library article: Enhance Your Apps with Common Dialogs: Part I . It wraps the API stuff for the Windows Common Dialog in a class module and includes a gratifying handful of input arguments.

    You can read more about the issues I had that led me to look at it in <!post=this thread,385724>this thread<!/post>.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    Thanks for the leads. I started reading them and they do look promising. I think you may have given me hope! Thanks.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    This is great stuff indeed! I have two questions I haven't been able to find the answer to:
    1) Is there a way to change the position of the dialog box? I don't see a .top and .left property. The dialog box pops up all the way in the upper lefthand corner. Even a flag to say "center" would be nice.
    2) Is there something like a .execute that I can issue to execute the dialog box function after I check what the user entered or chose?
    Thanks

  5. #5
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File Browser on User Form (2000 sp3)

    1. I expect there's a way to reposition the dialog using API calls, but that's beyond my expertise.

    2. Here's some sample code for showing the dialog and then opening the chosen files. (The Common Dialog isn't specifically an "open" dialog, so you're free to do what you like with the chosen files, and on the other hand there's no default action that will be taken if you don't.)
    <pre> Dim oComDlg As ComDlg
    Dim x As Long

    Set oComDlg = New ComDlg

    With oComDlg
    'Set the dialog properties here.
    If .ShowOpen Then
    For x = 1 To .FileNames.Count
    Documents.Open FileName:=.FileNames(x), _
    ReadOnly:=.CheckBoxSelected
    Next x
    End If
    End With

    Set oComDlg = Nothing</pre>


  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,265
    Thanks
    2
    Thanked 187 Times in 173 Posts

    Re: File Browser on User Form (2000 sp3)

    You need the SetWindowPos function to relocate the window:
    <pre> Public Declare Function SetWindowPos Lib "user32" ( _
    ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
    ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
    ByVal cy As Long, ByVal wFlags As Long) As Long
    </pre>

    You could build this into the wrapper as a positioning method taking x, y, height and width arguments.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Thanks! I really appreciate your help.

    I found this article about centering the dialog box:
    http://vbnet.mvps.org/index.html?code/comd...lgsoverview.htm
    and YIKES, it's beyond my expertise at this point too! I'll try to understand handles another day when I get some of these other problems worked out.

    Ah ha! So, I have to do the save or open myself afterwards. That makes sense and isn't too hard.

    By the way, at the risk of taking advantage of your good and generous nature, I have a few other problems. Maybe we should open another thread specific to common dialog boxes.
    1) I specified nMaxFile and nMaxFileTitle as 260 but upon return the values are limited to 30 characters. Any idea what that's about?
    2) How can I test the flags upon return? For the time being, since the value of the flags I set was 2062, I subtracted that from the returned flags value and I'm testing to see if it is equal to 1024 to indicate that the user changed the extension, but oh my! there must be a more obvious way.
    3) Somewhere I read that if the file name ends in "." then nFileExtension will equal zero. Not when I try it. The default extension just gets appended and the file name ends up as something..doc.
    4) Any idea where part II of that article is? I can't find it anywhere.
    5) Any idea why the article talks about this code being in a class module? I have it in a regular module and it works fine, except for the problems I'm having!

    Again, thank you very much for your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: File Browser on User Form (2000 sp3)

    2) You can test if a flag is set by ANDing .flags with that flag:

    If OFN.flags And OFN_EXTENSIONDIFFERENT Then ...

    3) Only if sDefFileExt is null.

    4) I don't see a mention of a part II.

    5) I don't see class modules mentioned, but in itself, a class module would be a good way to encapsulate the API function calls in a more user-friendly wrapper.

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    Thanks Rory,
    From what I saw doing a search on this, it looks like I need to understand handles more and how to reference the common dialog I want to display in the function. You've helped me find what I think is an easier way, if I build the class module correctly. Thanks again.

  10. #10
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    Hans,
    You never cease to amaze me! Thanks for editing my post. The reference to the help will come in REAL handy! I was wondering how other people were able to get different fonts and such.

    AND, but of course! I was trying to remember exclusive ors and missed the obvious; a non zero value is true. Thanks!

    Thanks for clearing up the file extension problem. I'll just check for ".." in the string and change it before the save.

    I should have been more specific about the URL I was referring to with regards to "Part II" and class references. st333ve sent a URL earlier in this thread for an MSDN article Enhance your Apps with Common Dialogs: Pat I . That's the article I was referring to with questions 4 and 5.

    Any ideas on the max of 30 characters coming back?

    Thanks for your help!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: File Browser on User Form (2000 sp3)

    I copied the code into a module and had no problem returning long paths/filenames. Do you create a buffer for the selected file name?

    With OFN
    ...
    .sFile = String(260, 0)
    .nMaxFile = Len(.sFile)
    ...
    If GetOpenFileName(OFN) Then
    MsgBox Left(.sFile, InStr(.sFile, vbNullChar) - 1)
    End If
    End With

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    You have a way of not only answering my questions, but making me think also! Thank you. I was not creating a large enough buffer. The value I moved into .sFile was 30 characters long. I thought that specifying .nMaxFile as 260 was creating the buffer. Now I see that I need to append more space to the end of the string I'm assigning to .sFile and then specify nMaxFile as the actual length of what I made .sFile.
    Thank You Very Much!

  13. #13
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File Browser on User Form (2000 sp3)

    I was able to obtain a copy of part two of the article but it's too big to attach. I tried to split it up and zip the pieces but even then, one file is 102KB. If you're interested, any suggestions?
    Thanks

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: File Browser on User Form (2000 sp3)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Links are better anyway - fewer copyright issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •