Formatting csv file

[picklefactory]

Hi folks, I'm back again.
Working through a new addition to an existing WB. I am creating and saving a range of values as .csv file, which is the required format to upload to a suppliers portal. No problem so far, but what has me stumped is just formatting the cells in the .csv to simply align all columns left and autofit the column widths to suit the values, I'm only using 3 columns A:C. I have tried numerous things, all of which have failed spectacularly..... well not so spectacular, just a whole lot of absolutely nothing happening at all with regard to the formatting.

This was my latest failure, I've tried various options, I get no errors, it creates and saves the .csv fine, it just ignores the formatting completely.
Can anyone point where I'm going wrong, please?

Sub Export_to_CSV()
   
    Dim MyPath As String
    Dim MyFileName As String
    MyPath = "M:\COMPANY SHARED\wages 2\CSV Exports\"
    MyFileName = Worksheets("NEW PAYROLL").Range("K1").Text
   
    Application.ScreenUpdating = False
      
    Sheets("NEW PAYROLL").Select
    Range("L1:N40").Select
    Selection.Copy
   
    With Workbooks.Add(xlWBATWorksheet)
        .Sheets(1).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Application.DisplayAlerts = False
        .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
        Range("A1:C50").EntireColumn.AutoFit
        ActiveSheet.Cells.HorizontalAlignment = xlLeft
        .Close False
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

 

[Joe4]

formatting the cells in the .csv to simply align all columns left and autofit the column widths to suit the values

Can you show us an example of:
1. What this data looks like in Excel?
2. What it needs to look like on your CSV?
3. What it looks like in your current CSV?

Just a line or two of data so we can get a better understanding of exactly what you are working with and what you need.

 

[picklefactory]

This is how it looks after export, I've had to use an image as XL2BB seems to auto format it here

And I simply want to tidy up the formatting by aligning left and auto fitting the column widths to suit the character length, so it looks like this

604	BASIC HOURS	23
222	BASIC HOURS	36
397	SHIFT ALLOWANCE	54

 

[Joe4]

None of those is a CSV view. The last two things I asked for were CSV views.
Note that a CSV file is a Text file, not an Excel file.

You should NEVER use Excel to view the contents of a CSV file if you really want to see what the contents of the CSV really look like. Excel performs its own automated conversions on data upon opening the file (such as dropping trailing zeroes on decimal values, etc).
ALWAYS view the contents of a CSV file using a Text Editor like NotePad if you truly want to see what the contents are.

It should look something like this:

604,BASIC HOURS,23
222,BASIC HOURS,36
397,SHIFT ALLOWANCE,54

So if you open your CSV file with NotePad, what does it actually look like, and what does it need to look like?

 

[picklefactory]

Ah, sorry Joe, I never work with CSV files, didn't know that, I have been opening it up in Excel. In that case, please ignore this question.... turns out it was one of my dafter ones. I've just opened it in a text editor and it looks fine. Sorry for the waste of your time and thank you for looking. I'll mark as resolved

 

[Joe4]

Ah, sorry Joe, I never work with CSV files, didn't know that, I have been opening it up in Excel. In that case, please ignore this question.... turns out it was one of my dafter ones. I've just opened it in a text editor and it looks fine. Sorry for the waste of your time and thank you for looking. I'll mark as resolved

No worries!
You aren't the first person to make that mistake. It is not all that uncommon.
I think part of the problem is that someone at Microsoft determined that Excel should be the default program to open CSV files, which I think is a mistake.
One of the first things I do when I get a new computer is change that default from Excel to NotePad.

 

[picklefactory]

One of the first things I do when I get a new computer is change that default from Excel to NotePad

I will do that now too
Thanks again Joe