Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA - GetSaveAsFilename

Posted on 2000-02-16
12
Medium Priority
?
2,541 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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