Macro pastes dates in wrong format.
I am pasting data from a csv file to an Excel spreadsheet. When pasted without the macro dates are correct, but when pasted via a macro, days and months are converted to months and days giving incorrect date values.
I cannot find an appropriate command to change locale settings before pasting. Can you help please>
ROD WEBSTER
Welcome to the Forum!
Please show the code for the macro you're using. It would be even better to see both files you are working with.
Note that we require code tags around code:
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
To post a file:
- Under the text box where you type your reply click the Go Advanced button.
- In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
- In the pop-up, click Add then Select to select a file from your local drive
- Click the Upload button to upload the file
- Click Done to attach it.
It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.
Bunker Roof slab.csvThanks for your reply Jeff. Instead of posting the code, I have attached the two files in question. These are START.xlsm and Bunker Roof slab.csv.
The macro is run from the page named "Temp Input 1". The first date for input is the 9th of July 2013, but after copying in, it displays as the 7th of September. Of course, if you are in the USA, the above dates will probably come out differently. If you look at rows 39 and 40 on the "temp" page, the date should move on by one day, but it shows a jump of a month.
ROD WEBSTER
You haven't uploaded the workbook.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
So sorry. Here it is. Too large for upload.
ROD WEBSTERSTART.zip
Hi Trevor.
Did you get the workbook ok?
ROD
I have now discovered that pasting is not the problem. VBA changes some dates when opening a .csv file.
1) In the file which was attached (Bunker Roof slab.csv), dates are corrupted down to row 183, but are ok for rows 184 and greater.
2) When loading other similar .csv files that contain fewer columns, dates in more rows are corrupted, which suggests to me that the extent of the problem could be related to bandwidth.
3) VBA does NOT change any data when it opens an Excel (.xlsx) file.
This solves my particular problem, as all I need to do is save all .csv files as .xlsx. The pasting macro then generates the correct dates. The underlying problem with VBA and .csv files needs to be fixed though.
ROD WEBSTER
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks