Solved

Excel VBA - GetSaveAsFilename

Posted on 2000-02-16
12
2,294 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Accepted Solution

by:
ture earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Neil,

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

/Ture
0
 
LVL 22

Expert Comment

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

Author Comment

by:njdavey
Comment Utility
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
Comment Utility
Thank you, Neil!

/Ture
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

772 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

10 Experts available now in Live!

Get 1:1 Help Now