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

    Lang

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Lots of examples on Ron's page. http://www.rondebruin.nl/win/s5/win001.htm see especially the last example at the end using GetSaveAsFilename

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Minor modification to Ron's code

    Code:
    Sub PromptToSave(ByVal sPath As String, sFile As String)
    Dim fname As String, FileFormatValue As Long
    
    
    'modified from http://www.rondebruin.nl/win/s5/win001.htm
    '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"
            Else
                '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
    Code:
    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
  •