Hey there.
This problem is giving me a major headache and no post I have read can
seem to help with the problem I am having.
I have some code to copy any present data from CSV files to a main
workbook (basebook) but some seem to paste over as mm/dd/yy instead of
dd/mm/yy...
Two of the CSV's that import differently have the following formats
when I open them through Excel:
06/10/2010 07:00:00 (custom - dd/mm/yyyy hh:mm) - copies to Excel as
10/06/2010 07:00:00
31/08/2010 13:00:00 (custom - dd/mm/yyyy hh:mm) - copies to Excel as
31/08/2010 13:00:00
So the first is obviously wrong and I can change the formatting in
Excel, just not to what I want as that would involve swapping the day
with the month...
The second is as I want it and the format cannot be changed in Excel,
which is fine.
I have read that it may be down to regional settings but I am unable
to give a fix for this as the CSV files come in from different users
and we have a load of them that cannot be redone.
I have tried changing the code to paste as values only and various
other fixes but am so far unsuccessful. I have also read that I could
convert the CSV's to .txt but I have no experience with this through
code and would rather not if avoidable.
The code which copies the data is below. Any help would be really
appreciated. I even started to consider if there is a way to just swap
the day with the month through VBA as the ones that are correct seem
to be stuck that way anyway but I have no idea how to accomplish it...
'Define the SourceRange
With mybook.Worksheets(1)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'Now you know where the list (to be copied) ends
Set sourceRange = mybook.Worksheets(1).Range("A2", "A" &
LastRow).EntireRow 'Sets the range to copy.
End With
'Define where to put the source values
With basebook.Worksheets("Summary")
'Establish the last used row in the target ws
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'Copy the whole lot over starting from row that's =lastRow + 1
sourceRange.Copy
basebook.Worksheets("Summary").Cells(LastRow + 1,
"A").PasteSpecial (xlPasteValues)
' basebook.Worksheets("Summary").Columns("B:C").NumberFormat =
"mm/dd/yyyy hh:mm:ss"
End With
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
