I'm having major problems opening a .csv file using a macro. When opened 'manually' through windows explorer the data is correct, however, when I run a simple macro to do the same thing, dates get swapped from dd/mm/yyyy to mm/dd/yyyy, but only in certain cells in the same column. this is not down to cell formatting, as all cell formats are set the same.. Any help would be MUCH appreciated!
Welcome to the board
Any chance of posting an example workbook (minus any confidential information) ?
I think you've run into Bill Gates' rule that our American date format mm/dd/yyyy is the only correct format :biggrin:
I'm assuming that your regional settings are dd/mm/yyyy and the csv file you are opening is in the same format. Probably every date with a day value of 12 or less comes across in mm/dd/yyyy format and the dates with days greater than 12 come across in dd/mm/yyyy format (although, if you check them, they are probably just text and not actual dates)
One workaround I found is to change the file extension from .csv to .txt and open the text file in Excel. That will start the text import wizard and you can specify comma as the delimiter, then specify the actual format for each column. If you specify "date" where needed, all the values should come over with the correct format. You can also specify text, numeric, etc for the other columns were appropriate.
Use the macro recorder when you do this and you'll have the correct code for putting in your VBA module.
Hope this helps
I have a similar date problem with cvs to excel imports. In my case I'm pulling retail unit numbers, no problem if the unit is 1,2 ,3 etc… but if the entry is units "4-5" I get 5/4/2003.
My solution is to loop thru the column after import from the csv. ie:
for x=1 to 1000
if isdate(cells(x,10).value) then
Perhaps your solution is also to loop thru the column after importing ie:
if isdate(cells(x,1).value) then
You would have to modify & test the above based on the specific conversion errors you are getting.