VB converting UK to US dates, randomly, ignores cdate func!
in [Programming]
|
Prev: Find "invisible" conditional formatting
Next: Alternate Column Shading to be applied only to visible columns
From: PBcorn on 28 May 2010 07:07 Written macro which processes csv files - splits into +ve & -ve values of one colum, sorts, adda couple of calculated columns. One of the cols (col index 5) is a date: dd/mm/yyyy, however excel is randomly converting some of these to american format. Using CDATE fuction makes no difference. The last step in the macro is the following code to attempt to correct this: ----------------------------------------------- with workbooks(workbookobjectname) For Each w In .Worksheets For i = 2 To w.UsedRange.Rows.Count w.Cells(i, 5) = CDate(DateSerial(Year(w.Cells(i, 5)), Month(w.Cells(i, 5)), Day(w.Cells(i, 5)))) Next i Next w end with ------------------------------- This does not work - why? Help appreciated. Thanks
From: Jacob Skaria on 28 May 2010 08:08 Try out this method for CSV import --In 2007 from menu Data>From Text and in 2003 from menu Data>Import External Data>Import Data ---select the .csv file to be opened. --From the 'Text Import Wizard' Step 1; hit Next>Next will take you to Step 3 of 3 of the Wizard. --From 'Column Data format' select 'Date' and select the date format in which your data is ('MDY' or 'DMY' or what ever it is). --Hit Finish. MSExcel will now convert the dates to the default date format of your computer. -- Jacob (MVP - Excel) "PBcorn" wrote: > Written macro which processes csv files - splits into +ve & -ve values of one > colum, sorts, adda couple of calculated columns. One of the cols (col index > 5) is a date: dd/mm/yyyy, however excel is randomly converting some of these > to american format. Using CDATE fuction makes no difference. The last step in > the macro is the following code to attempt to correct this: > ----------------------------------------------- > with workbooks(workbookobjectname) > > For Each w In .Worksheets > > For i = 2 To w.UsedRange.Rows.Count > w.Cells(i, 5) = CDate(DateSerial(Year(w.Cells(i, 5)), Month(w.Cells(i, > 5)), Day(w.Cells(i, 5)))) > Next i > > Next w > > end with > > ------------------------------- > > This does not work - why? Help appreciated. > > Thanks >
From: PBcorn on 28 May 2010 09:07 Thanks, However the macro also picks the correct csv file from a folder (there are more than one for each day due to more than one run number - macro selects the one corresponding to date entered by user and the latest run number). Looks like the import process adds another layer of complexity I was hoping to avoid. I was looking at the possibility of getting xls's instead of csv's at source as this would be the most efficient solution but if you have any code efficient way of doing the above with import process please let me know. Thanks again pb "Jacob Skaria" wrote: > Try out this method for CSV import > > --In 2007 from menu Data>From Text and in 2003 from menu Data>Import > External Data>Import Data ---select the .csv file to be opened. > --From the 'Text Import Wizard' Step 1; hit Next>Next will take you to Step > 3 of 3 of the Wizard. > > --From 'Column Data format' select 'Date' and select the date format in which > your data is ('MDY' or 'DMY' or what ever it is). > > --Hit Finish. MSExcel will now convert the dates to the default date format > of your computer. > > -- > Jacob (MVP - Excel) > > > "PBcorn" wrote: > > > Written macro which processes csv files - splits into +ve & -ve values of one > > colum, sorts, adda couple of calculated columns. One of the cols (col index > > 5) is a date: dd/mm/yyyy, however excel is randomly converting some of these > > to american format. Using CDATE fuction makes no difference. The last step in > > the macro is the following code to attempt to correct this: > > ----------------------------------------------- > > with workbooks(workbookobjectname) > > > > For Each w In .Worksheets > > > > For i = 2 To w.UsedRange.Rows.Count > > w.Cells(i, 5) = CDate(DateSerial(Year(w.Cells(i, 5)), Month(w.Cells(i, > > 5)), Day(w.Cells(i, 5)))) > > Next i > > > > Next w > > > > end with > > > > ------------------------------- > > > > This does not work - why? Help appreciated. > > > > Thanks > >
From: Jacob Skaria on 28 May 2010 10:49
The below macro will import a csvfile having the second field as date in DMY format to excel. If date is in 3rd column you can mention the array as Array(1, 1, xlDMYFormat, 1) Sub Macro2() Dim wb As Workbook,strFile As string strFile = "D:\new.csv" Set wb = Workbooks.Add With wb.Sheets(1).QueryTables.Add(Connection:="TEXT;" & strFile, _ Destination:=wb.Sheets(1).Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .TextFileColumnDataTypes = Array(1, xlDMYFormat, 1) .Refresh End With End Sub -- Jacob (MVP - Excel) "PBcorn" wrote: > Thanks, > > However the macro also picks the correct csv file from a folder (there are > more than one for each day due to more than one run number - macro selects > the one corresponding to date entered by user and the latest run number). > Looks like the import process adds another layer of complexity I was hoping > to avoid. I was looking at the possibility of getting xls's instead of csv's > at source as this would be the most efficient solution but if you have any > code efficient way of doing the above with import process please let me know. > > Thanks again > > pb > > "Jacob Skaria" wrote: > > > Try out this method for CSV import > > > > --In 2007 from menu Data>From Text and in 2003 from menu Data>Import > > External Data>Import Data ---select the .csv file to be opened. > > --From the 'Text Import Wizard' Step 1; hit Next>Next will take you to Step > > 3 of 3 of the Wizard. > > > > --From 'Column Data format' select 'Date' and select the date format in which > > your data is ('MDY' or 'DMY' or what ever it is). > > > > --Hit Finish. MSExcel will now convert the dates to the default date format > > of your computer. > > > > -- > > Jacob (MVP - Excel) > > > > > > "PBcorn" wrote: > > > > > Written macro which processes csv files - splits into +ve & -ve values of one > > > colum, sorts, adda couple of calculated columns. One of the cols (col index > > > 5) is a date: dd/mm/yyyy, however excel is randomly converting some of these > > > to american format. Using CDATE fuction makes no difference. The last step in > > > the macro is the following code to attempt to correct this: > > > ----------------------------------------------- > > > with workbooks(workbookobjectname) > > > > > > For Each w In .Worksheets > > > > > > For i = 2 To w.UsedRange.Rows.Count > > > w.Cells(i, 5) = CDate(DateSerial(Year(w.Cells(i, 5)), Month(w.Cells(i, > > > 5)), Day(w.Cells(i, 5)))) > > > Next i > > > > > > Next w > > > > > > end with > > > > > > ------------------------------- > > > > > > This does not work - why? Help appreciated. > > > > > > Thanks > > > |