Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    I have encountered some strange (inconsistent) behaviour within the Excel.Application.GetSaveAsFilename dialog. Oddly enough, it behaves when used in Word, but not in similar circumstances in XL. In both cases, I'm wanting it to display existing filenames in the dialog, which conform to the appropriate suffix:

    Word case - *Excess Collection.doc
    Excel case - *Building Invoice.xls

    For Word:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black> ChDrive strPath
    ChDir (strPath) <font color=448800>' No use without changing drive if strPath is on a different drive!!</font color=448800>
    strNewName = Excel.Application.GetSaveAsFilename( _
    InitialFilename:=strPath & Application.PathSeparator & "*Excess Collection.doc", _
    FileFilter:="Text Files (*.txt), *.txt, Word Documents (*.doc), *.doc", _
    FilterIndex:="2", _
    Title:="Satisfaction Release Documents")
    <font color=blue>If</font color=blue> strNewName = <font color=blue>False</font color=blue> <font color=blue>Then</font color=blue> <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    Application.ScreenUpdating = <font color=blue>True</font color=blue>
    </font color=black></code></div hiblock>
    For Excel:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black> ChDrive strSavePath <font color=448800>'</font color=448800>
    ChDir (strSavePath) ' No use without changing drive if strPath is on a different drive!!
    strNewFName = Excel.Application.GetSaveAsFilename( _
    InitialFilename:=strSavePath & Application.PathSeparator & "*Building Invoice.xls", _
    FileFilter:="Excel Files (*.xls), *.xls", _
    FilterIndex:="2", _
    Title:="Save As Building Invoice")
    <font color=blue>If</font color=blue> strNewFName = <font color=blue>False</font color=blue> <font color=blue>Then</font color=blue> <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    Application.ScreenUpdating = <font color=blue>True</font color=blue>
    </font color=black></code></div hiblock>
    Now the problem:
    Word will happily display all filenames ending in "Excess Collection.doc" in the dialog. Excel will not behave accordingly for filenames ending in "Building Invoice.xls". When I jump around through drives and folders in the dialog, I can (sometimes) get the display to recognize all of the files it should. Initially though, it will only display a single filename, which was previously looked for using a Dir command. ???

    Any suggestions desperately appreciated.

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    Having a further fiddle, it appears that Word is quite happy with testing:
    If strNewFName = False Then Exit Sub
    if a new/ existing filename is entered in the dialog, while XL VBA throws a type mismatch error.

    The help file (in XL no less <img src=/S/confused.gif border=0 alt=confused width=15 height=20>) says
    "This method returns the selected file name or the name entered by the user. The returned name may include a path specification. Returns False if the user cancels the dialog box."


    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Works for Word, not for XL it seems. It appears that I can't test for the user hitting "Cancel" in XL's dialog, from within XL, but can do so from Word. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Alan

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    <hr>If the user clicks Save, a Boolean value False is returned, otherwise a String value<hr>
    The other way around of course.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    Oops <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    Edited by HansV to correct stupid mistake - thanks to Jan Karel for pointing it out!

    GetSaveAsFilename returns a Variant. If the user clicks Cancel, a Boolean value False is returned, otherwise a String value. Hence, strNewFName must be declared as a Variant, not as a String, for you cannot perform the comparison

    If strNewFName = False Then

    if strNewFName is a String variable. With a Variant, the code works OK for me in Excel 2002 (XP): only files with a name ending in the specified text are displayed, and the code reacts correctly to selecting a file and to canceling the dialog.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    These appear to be two separate issues that I've run into one. I realize that the return value must be a variant, but I found that Word will "tolerate" it being returned to a string, and will perform the boolean comparison correctly. XL won't.

    The other aspect - the incomplete display of files - appears to be due to things not refreshing properly (not sure how exactly). Sometimes it works, other times not. Sometimes closing then reopening XL seems to rectify things. I'll have to fiddle further.

    cheers
    Alan

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    FWIW, the JohnBF lazy way:

    Dim strNewFName as String
    strNewFName = CStr(Excel.Application.GetSaveAsFilename( ...))
    If strNewFName = "False" Then
    Else
    ...

    and pray that no no-extension file named "False" exists or is proposed.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: GetSaveAsFilename Dialog Display Issues (2000 sp-3/ 98SE/ VB6)

    To be language-independent, use

    If strNewName = CStr(False) Then

    (CStr translates the VBA constant False into the language of the Windows installation)

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000

    Thanks to all the contibutors of this thread.

    How about <pre> Dim strNewFName
    strNewFName = Excel.Application.GetSaveAsFilename()
    Select Case VarType(strNewFName)
    Case vbBoolean
    If strNewFName = False Then ' user cancelled
    Else ' ???
    End If
    Case vbString
    ' do something with the returned string
    Case Else
    ' ehhh?
    End Select
    </pre>


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

    Re: GetSaveAsFilename Dialog Display Issues (2000

    You don't need the Else and Case Else, they will never occur. So you can shorten the code to
    <code>
    Dim varNewFName As Variant
    varNewFName = Excel.Application.GetSaveAsFilename
    If VarType(varNewFName) = vbBoolean Then
    ' user cancelled
    Else
    ' do something with the returned string
    End If</code>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetSaveAsFilename Dialog Display Issues (2000

    >You don't need the Else and Case Else, they will never occur.
    Right.
    It's an old habit of putting in a "Msgbox catastrophic error" for all those cases which, the client says, will never arise.
    I agree with you.
    Until MSoft changes the VBA specs (not likely now, I know).

Posting Permissions

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