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

[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