Using VBA to Name a file based on a string & a cell value

[Enthusiastic Amateur]

Hello!

I am trying to create a macro that will combine a string value and a cell value to create a file name (I don't want those pesky end-users giving these files any oddball names).

For bonus points I want the user to define the Save To directory -AND- this is CSV output file and it needs to be converted into an XLSX file.

Here is what I have so far. This allows the user to name the file and choose the directory they wish to save it to:

Private Sub SaveFile()
     
     Do
        fname = Application.GetSaveAsFilename( _
            fileFilter:="Microsoft Excel Workbook (*.xlsx), *.xlsx")
    Loop Until fname <> False
            ActiveWorkbook.SaveAs filename:=fname, FileFormat:=xlNormal
    
End Sub

This works fine and I could stop here, but perfectionism is a blessing & curse.

What I would prefer is to predefine the file name to "Case Log for "[cell value] where cell value = the name of a doctor.

Here is what I have for this:

Sub SaveFile2()
    
Dim MDname As String       'MDName is the Dr's name in cell A2
Dim CaseLogName As String

Range("A2").Select

MDname = ActiveCell.Value
    
CaseLogName = "Case Log for " & MDname
    
    ...?
    
End Sub

The ...? is where I'm struggling to find the best solution for this.

How do I best:
1) Combine the two Strings to create a combined file name
2) Save this to a user defined directory
3) Convert the file from a CSV >> XLSX format.

Any advice would be appreciated!

Thanks in advance!

 

[rlv01]

There are a hundred ways to do this. One way:

Sub SaveFile3()

    Dim MDname As String                              'MDName is the Dr's name in cell A2
    Dim WB As Workbook, WS As Worksheet
    Dim FD As FileDialog
    Dim CaseLogName As String
    Dim FilePath As String

    Set WB = ActiveWorkbook
    Set WS = ActiveSheet

    If Not WB.Name = ThisWorkbook.Name Then
        'Make File Name
        MDname = Trim(WS.Range("A2").Value)
        If MDname <> "" Then
            CaseLogName = Format(Date, "yyyy-mm-dd") & " Case Log for Dr. " & MDname & ".xlsx" '<- edit or replace this line to make the file name you want.
        Else
            MsgBox "Doctor's name is missing", , WS.Range("A2").Address
            Exit Sub
        End If
        
        'Pick folder
        Set FD = Application.FileDialog(msoFileDialogFolderPicker)

        FD.Title = "Select a folder"
        FD.InitialFileName = "C:\"

        'Save file as .xlsx
        If FD.Show = -1 Then
            FilePath = FD.SelectedItems(1) & "\" & CaseLogName
            WB.SaveAs Filename:=CaseLogName, FileFormat:=xlOpenXMLWorkbook
        End If
    Else
        MsgBox WB.Name & " has macros and therefore is not a CSV file"
    End If
End Sub