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

**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