Solved

Excel VBA - GetSaveAsFilename

Posted on 2000-02-16
12
2,365 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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