ReinhardtSteyn
New Member
- Joined
- Mar 22, 2021
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
Good Day
I created a simple macro code to generate a new excel sheet and copy data from another sheet to this new generated sheet and save this new sheet as a CSV file for import purposes into a specific accounting software. The issue that once the CSV file is saved, the dates included in the CSV is changes from UK format to US format and the accounting software does not recognise the US format as the server on which the account software is loaded date format is set to UK format.
The original spreadsheet where the data is collected from for import purposes date format is set to UK format. If I do the copy and paste exercise manually instead of making use of macro, the date format saves perfectly. Please see below the coding that I made use of:
Sub Generate_CSV()
Dim MonthDate As String: MonthDate = ThisWorkbook.Name
Dim File_Directory As String: File_Directory = ThisWorkbook.Path
Dim File_Date As String: File_Date = Mid(MonthDate, 22, 5)
'Generate Customer Invoice - Import
ThisWorkbook.Sheets("Customer Invoice - Import").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=File_Directory & "\CUSTOMER IMPORT " & File_Date & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
This is how the data looks before the macro is run
This is how the date is generated in column G after making use of the macro.
I do not know why the date format is changing when I make use of the macro to generate my CSV import file. Some input on the matter will be appreciated.
I created a simple macro code to generate a new excel sheet and copy data from another sheet to this new generated sheet and save this new sheet as a CSV file for import purposes into a specific accounting software. The issue that once the CSV file is saved, the dates included in the CSV is changes from UK format to US format and the accounting software does not recognise the US format as the server on which the account software is loaded date format is set to UK format.
The original spreadsheet where the data is collected from for import purposes date format is set to UK format. If I do the copy and paste exercise manually instead of making use of macro, the date format saves perfectly. Please see below the coding that I made use of:
Sub Generate_CSV()
Dim MonthDate As String: MonthDate = ThisWorkbook.Name
Dim File_Directory As String: File_Directory = ThisWorkbook.Path
Dim File_Date As String: File_Date = Mid(MonthDate, 22, 5)
'Generate Customer Invoice - Import
ThisWorkbook.Sheets("Customer Invoice - Import").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=File_Directory & "\CUSTOMER IMPORT " & File_Date & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
This is how the data looks before the macro is run
This is how the date is generated in column G after making use of the macro.
I do not know why the date format is changing when I make use of the macro to generate my CSV import file. Some input on the matter will be appreciated.