CODEWITHSUNDEEP
vbaexcel
Pavi
Pavi

Reputation: 345

Macro is changing tab name

I have excel files in a folder. I want to convert each excel file(only sheet1) to csv file. I have done this by using below Macro. I am not able to figure which line in the code changing tab name with file name. I dont want to change tab name. 

Option Explicit
Sub SaveasCSV()
Dim bookList As Workbook
Dim createObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set createObj = CreateObject("Scripting.FileSystemObject")
ChDir "C:\Users\puttaraju.d\Desktop\Phase1"
Set dirObj = createObj.GetFolder("C:\Users\puttaraju.d\Desktop\Phase1")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
ActiveWorkbook.SaveAs FileFormat:=xlCSV, CreateBackup:=False 
bookList.Close
Next
End Sub

For every file, pop up open for asking do u want to save changes in file. I dont want to change current file. how can i add line of code to do that.

Upvotes: 0

Views: 557

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38530

This is the line:

ActiveWorkbook.SaveAs FileFormat:=xlCSV

Try manually saving a workbook as CSV. You will see that it changes the tab name to match the filename (without the .csv extension).

What you can do is save a temporary copy of your worbook using SaveCopyAs, then open that copy, and save it as CSV (and close it and delete it).

Here's an old procedure of mine that does something similar, using a different approach. It saves a sheet as text: the sheet is copied to another temp workbook, then that workbook is saved as text. Adapt to suit your needs.

Sub SaveSheetAsTextFile(sourceSheet As Worksheet, textFilePath As String)
    Dim oldScreenUpdating As Boolean
    Dim thisbook As Workbook
    Dim tempbook As Workbook

    oldScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    ' Copy sheet to a new temp workbook
    Set thisbook = ActiveWorkbook
    Set tempbook = Workbooks.Add
    thisbook.Activate
    sourceSheet.Cells.Copy tempbook.ActiveSheet.Range("A1")

    ' Save as text file
    tempbook.ActiveSheet.SaveAs filename:=textFilePath, _
                                FileFormat:=xlTextPrinter
    'FileFormat:=xlTextWindows  ' this puts text cells in "quotes"!

    tempbook.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
End Sub

Example usage:

SaveSheetAsTextFile Sheet1, "C:\myfile.txt"

Upvotes: 2

Related Questions