Results 1 to 3 of 3

Thread: SaveAs from cell reference in specific folder

  1. #1

    SaveAs from cell reference in specific folder

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am no expert on vb code and usually get by by pasting chunks of code from forums and tutorials etc.

    The first part of my macro, for information/ context is as follows:

    A .csv data file is opened and a macro started, which does the following:

    • Open a template .xlsx file (which is read only to preserve its integrity)
    • Copy 2 columns from the .csv file into a sheet on the template .xlsx file
    • apply various filters required for generating a graph on a second sheet in the same .xlsx template file

    The coding for the above is sorted and works well, but what I need help with is the second part, which I have been struggling to get right:
    Following the above sequence, the following needs to occur:

    • A "Save As" dialogue window to be brought up...
    • Defaulting to a predetermined folder (say C:\folder)
    • Prepopulating the filename with text from a cell reference in the active document (say A:1)
    • Preselecting the file type as .xlsx

    Saving must not be automatic, so that the user can revise the prepopulated file name if required, before clicking on save

    Some code that I have been playing around with for this last part either saves the file as "FALSE.xlsx" or doesnt save anything at all.

    I would appreciate any help anyone can offer


  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    Lots of examples on Ron's page. see especially the last example at the end using GetSaveAsFilename

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    Minor modification to Ron's code

    Sub PromptToSave(ByVal sPath As String, sFile As String)
    Dim fname As String, FileFormatValue As Long
    'modified from
    'Give the user the choice to save in 2000-2003 format or in one of the
            'new formats. Use the "Save as type" dropdown to make a choice,Default =
            'Excel Macro Enabled Workbook. You can add or remove formats to/from the list
        If Right(sPath, 1) <> "" Then sPath = sPath & "\"
        fname = Application.GetSaveAsFilename(InitialFileName:=sPath & sFile, filefilter:= _
            " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
            " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
            " Excel 2000-2003 Workbook (*.xls), *.xls," & _
            " Excel Binary Workbook (*.xlsb), *.xlsb", _
            FilterIndex:=1, Title:="Please edit the path or file name")
        'Find the correct FileFormat that match the choice in the "Save as type" list
        If fname <> "False" Then
            Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))
            Case "xls": FileFormatValue = 56
            Case "xlsx": FileFormatValue = 51
            Case "xlsm": FileFormatValue = 52
            Case "xlsb": FileFormatValue = 50
            Case Else: FileFormatValue = 0
            End Select
            'Now we can create/Save the file with the xlFileFormat parameter
            'value that match the file extension
            If FileFormatValue = 0 Then
                MsgBox "Sorry, unknown file extension"
                'Save the file in the format you choose in the "Save as type" dropdown
                ActiveWorkbook.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
            End If
        End If
    End Sub
    Call the routine by something like
    Call PromptToSave(ActiveSheet.Range("A1").Value, ActiveSheet.Range("A2").Value)

Tags for this Thread

Posting Permissions

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