Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB converting UK to US dates, randomly, ignores cdate func!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB converting UK to US dates, randomly, ignores cdate func!
Try out this method for CSV import --In 2007 from menu DataFrom Text and in 2003 from menu DataImport External DataImport Data ---select the .csv file to be opened. --From the 'Text Import Wizard' Step 1; hit NextNext 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB converting UK to US dates, randomly, ignores cdate func!
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 DataFrom Text and in 2003 from menu DataImport External DataImport Data ---select the .csv file to be opened. --From the 'Text Import Wizard' Step 1; hit NextNext 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB converting UK to US dates, randomly, ignores cdate func!
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 DataFrom Text and in 2003 from menu DataImport External DataImport Data ---select the .csv file to be opened. --From the 'Text Import Wizard' Step 1; hit NextNext 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cdate does not help | Excel Programming | |||
Converting Julian Dates to regular dates | Excel Worksheet Functions | |||
Converting Text dates into dates | Excel Programming | |||
VBA Function that ignores dates in a Holiday Table | Excel Worksheet Functions | |||
CDate and IsDate | Excel Programming |