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

**Flupsie** · 08-28-2016, 01:45 AM

I have written code to create a csv file, but the sheet name must the same as sheet1 on the xlsm file from whch it was created

Code:

 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
.Sheets(1).Name = "JNL"
    .SaveAs Filename:= _
        MyPath & MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False
    
    .Close False
End With
End Sub

**DocAElstein** · 08-29-2016, 04:40 AM

Hi Flupsie,
_ I assume by „Sheet1“ you mean the first “Tab” Counting from the Left. This can be referenced by it’s Item number , which is a consecutive count starting at 1 from the left and increasing by 1 as you count to the right. ( _.....you actually used the “shorthand” version of that yourself with the
.Sheets(1).Name= .....)

Here the modified code:

Code:

'
 Sub CreateCSVFile() '   http://www.excelfox.com/forum/showthread.php/2123-Macro-to-crete-CSV-with-same-sheetname-as-xlsm-file
'The path name:
Dim MyPath As String: MyPath = "C:\Journal Templates\"
            'MyFileName = "Adjustment JNL.csv"
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
   
    With ActiveWorkbook
    'The File name:
    Dim MyFileName As String: Let MyFileName = .Worksheets.Item(1).Name & ".csv" 'The Worksheets Collection Object is used referrencing by Item number which is a consecutive count starting at 1 from the left  and increasing by 1 as you count to the right. This Worksheet has the .Name Property applied to return the String Tab Name
                '.Sheets(1).Name = "JNL"
     .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
     .Close False
    End With
End Sub

Alan

**Flupsie** · 08-29-2016, 08:50 AM

Originally Posted by DocAElstein

Hi Flupsie,
_ I assume by „Sheet1“ you mean the first “Tab” Counting from the Left. This can be referenced by it’s Item number , which is a consecutive count starting at 1 from the left and increasing by 1 as you count to the right. ( _.....you actually used the “shorthand” version of that yourself with the
.Sheets(1).Name= .....)

Here the modified code:

Code:

'
 Sub CreateCSVFile() '   http://www.excelfox.com/forum/showthread.php/2123-Macro-to-crete-CSV-with-same-sheetname-as-xlsm-file
'The path name:
Dim MyPath As String: MyPath = "C:\Journal Templates\"
            'MyFileName = "Adjustment JNL.csv"
    If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
   
    With ActiveWorkbook
    'The File name:
    Dim MyFileName As String: Let MyFileName = .Worksheets.Item(1).Name & ".csv" 'The Worksheets Collection Object is used referrencing by Item number which is a consecutive count starting at 1 from the left  and increasing by 1 as you count to the right. This Worksheet has the .Name Property applied to return the String Tab Name
                '.Sheets(1).Name = "JNL"
     .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
     .Close False
    End With
End Sub

Alan

Thanks for the help Alan. I need one small change. The sheet name is now correct, but I need the original workbook name i.e Adjustment JNL.csv

Kindly amend your code to incorporate this

**DocAElstein** · 08-29-2016, 01:35 PM

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

Code:

 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** · 08-29-2016, 07:13 PM

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:

Code:

'
 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** · 08-29-2016, 10:01 PM

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

**DocAElstein** · 08-30-2016, 04:24 AM

Hi again Flupsi,

I think I understand now. Actually I see now you explained adequately in the first Post. Sorry I do not have too much experience with .csv files and I did not realise what happened with “Worksheet” names. So I have learned something too :-)
_............................
I think I can explain the problem.

A CSV file is just a text file. It is not an Excel File. What you are seeing is not a Spreadsheet.
If you open your CSV file in NotePad, you will see that it's just data.
A CSV file stores no information at all on formatting, formulas, Worksheets etc. It has no worksheets. It is just a set of values, typically separated by some separator.
If you open your file in Excel you are still seeing just data. It may appear to be in the form of a Spreadsheet. But it is not. The “Tab” at the bottom is not a Worksheet Name. It may appear to be a Worksheet, and you can even change its value within Excel. However, by saving as .csv the value at the bottom will have no meaning. After closing and opening the .csv File the value shown in that Tab will be again that of the File Name. It is not a Worksheet name.

So you see you cannot change the Worksheet name of a .csv File. This is because A .csv File has no Worksheet. It is a simple File of data which, when "viewed in Excel, will be displayed in a "Worksheet type"format. The people who wrote Excel were in my opinion a bit silly to put a value in the tab at the bottom when the File is a .csv. They should have just left it blank or removed it all together to avoid the confusion, in my opinion. That value being shown in the tab is not a Worksheet name. It is the actual file name. The value there will always be given the File name. There is no way to get over that.

I hope that helps a bit....
_......

The best I can suggest is to save as an .xlsx File if you want a Worksheet name.

A simple way to do what you want then is to copy the first sheet. This automatically creates a new workbook with a sheet in it of the same name as that from which it is copied. That can be saved as a .xlsx file ( with the name of your choosing). There is no point in trying to save that file after as a .csv with a different name to the Worksheet name. If you have understood what I have said you will see that you will no longer have a Worksheet and the “value” written in the Tab below will be as previously just the File Name.

So this code will do almost what you want, except that the file saved is a .xlsx File

Code:

Sub CopyToXLSX() ' http://www.excelfox.com/forum/showthread.php/2123-Macro-to-create-CSV-with-same-sheetname-as-Excel-xlsm-file?p=9924#post9924
Rem 1 Workbooks and Worksheets Info
'1a) The Required Workbook Path of File to be saved
Dim MyPathAW As String: MyPathAW = "C:\Pinnacle Journal Templates\": ' If Not Right(MyPathAW, 1) = "\" Then MyPathAW = MyPathAW & "\" ' 'Makes sure the path name ends with "\" - not really necerssary as inspection shows we clearly have a last \
'1b) File with first Worksheet which should be saved as a ( .xlsx ) File. Select the File required so that it is the Active Workbook ( Active File )
Dim WBActive As Workbook ' Dim: ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense ) '
Set WBActive = ActiveWorkbook ' Set now to the Current active Workbook, - That being "looked at" when running this code , so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want... Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed. http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191Set ws = ActiveSheet ' Alternative to last line, make code apply to the current active sheet, - That being "looked at" when running this code '
Dim Ws1 As Worksheet: Set Ws1 = WBActive.Worksheets.Item(1) ' 'The Worksheets Collection Object of WBActive is used referrencing by Item number which is a consecutive count starting at 1 from the left and increasing by 1 as you count to the right. This Worksheet has the .Name Property applied to return the String Tab Name
'Dim Ws1Name As String ' Prepares "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But... http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
'Let Ws1Name = Ws1.Name
Rem 2 Build The string of the File name to be saved as:
Dim MyFileAW As String: Let MyFileAW = WBActive.Name 'The .Name Property of the Active Workbook returns the string name including the extension ( .xlsm, or .xls etc. )
Dim MyFileAWNameOnly As String: Let MyFileAWNameOnly = Left(MyFileAW, (InStrRev(MyFileAW, ".") - 1)) ' ' ActiveWorkbook Name without extension: 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.
Dim MyFileName As String: Let MyFileName = MyFileAWNameOnly & ".xlsx" 'Final required Full File Name

Rem 3 use the Copy Method applied to the fist worksheet of the Active Workbook to produce a copy Worksheet with the same name. Excel "houses this" automatically in a new File
Ws1.Copy ' A new Worksheet of the same name as Ws1 is housed in a new File which becomes the new Active Workbook
ActiveWorkbook.SaveAs Filename:=MyPathAW & MyFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'3b Close New created File
ActiveWorkbook.Close ' False '
End Sub

Hope that is some help. Sorry I did not get the point quicker
Let me know if you need more help.

Alan

**Flupsie** · 08-30-2016, 07:10 AM

Alan -Thanks for your help and detailed explanation

**DocAElstein** · 09-06-2016, 06:59 AM

Originally Posted by Flupsie

Alan -Thanks for your help and detailed explanation

Your welcome.

One last point that might help in the understanding here:

When you “work on a file”_.. you do not actually work on a file !!!

When you “open”” a file _... you do not actually open a file !!!
_..........................

With Excel in front of you, what you see, and what you have, the "thing" , is something which can pretty well do all that Excel has to offer. That includes data, Formatting, Worksheets, VBA Macros, Graphics, Charts -..... etc.. etc..

When you “Open” a file, the following is actually what happens:
A Copy is made of that file that you “open” as it stands at the point that you opened it. That copy is put into what you have in front of you and is represented as it would be in this “thing” which can do all that Excel has to offer. You can add Worksheets, add and run macros etc.. etc.. This is independent of what type of file you “open”.

What you have in front of you is not a file.

When you “save” , the following happens:

Excel looks at the type you save as ( for example .xlsx or .CSV or .xlsm or .xlsx _... etc. ). Excel then “takes” from what it has in front of you what it “needs” to create or overwrite the File of the name you give it.

So for examples,
by saving as .xlsm it takes just about everything
by saving as .xlsx it takes most except for any macro stuff.

By saving as .CSV it takes only data, nothing else. So it ignores any Worksheets or worksheet names you may have made or changed to / in the “thing” that was in front of you. - ( And as we have seen, in the case of a .CSV file, Excel has the annoying and confusing habit of putting the file name in the tab at the bottom in the “thing” in front of you when you “open” a .CSV file. After “opening” you could add and change the tabs at the bottom. But the value in it / those are only of significance if you save as some other type than .CSV, such as .xls or .xlsm or .xls etc.
By saving as .CSV it ignores anything written in any tabs. The .CSV File will only take and hold data, nothing else)

Alan

‘ Ref http://www.mrexcel.com/forum/excel-q...ml#post4425428

**snb** · 09-06-2016, 03:50 PM

or ?

Code:

Sub M_snb()
    Thisworkbook.Sheets(1).Copy
    With ActiveWorkbook
         .SaveAs ThisWorkbook.Name, 23
         .Close 0
     End With
End Sub

or

Code:

Sub M_snb()
    ThisWorkbook.Sheets(1).Copy
    With ActiveWorkbook
         .SaveAs CreateObject("scripting.filesystemobject").getbasename(ThisWorkbook.FullName), 23
         .Close 0
     End With
End Sub