VBA Macro To Create An Excel File With Same Sheet Name As Workbook Name

[DocAElstein]

Hi Flupsi,
I am not sure if I quite understand your question?

Your original Code saves as Adjustment JNL.csv

In your original code the following line
.Sheets(1).Name = "JNL"
changes the first Worksheet name.

So if you do not want that change to happen, then simply remove that code line

 Sub CreateCSVFile()
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "C:\Journal Templates"
MyFileName = "Adjustment JNL.csv"

    If Not Right(MyPath, 1) = "" Then MyPath = MyPath & ""
    
With ActiveWorkbook

    .SaveAs Filename:= _
        MyPath & MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False
    
    .Close False
End With 
End Sub

Alan

[DocAElstein]

Hi again Flupsi,

Possibly you are asking for the File to be saved as a .csv File, but with the name of the Current Active Workbook ?

If so the next code will do that:

'
 Sub CreateCSVFileNameFromActiveWorkbook() '   http://www.excelfox.com/forum/showthread.php/2123-Macro-to-crete-CSV-with-same-sheetname-as-xlsm-file
    With ActiveWorkbook
Rem 1 The ActiveWorkbook Path
    Dim MyPathAW As String: MyPathAW = ActiveWorkbook.Path ' String path to Folder containing the current Active Workbook
        If Not Right(MyPathAW, 1) = "\" Then MyPathAW = MyPathAW & "\" ' This will usually always be needed
Rem 2 The File name:
    '2a) ' ActiveWorkbook Full Name
    Dim MyFileAW As String: Let MyFileAW = ActiveWorkbook.Name 'The .Name Property of the Active Workbook returns the string name including the extension ( .xlsm, or .xls  etc. )
    '2b) ' ActiveWorkbook Name without extension
    Dim MyFileAWNameOnly As String: Let MyFileAWNameOnly = Left(MyFileAW, (InStrRev(MyFileAW, ".") - 1)) 'To Take off the bit after the . dot     (InStrRev(MyFileAW, ".")   gives the position of the last  .    This is also the first . looking from the right, but the position is counting from the left   Applying  -1  will give the postion just before the last  .     MyFileAWNameOnly then becomes the first   (InStrRev(MyFileAW, ".") - 1)   characters in MyFileAW  counting from the left.
    '2c) ' The File name I finally want
    Dim MyFileName As String: Let MyFileName = MyFileAWNameOnly & ".csv"
Rem 3 save the File as a .csv File
     .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
     .Close False ' False prevents being asked to save changes. It will not prevent being asked if you wish to overwrite an existing File with this Path and Name
    End With
End Sub

Alan

[Flupsie]

Hi Alan

Thanks for the help

I need two small changes

1) I need the csv file to be saved to "C:\Journal Templates" -(have amended this in the code)
2) I need the sheet name to be the same as the xlsm workbook i.e "JNL"


I have attached my sample workbook

It would be appreciated if you could make the necessary change