Date format in VB created CSV file

[gaz_chops]

I have a small bit of code that copies a sheet from Excel and creates a CSV file and saves it, this works fine, however when I try and import the file into Xero (finance software), the date format in the CSV file has changed from UK to USA format!

In Excel it shows as

25/06/2021​

In newly created CSV file it shows as

6/25/2021

Anyway to retain the original date format?

Thanks
Gaz

 

[Fluff]

If you open the csv in a text editor, such as notepad, what is the date?

 

[gaz_chops]

If you open the csv in a text editor, such as notepad, what is the date?

This is in notepad

*Narration,*Date,Description,*AccountCode,*TaxRate,*Amount,TrackingName1,TrackingOption1,TrackingName2,TrackingOption2
Monthly Payroll Summary,6/25/2021,Gross Pay,6020,No Tax," 4,750.87 ",,,,
Monthly Payroll Summary,6/25/2021,Gross Pay,6029,No Tax," 7,195.72 ",,,,
Monthly Payroll Summary,6/25/2021,Gross Pay,6041,No Tax," 3,901.40 ",,,,

 

[Fluff]

Not sure what's going on. If I manually save a sheet as a csv the dates remain as UK dates, but doing the same thing from a macro converts the dates to US style.
I've used VBA to create csv files countless times & never had this problem.

 

[FormR]

HI, have you tried setting the "local" argument of the SaveAs method to TRUE (assuming you are using the SaveAs method to create the csv file!)?

 

[gaz_chops]

HI, have you tried setting the "local" argument of the SaveAs method to TRUE (assuming you are using the SaveAs method to create the csv file!)?

This is the code i am using, where would i add/amend it to include the "local" argument?

VBA Code:
Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Path & FileName, xlCSV
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

 

[Fluff]

Try

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

But you should avoid using VBA keywords (such as Path & Filename) as the names of variables.

 

[gaz_chops]

Try

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

But you should avoid using VBA keywords (such as Path & Filename) as the names of variables.

Thanks, i'll try it when in work tomorrow.

Gaz

 

[gaz_chops]

Try

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Path = "H:\Documents\Xero Reports\IRIS Payroll Data for Xero\"
    FileName = Range("G2").Value & ".csv"
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

But you should avoid using VBA keywords (such as Path & Filename) as the names of variables.

That doesn't appear to work, dates are still wrong in the CSV file!

*Narration    *Date    Description    *AccountCode
Monthly Payroll Summary    6/25/2021    Gross Pay    6020
Monthly Payroll Summary    6/25/2021    Gross Pay    6029

 

[gaz_chops]

OK it looks like i've fixed it, just for anyone else who may read this, I did the following -

I entered the formula =text(cell ref,"dd/mm/yyyy") in the cell that was picking up the date, it now shows in the correct format in the CSV file that is created.

*Narration    *Date    Description
Monthly Payroll Summary    25/06/2021    Gross Pay
Monthly Payroll Summary    25/06/2021    Gross Pay
Monthly Payroll Summary    25/06/2021    Gross Pay