Solved

Excel VBA - GetSaveAsFilename

Posted on 2000-02-16
12
2,320 Views
Last Modified: 2012-05-04
I have a small VBA driven system that includes a save data routine (data is copied to a new workbook and saved as a data file NOT as part of the system).

Using the GetSaveAsFilename method I prompt the user for a filename and assuming they give one the data is saved HOWEVER if an existing filename is chosen the user is prompted as to whether they wish to replace the file - if No or Cancel is chosen my macro crashes.

I know this is because the test I use tests whether GetSaveAsFilename has a value and once a filename is input / selected then it does but obviously choosing No or Cancel means that GetSaveAsFilename has a value but the save is terminated.

I'm assuming I can't get around this?
And if not is it possible to replicate the Save As dialog box but in my own form with command buttons I can control?

I apologise if this isn't as clear as it could be !

TIA,
Neil


0
Comment
Question by:njdavey
12 Comments
 
LVL 2

Expert Comment

by:mrt1
ID: 2527703
Could you paste your code into a comment so that I can see it?

I may be better to check if the file exists using your own code, and then deleting it yourself before performing the save.
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 2527823
you have to check to see to see if it returns false
When they click no or cancel the getsaveasfilename returns False as opposed to the string filename.
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 2527830
an example
dim filesavename as variant
fileSaveName = Application.GetSaveAsFilename( _
      fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName <> False Then
      MsgBox "Save as " & fileSaveName
End I
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 2527848

here is a better example
Sub testFileName()
    Dim fileSaveName As Variant
    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
    If fileSaveName <> False Then
        ActiveWorkbook.SaveAs FileName:=fileSaveName
    Else
        'they cancled the save so don't save it
    End If
End Sub
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 2527906
On second look it seems you are using another pop-up box after they have selected a filename that is confirming the save. Is this a message box with yes, no, and cancel buttons use this
Sub testFileName()
    Dim fileSaveName As Variant
    Dim x As Variant
    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
    If fileSaveName <> False Then
       
        If Dir(fileSaveName) <> "" Then
            'File already exists
            x = MsgBox("File already exists!, Do You Want To Replace The Original?", vbYesNoCancel)
            Select Case x
                Case 7 'They chose NO
                    MsgBox "Enter a New Name"
                    fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Excel Files (*.xls), *.xls")
                    If fileSaveName <> False Then
                        ActiveWorkbook.SaveAs FileName:=fileSaveName
                    Else
                        MsgBox "Data Not Saved"
                        Exit Sub
                    End If
                Case 6 'they chose Yes
                    ActiveWorkbook.SaveAs FileName:=fileSaveName
                Case 2 'They chose cancel
                    'whatever you want it to do
            End Select
        Else
            'file doesn't exist
            ActiveWorkbook.SaveAs FileName:=fileSaveName
        End If
    Else
        'they cancled the save so don't save it
    End If
End Sub


When they choose yes the msgbox returns 6
"No" returns 7
"Cancel" returns 2
0
 
LVL 22

Expert Comment

by:ture
ID: 2528817
Neil,

Perhaps this can be of some help?

Sub SaveTheWorkbook()
  Dim f As Variant
  ActiveWorkbook.Saved = False
  f = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel Workbooks (*.xls), *.xls")
  If f <> False Then
    On Error Resume Next
    ActiveWorkbook.SaveAs f
    On Error GoTo 0
  End If
  If ActiveWorkbook.Saved Then
    MsgBox "The workbook has been saved"
  Else
    MsgBox "The workbook has not been saved"
  End If
End Sub

Ture Magnusson
Karlstad, Sweden
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Accepted Solution

by:
ture earned 200 total points
ID: 2528826
Neil,

This one saves using the specified filename, overwriting previous file without asking first.

Sub SaveTheWorkbook()
  Dim f As Variant
  f = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel Workbooks (*.xls), *.xls")
  If f <> False Then
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs f
    Application.DisplayAlerts = True
  End If
End Sub

/Ture
0
 
LVL 9

Expert Comment

by:antrat
ID: 2529603
Hi njdavey

You may have already found your answer above. But I use a similar method as you mention in one of my apps and I found that using a user form with my own cotrols on it gave me complete flexibility over any scenario, all it does is simply pass the file name to a textbox.

antrat
0
 
LVL 22

Expert Comment

by:ture
ID: 2554443
Neil,

It's been a while. Please don't forget about this question...

/Ture
0
 
LVL 22

Expert Comment

by:ture
ID: 2571999
Neil,
Please don't forget about this question...
/Ture
0
 

Author Comment

by:njdavey
ID: 2572051
I'm not sure I explained what I was after that well but eventually I decided to use the Application.DisplayAlerts command to overcome the error.

Therefore Ture the points are yours !

Ta,
Neil
0
 
LVL 22

Expert Comment

by:ture
ID: 2572091
Thank you, Neil!

/Ture
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macbook MS Office installer missing 11 87
recovering Excel 2016 file 2 51
Excel 2007 Formula That Pulls Highest Degree From List 2 54
Excel 2013 Power Query 1 46
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now