+ Reply to Thread
Results 1 to 5 of 5

Issue with automatically saving a file to a folder on my desktop

Hybrid View

  1. #1
    Registered User
    Join Date
    02-09-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Issue with automatically saving a file to a folder on my desktop

    I have a quote program that i have put together with a lot of help from asking questions on these forums. I have a save as button on my sheet and would like to automatically save a backup .xlms file to a specific folder on my desktop. I would like to have it save the file name from 2 seperate fields in one workbook. Example: Customer Name & Job Name. The other issue is that it takes me to an excel filer first before taking me to the option of entering the file name and creating a backup .xlms file. Can someone look at this code and tell me what I need to do? I have been teaching myself VBA programming for the last 3 months and I am still not fluent in this language. Anything will help!
    ElseIf Application.Caller = "SaveAsExcelButton" Then
        Dim Fname As String
        MsgBox "Where would you like to save the Excel Backup of this Quote", vbOKOnly + vbExclamation, "Backup Quote"
        Fname = Application.GetSaveAsFilename("", _
        FileFilter:="Excel Filer (*.xlsm), *.xlsm", _
                      Title:="Save Quote Backup")
        If Application.Dialogs(xlDialogSaveAs).Show(, , vbNullString) Then
            MsgBox "Your Excel File Has Been Saved", vbExclamation, "Backup Quote"
        Else
            MsgBox "YOU DID NOT SAVE A BACKUP!!", vbOKOnly + vbCritical, "Try Again"
        End If
    Last edited by Leith Ross; 02-28-2013 at 10:04 PM. Reason: Added Code Tags

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Issue with automatically saving a file to a folder on my desktop

    Here's the SaveAs method text from the help file:

    Set NewBook = Workbooks.Add
    Do
        fName = Application.GetSaveAsFilename
    Loop Until fName <> False
    NewBook.SaveAs Filename:=fName
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    02-09-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Issue with automatically saving a file to a folder on my desktop

    Please help me understand your code. On "Sheet 2" I want cells B13 & B17 to be used as my file name. Them I want it to save to the same folder as I complete my quotes. Is this possible to do? Where would this code fit into mine? Remember that I am fairly new and still trying to understand writing code. I appreciate your response! Thank you!

  4. #4
    Registered User
    Join Date
    02-09-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Issue with automatically saving a file to a folder on my desktop

    Maybe it would make more sense if you saw the issue.

    Thank you for any and all help!

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Issue with automatically saving a file to a folder on my desktop

    Hi pcm,

    This is how I'd insert the helpfile code:

    ElseIf Application.Caller = "SaveAsExcelButton" Then
        Dim Fname As String, Newbook As Workbook
        MsgBox "Where would you like to save the Excel Backup of this Quote", vbOKOnly + vbExclamation, "Backup Quote"
        Do: Fname = Application.GetSaveAsFilename
                      Loop Until Fname <> False
                      Newbook.SaveAs Filename:=Fname
        If Application.Dialogs(xlDialogSaveAs).Show(, , vbNullString) Then
            MsgBox "Your Excel File Has Been Saved", vbExclamation, "Backup Quote"
        Else
            MsgBox "YOU DID NOT SAVE A BACKUP!!", vbOKOnly + vbCritical, "Try Again"
        End If
    But if you want B13 & B17 to be the filename:

    Fname=Sheets(2).Range("B13") & Sheets(2).Range("B17")
    ActiveWorkbook.SaveAs Filename:=Fname
    And, you'd probably want to close it so that you'd be back in the one you backed up.

    ActiveWorkbook.Close
    Last edited by xladept; 03-01-2013 at 02:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1