Macro to save sheet as a CVS

[howard]

I have the following code to save as sheet as a CSV and ensuring that the cols are autofitted , but I cannot get the cols in the CSV to be autofitted

The Cols on Sheet "Settlements" on the Excel workbook are all autofitted so I can't understand why the cols the CSV file is not autofitted



It would be appreciated if someone could amend my code so that the cols are autofitted

 Sub SaveSheetAsCSVWithAutofit()
    Dim ws As Worksheet
    Dim newWB As Workbook
    Dim savePath As String
    
    ' Set the worksheet to be saved as CSV
    Set ws = ThisWorkbook.Sheets("Settlements")  ' Change the sheet name as needed
    
    ' Create a new workbook
    Set newWB = Workbooks.Add
    
    ' Copy data to new workbook
    ws.Cells.Copy newWB.Sheets(1).Range("A1")
    
    ' Autofit columns in the new workbook (won't affect CSV file)
    newWB.Sheets(1).Cells.EntireColumn.AutoFit
    
    ' Specify the save path and filename
    savePath = "C:\My Documents\MRTY Settlement.csv"  ' Update with your desired path
    
    ' Save as CSV file
    newWB.SaveAs Filename:=savePath, FileFormat:=xlCSV, CreateBackup:=False
    
    ' Close and save changes to the new workbook
    newWB.Close SaveChanges:=True
End Sub

 

[Joe4]

but I cannot get the cols in the CSV to be autofitted

What exactly do you mean by this? A CSV file is a straight text file with NO formatting. Each entry should be shown in its entirety separated by commas.

How exactly are you trying to view your CSV? Are you trying to view it in Excel?
You should NEVER use Excel to view a CSV or any other type of text file, as Excel will automatically do its own conversions/formatting.
If you TRULY want to see what the contents of a CSV or any other text file look like, you should view it in a Text Editor, like Note Pad.
This will show the file contents, exactly as they exist.

 

[howard]

I have an Excel workbook with several Sheet. I need to Export the sheet "Settlements" and save this as C:\My Documents\MRTY Settlement.csv

I need to email the CSV file but the cols are not all wide enough.

 

[Joe4]

I need to email the CSV file but the cols are not all wide enough.

How exactly are you making that determination?

If you export the sheet to a CSV file, and open that CSV file in NotePad (do NOT open it in Excel!!!), what does the file look like?

Note that a real CSV file has no columns! Just data separated by commas! It is only when you open it in Excel does it put it into columns (and Excel will do other automated data conversions to the data).

When you open a CSV in Excel, you are really doing data conversions on it, whether you know it or not. That is why you never want to use Excel to view a CSV file if you really want to see what it looks like (unaltered by Excel).

 

[howard]

When opening in Notepad, it is perfect

 

[Joe4]

When opening in Notepad, it is perfect

Then it is perfect!
NotePad shows it as it really appears!
Excel will perform its own manipulations on it automatically every time you open it.
Which is why you do NOT want to open the CSV in Excel to view it.