Save Range as CSV and update range format in CSV before saving.

[Hyperion1571]

Hello all,

I have the below code however i need to edit the CSV data before it saves as currently the copy and past section looses the formating.

Sub save_to_CSV()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range

    Application.DisplayAlerts = False
    On Error GoTo err

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "Staffingfile-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

    Set rngToSave = Range("B2:F300")
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
     
err:
    Application.DisplayAlerts = True
    
End Sub

This is the section that im looking to do in the CSV the following

    Columns("D:D").Select
    Selection.NumberFormat = "yyyy-mm-dd"
'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "CountryCode"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "StationCode"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "TrainingName"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Capacity"

Thanks for your help.

[davsth]

A sample workbook would be much clear with what you want to achieve. see the yellow banner

You are pasting as values with no formatting and losing formatting. Are you trying to repeat the formats from the original document? If so why not paste the formats as well?

[jindon]

If the original data is formatted properly then just try remove xlPasteValues.

[Hyperion1571]

Thanks Jindon, The data in the origional document it correct but is prone to other users changing headings or format. What im attempting to do is merge the first and second set of code into one macro so the process would be,
Copy and Past range from core document to new document > Edit Formating and headings > Save under the new file name.

Thanks in advance.

[jindon]

If you want to combine 2 codes then should be look like(untested)

Sub save_to_CSV()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range

    Application.DisplayAlerts = False

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "Staffingfile-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

    Set rngToSave = Range("B2:F300")
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        With .Sheets(1)
            .Range("A1").PasteSpecial
            .Range("a1:e1") = Array("CountryCode", "StationCode", "TrainingName", "Date", "Capacity")
            .Columns("d").NumberFormat = "yyyy-mm-dd"
        End With
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
    Application.DisplayAlerts = True
    
End Sub

[Hyperion1571]

Thanks jindon
All seems to work however the date format still comes up as UK Date "dd/mm/yyyy" cont figure out why it doesnt conform to the format stated

[jindon]

That means that column is Text date, not Serial date, I guess.

Or just realized that "Date" Column is "E" in new workbook...