Formatting Column After Saving as CSV in VBA

[Theoutlaw700]

I cannot get this to work. Essentially, after saving my worksheet to a csv I need all cells in range E:E to be formatted "[$-en-US]mm/dd/yy hh:mm AM/PM;@" My code looks as follows:

Sub SaveAsCSV()



Dim path As String
Dim fname As String


path = "Z:\Job Importing\"
fname = "This Week Schedule"


'Copy the invoice sheet to a new workbook

Sheet2.Copy


'Save the new workbook to a specified folder (This will be Z drive)
With ActiveWorkbook
    .Sheets(1).Name = "Weekly Schedule"
    .SaveAs Filename:=path & fname, FileFormat:=62
End With

    Range("E:E").NumberFormat = "[$-en-US]mm/dd/yy hh:mm AM/PM;@"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
'Close the Workbook

End Sub

An example of what my worksheet looks like this:

Technician	Customer	Customer ID	Job Type	Start Date [$-en-US]mm/dd/yy hh:mm AM/PM;@	Assignment type	Job Pool?	Description
Test	21st Century Healthcare	21st Century	Monthly Calibration	06/21/22 10:29 AM​	1		Test
Test	American Beverage Corp	American Beverage	Service	06/23/22 11:20 AM​	1		Test

Thank you very much ahead of time.

 

[Joe4]

Try changing the format of the column BEFORE you do the inital "SaveAs".
Then you should be able to get rid of the second "Save" line.

And make sure you open the CSV in a Text Editor (NOT EXCEL) to confirm that the format worked (once you open it in Excel, Excel will automatically change it to the format it wants).

 

[Theoutlaw700]

That still didn't work. I essentially want to save to a csv, open the new csv file, format Row E:E, save it and close.