?
Solved

Excel VBA - GetSaveAsFilename

Posted on 2000-02-16
12
Medium Priority
?
2,497 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

719 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