Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    File SaveAs - Cancel (Excel2K)

    Hi,

    The following code in a Workbook_Open procedure attempts to save a new file, giving the user the opportunity to change the filename or cancel, but the cancel option still seems to result in a file being saved - despite several different coding attempts as shown commented.

    Any suggestions as to what code is best to fix this:

    <pre> ' Prepares to Save File with Location and Filename+yymm as
    ' required and provides facility for Operator intervention.
    Dim fileSaveName As String
    ChDir Application.DefaultFilePath
    fileSaveName = "TIMESHT" _
    & Format(TSMonth.Value, "yymm") & ".xls"
    Application.GetSaveAsFilename InitialFilename:=fileSaveName, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls"
    ' If fileSaveName <> False Then
    ' If fileSaveName <> "" Then
    If fileSaveName <> CStr(False) Then
    ActiveWorkbook.SaveAs Filename:=fileSaveName, _
    FileFormat:=xlNormal, AddToMru:=True
    End If
    </pre>


    Thanks in advance.

    Peter Moran
    Two heads are always better than one!

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File SaveAs - Cancel (Excel2K)

    No time to check it, but I think that "FALSE" will work. But instead of that, I usually make the name a variant and check if the return type is Boolean:
    dim fileSaveName as Variant

    fileSaveName = Application. ...File... ( ... )
    if VarType(fileSaveName) = vbBoolean then 'pressed cancel

    If this dosn't help, post back and I'll test it tomorrow. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: File SaveAs - Cancel (Excel2K)

    Sam is right, GetFileSaveAs returns a Boolean FALSE if cancelled, else it returns the file name as String. See the thread starting with <post#=209230>post 209230</post#>. Your problem is the logic; you GetFileSaveAs but never use the returned value for it. You set the file name with the line
    fileSaveName = "TIMESHT" & Format(TSMonth.Value, "yymm") & ".xls"

    do nothing with the
    Application.GetSaveAsFilename InitialFilename:=fileSaveName, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls"'

    so here, fileSaveName can never be false, because it has been not been reset on Cancel because its value doesn't come from GetFileSaveAs
    If fileSaveName <> CStr(False) Then

    You need to use something like (untested)

    Dim fileSaveName as Variant
    fileSaveName = Application.GetSaveAsFilename(InitialFilename:="TI MESHT" & _
    Format(TSMonth.Value, "yymm") & ".xls", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls"')
    ' the above two lines will need some correction
    If VarType(fileSaveName) <> Boolean Then ' that is, it's not False
    ' Save it
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: File SaveAs - Cancel (Excel2K)

    Hi,

    Thanks Sam and John for your quick replies. Hope to sort out this problem in the next day or so. Will let you know what happens.

    Regards,

    Peter Moran
    Two heads are always better than one!

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: File SaveAs - Cancel (Excel2K)

    Hi again,

    Not able to get the following code to work despite your good efforts and reviewing the suggested thread.

    Any further suggestions?

    <pre> Dim fileSaveName As Variant
    ChDir Application.DefaultFilePath
    Application.GetSaveAsFilename InitialFilename:="TIMESHT" _
    & Format(TSMonth.Value, "yymm") & ".xls", _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls"
    If VarType(fileSaveName) <> vbBoolean Then
    ActiveWorkbook.SaveAs Filename:=fileSaveName, _
    FileFormat:=xlNormal, AddToMru:=True
    End If
    </pre>



    The result when Cancel is used is - "Run time error '1004' - The file could not be accessed.........." on the SaveAs statement

    I presume that the Cancel removed the filename but the test did not stop the attempt to save the file!

    Regards,

    Peter Moran
    Two heads are always better than one!

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

    Re: File SaveAs - Cancel (Excel2K)

    You need to obtain the return value of GetFileSaveAs; you are not getting and setting fileSaveName to what GetFileSaveAs returns. This code worked for me, changes in red:

    Sub test()
    Dim fileSaveName As Variant
    ChDir Application.DefaultFilePath
    <font color=red> fileSaveName = Application.GetSaveAsFilename(InitialFilename:="TI MESHT" _
    & Format(Date, "yymm") & ".xls", FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")</font color=red>
    If VarType(fileSaveName) <> vbBoolean Then
    ActiveWorkbook.SaveAs Filename:=fileSaveName, _
    FileFormat:=xlNormal, AddToMru:=True
    <font color=red> Else
    MsgBox "You cancelled, fool!"</font color=red>
    End If
    End Sub

    BTW, I didn't know for certain what was in the variable TSMonth, so I cheated and used system variable Date.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: File SaveAs - Cancel (Excel2K)

    Hi John,

    Spot on! We are now in business! Great!

    I now understand what you meant about the return value of GetFileSaveAs, and you were right, TSMonth is just the relevant date in the timesheet.

    Many thanks

    Peter Moran
    Two heads are always better than one!

Posting Permissions

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