From: PBcorn on
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
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
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
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
> > >