SaveAs from cell reference in specific folder

Langchop

New member
Joined
Sep 23, 2014
Messages
1
Reaction score
0
Points
0
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
 
Minor modification to Ron's code

Code:
[B]Sub PromptToSave(ByVal sPath As String, sFile As String)
Dim fname As String, FileFormatValue As Long[/B]


'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:=[B]sPath & sFile[/B], 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:=[B]1[/B], Title:="[B]Please edit the path or file name[/B]")


    '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)
 
Back
Top