VBA code generate CSV file, but changes date to incorrect format

ReinhardtSteyn

New Member
Joined
Mar 22, 2021
Messages
7
Office Version
  1. 365
Platform
  1. 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
1618691073196.png


This is how the date is generated in column G after making use of the macro.
1618691190781.png


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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
CSV files do not contain formats. When the csv file opens, in Excel, it converts dates to your regional short date format.
 
Upvote 0
The issue that once the CSV file is saved, the dates included in the CSV is changes from UK format to US format
Yes VBA is still in US whatever the local version …

Set the Local parameter of this Workbook.SaveAs method to True and see if that generates the exected result.

With an attachment of the workbook and the expected result text file I'll show you how to proceed without another worksheet …
 
Upvote 0
Yes VBA is still in US whatever the local version …

Set the Local parameter of this Workbook.SaveAs method to True and see if that generates the exected result.

With an attachment of the workbook and the expected result text file I'll show you how to proceed without another worksheet …
I changed the local parameter to true and still gave me the incorrect date format in the CSV file.
 
Upvote 0
I changed the local parameter to true and still gave me the incorrect date format in the CSV file.

Hi Marc

See below example of spreadsheet


Sharemilk Uitboeking TEST 02-21.xlsm
ABCDEFGHI
1Account Number Supplier Excl Supplier VAT Payments Excl Payments VAT General Journal Excl General Journal VAT Total Excl Total VAT
22000/003151 341.001 987.50----151 341.001 987.50
32000/010311 866.863 937.50----311 866.863 937.50
42000/02096 698.005 445.00----96 698.005 445.00
52000/03076 473.301 275.00----76 473.301 275.00
62000/040--------
72000/050201 620.00---(97 750.00)-103 870.00-
82000/060252 556.00637.50----252 556.00637.50
92000/070--------
102000/080201 620.00---(97 750.00)-103 870.00-
112000/090162 956.001 987.50----162 956.001 987.50
122000/099--------
132000/1101 035 726.0016 912.50----##########16 912.50
142000/120--------
152000/130--------
162000/140--------
172000/14143 045.80-----43 045.80-
182000/150176 625.0014 130.00----176 625.0014 130.00
192000/161938.30---(938.30)---
202050/003145 535.085.96--(39.71)-145 495.375.96
212050/010284 794.10-----284 794.10-
222050/020221 956.25-----221 956.25-
232050/030133 962.50-----133 962.50-
242050/040--------
252050/0501 500.00-92 944.50---94 444.50-
262050/060164 661.64-----164 661.64-
272050/070--------
282050/0803 842.26-98 396.35---102 238.61-
292050/090182 766.66-----182 766.66-
302050/099--------
312050/110275 719.04135.00----275 719.04135.00
322050/120195 615.3993.75----195 615.3993.75
332050/1304 230.00---(104 850.00)-##########-
342050/140--------
352050/141--8 820.001 323.00209 263.09-218 083.091 323.00
362050/150--172 258.19153.60672.56-172 930.75153.60
372050/160--------
382050/161----938.30-938.30-
392910/003--------
402910/010--------
412910/020--------
422910/030--------
432910/040--------
442910/050--------
452910/060--------
462910/070--------
472910/080--------
482910/090--------
492910/099--------
502910/110--------
512910/120--------
522910/130--------
532910/140--------
542910/150--------
552910/160--------
562910/161--------
572370/003--------
582370/010--------
592370/020--------
602370/030--------
612370/040--------
622370/050--------
632370/060--------
642370/070--------
652370/080--------
662370/090--------
672370/099--------
682370/110--------
692370/120--------
702370/130--------
712370/140--------
722370/150--------
732370/160--------
744350/003576.20-----576.20-
754350/0101 008.36-----1 008.36-
764350/020477.30-----477.30-
774350/030446.27-----446.27-
784350/040--------
794350/0501 946.48-----1 946.48-
804350/060905.26-----905.26-
814350/070--------
824350/0801 091.25-----1 091.25-
834350/090998.47-----998.47-
844350/1102 661.38-----2 661.38-
854350/1201 931.96-----1 931.96-
864350/130--------
874350/140--------
884350/141--------
894350/150--------
904350/160--------
914350/161--------
Exp Summary


Sharemilk Uitboeking TEST 02-21.xlsm
ABCDEFGHIJKLM
1Month789101112123456
2Period123456789101112
3Days in month313130313031312931303130
4Days accumulated316292123153184215244275305336366
5
6Month1
7Vul net datum in hierperiod
828/02/2021288
9215
10
11
12
13Period123456789101112
MTH INFO
Cell Formulas
RangeFormula
C1:G1,I1:M1C1=+B1+1
B4B4=+B3
C4C4=+C3+B3
D4:M4D4=+D3+C4
C9C9=+SUMIF(1:1,B6,4:4)


Sharemilk Uitboeking TEST 02-21.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Header GRA001828/02/2021Feb-21NFERTILIZER, SEED, WATER MAY 20 028/02/2021 1
2Detail311866.861.00311866.86311866.86 0002000007Fertiliser - Gradita6
3Detail3937.501.003937.503937.50 8002000007Fertiliser - Gradita6
4Detail96698.001.0096698.0096698.00 0002000007Fertiliser - No 2 Dairy 6
5Detail5445.001.005445.005445.00 8002000007Fertiliser - No 2 Dairy 6
6Detail76473.301.0076473.3076473.30 0002000007Fertiliser - Mangere6
7Detail1275.001.001275.001275.00 8002000007Fertiliser - Mangere6
8Detail0.001.000.000.00 0002000007Fertiliser - Vlakte6
9Detail0.001.000.000.00 8002000007Fertiliser - Vlakte6
10Detail103870.001.00103870.00103870.00 0002000007Fertiliser - Heemstede6
11Detail0.001.000.000.00 8002000007Fertiliser - Heemstede6
12Detail252556.001.00252556.00252556.00 0002000007Fertiliser - Veelgeluk6
13Detail637.501.00637.50637.50 8002000007Fertiliser - Veelgeluk6
14Detail103870.001.00103870.00103870.00 0002000007Fertiliser - Riverina6
15Detail0.001.000.000.00 8002000007Fertiliser - Riverina6
16Detail162956.001.00162956.00162956.00 0002000007Fertiliser - Kenmore6
17Detail1987.501.001987.501987.50 8002000007Fertiliser - Kenmore6
18Detail1035726.001.001035726.001035726.00 0002000007Fertiliser - Valley6
19Detail16912.501.0016912.5016912.50 8002000007Fertiliser - Valley6
20Detail0.001.000.000.00 0002000007Fertiliser - Palmietvlei6
21Detail0.001.000.000.00 8002000007Fertiliser - Palmietvlei6
22Detail284794.101.00284794.10284794.10 0002050007Seed & Herbicide - Gradita 6
23Detail0.001.000.000.00 8002050007Seed & Herbicide - Gradita 6
24Detail221956.251.00221956.25221956.25 0002050007Seed & Herbicide - No 2 Dairy 6
25Detail0.001.000.000.00 8002050007Seed & Herbicide - No 2 Dairy 6
26Detail133962.501.00133962.50133962.50 0002050007Seed & Herbicide - Mangere 6
27Detail0.001.000.000.00 8002050007Seed & Herbicide - Mangere 6
28Detail0.001.000.000.00 0002050007Seed & Herbicide - Vlakte 6
29Detail0.001.000.000.00 8002050007Seed & Herbicide - Vlakte 6
30Detail94444.501.0094444.5094444.50 0002050007Seed & Herbicide - Heemstede 6
31Detail0.001.000.000.00 8002050007Seed & Herbicide - Heemstede 6
32Detail164661.641.00164661.64164661.64 0002050007Seed & Herbicide - Veelgeluk 6
33Detail0.001.000.000.00 8002050007Seed & Herbicide - Veelgeluk 6
34Detail102238.611.00102238.61102238.61 0002050007Seed & Herbicide - Riverina 6
35Detail0.001.000.000.00 8002050007Seed & Herbicide - Riverina 6
36Detail182766.661.00182766.66182766.66 0002050007Seed & Herbicide - Kenmore 6
37Detail0.001.000.000.00 8002050007Seed & Herbicide - Kenmore 6
38Detail275719.041.00275719.04275719.04 0002050007Seed & Herbicide - Valley 6
39Detail135.001.00135.00135.00 8002050007Seed & Herbicide - Valley 6
40Detail195615.391.00195615.39195615.39 0002050007Seed & Herbicide - Palmietvlei 6
41Detail93.751.0093.7593.75 8002050007Seed & Herbicide - Palmietvlei 6
42Detail0.001.000.000.00 0002370007Water - Heemstede 6
43Detail0.001.000.000.00 8002370007Water - Heemstede 6
44Detail0.001.000.000.00 0002370007Water - Riverina 6
45Detail0.001.000.000.00 8002370007Water - Riverina 6
46Detail0.001.000.000.00 0002370007Water - Vlakte 6
47Detail0.001.000.000.00 8002370007Water - Vlakte 6
48Detail0.001.000.000.00 0002910007Water Levy - Gradita 6
49Detail0.001.000.000.00 8002910007Water Levy - Gradita 6
50Detail0.001.000.000.00 0002910007Water Levy - Kenmore 6
51Detail0.001.000.000.00 8002910007Water Levy - Kenmore 6
52Detail0.001.000.000.00 0002910007Water Levy - Mangere 6
53Detail0.001.000.000.00 8002910007Water Levy - Mangere 6
54Detail0.001.000.000.00 0002910007Water Levy - No 2 Dairy 6
55Detail0.001.000.000.00 8002910007Water Levy - No 2 Dairy 6
56Detail0.001.000.000.00 0002910007Water Levy - Palmietvlei 6
57Detail0.001.000.000.00 8002910007Water Levy - Palmietvlei 6
58Detail0.001.000.000.00 0002910007Water Levy - Valley 6
59Detail0.001.000.000.00 8002910007Water Levy - Valley 6
60Detail0.001.000.000.00 0002910007Water Levy - Veelgeluk 6
61Detail0.001.000.000.00 8002910007Water Levy - Veelgeluk 6
62Detail1008.361.001008.361008.36 0004350007Rates - Gradita6
63Detail0.001.000.000.00 8004350007Rates - Gradita6
64Detail477.301.00477.30477.30 0004350007Rates - No 2 Dairy 6
65Detail0.001.000.000.00 8004350007Rates - No 2 Dairy 6
66Detail446.271.00446.27446.27 0004350007Rates - Mangere6
67Detail0.001.000.000.00 8004350007Rates - Mangere6
68Detail0.001.000.000.00 0004350007Rates - Vlakte6
69Detail0.001.000.000.00 8004350007Rates - Vlakte6
70Detail1946.481.001946.481946.48 0004350007Rates - Heemstede6
71Detail0.001.000.000.00 8004350007Rates - Heemstede6
72Detail905.261.00905.26905.26 0004350007Rates - Veelgeluk6
73Detail0.001.000.000.00 8004350007Rates - Veelgeluk6
74Detail1091.251.001091.251091.25 0004350007Rates - Riverina6
75Detail0.001.000.000.00 8004350007Rates - Riverina6
76Detail998.471.00998.47998.47 0004350007Rates - Kenmore6
77Detail0.001.000.000.00 8004350007Rates - Kenmore6
78Detail2661.381.002661.382661.38 0004350007Rates - Valley6
79Detail0.001.000.000.00 8004350007Rates - Valley6
80Detail1931.961.001931.961931.96 0004350007Rates - Palmietvlei6
81Detail0.001.000.000.00 8004350007Rates - Palmietvlei6
BDF - Supplier Milkshare Import
Cell Formulas
RangeFormula
F1F1=+'MTH INFO'!E8
G1G1=+'MTH INFO'!B8
H1H1=+'MTH INFO'!B8
U1U1=+G1
D2:D81D2=+E2
E2E2=+'Exp Summary'!H3
E3E3=+'Exp Summary'!I3
E4E4=+'Exp Summary'!H4
E5E5=+'Exp Summary'!I4
E6,E22E6=+'Exp Summary'!H5
E7,E23E7=+'Exp Summary'!I5
E8,E24,E50E8=+'Exp Summary'!H6
E9,E25,E51E9=+'Exp Summary'!I6
E10,E26E10=+'Exp Summary'!H7
E11,E27E11=+'Exp Summary'!I7
E12,E14,E28E12=+'Exp Summary'!H8
E13,E15,E29E13=+'Exp Summary'!I8
E16,E18,E30,E56E16=+'Exp Summary'!H11
E17,E19,E31,E57E17=+'Exp Summary'!I11
E20,E32,E34E20=+'Exp Summary'!H14
E21,E33,E35E21=+'Exp Summary'!I14
E36,E38E36=+'Exp Summary'!H29
E37,E39E37=+'Exp Summary'!I29
E40,E48,E58E40=+'Exp Summary'!H32
E41,E49,E59E41=+'Exp Summary'!I32
E42E42=+'Exp Summary'!H62
E43E43=+'Exp Summary'!I62
E44E44=+'Exp Summary'!H65
E45E45=+'Exp Summary'!I65
E46E46=+'Exp Summary'!H61
E47E47=+'Exp Summary'!I61
E52E52=+'Exp Summary'!H42
E53E53=+'Exp Summary'!I42
E54E54=+'Exp Summary'!H41
E55E55=+'Exp Summary'!I41
E60E60=+'Exp Summary'!H45
E61E61=+'Exp Summary'!I45
E62E62='Exp Summary'!$H$75
E63E63=+'Exp Summary'!$I$75
E64E64='Exp Summary'!$H$76
E65E65=+'Exp Summary'!$I$76
E66E66='Exp Summary'!$H$77
E67E67=+'Exp Summary'!$I$77
E68E68='Exp Summary'!$H$78
E69E69=+'Exp Summary'!$I$78
E70E70='Exp Summary'!$H$79
E71E71=+'Exp Summary'!$I$79
E72E72='Exp Summary'!$H$80
E73E73=+'Exp Summary'!$I$80
E74E74='Exp Summary'!$H$82
E75E75=+'Exp Summary'!$I$82
E76E76='Exp Summary'!$H$83
E77E77=+'Exp Summary'!$I$83
E78E78='Exp Summary'!$H$84
E79E79=+'Exp Summary'!$I$84
E80E80='Exp Summary'!$H$85
E81E81=+'Exp Summary'!$I$85
B2:B81B2=+D2
 
Upvote 0
Should I confess I'm a bit lost without the expected result text file as required …​
 
Upvote 0
Is there only a single date in cell G1 - not in UK format but french ! - but anywhere else ? (edit : cell U1 also)​
What happens if you replace the cells formatted as Date by some text formatting ?​
 
Last edited:
Upvote 0
  • I just saved your data on my side from Excel manually as a .csv text file : the original format dd/mm/yyyy is the same within the text file …

  • Using your codeline the date is converted to the mm/dd/yyyy (for me it was also a UK format) like you have well described …

  • Amending this codeline with the Local parameter to True the date stays in dd/mm/yyyy format as I stated.
⚠️ Do not open the csv text file in Excel to check the date format as Excel may convert the date !
You must use Notepad, Notepad++, whatever … any text editor to be sure but not Excel.​
So as the Local parameter to True uses the Windows locals settings, what are those settings on your side ?​
 
Upvote 0
I also did some research and added a number format before my CSV files performs the SaveAs. It is working now.

Thank you for all your help
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top