Import with troubles - Date format

[gohan123]

Hi, guys,


I trying to import a cvs file. I have sucess with import using a forums trips, but I need to format the columns C and D to date format (dd/mm/yyyy hh:mm) and I don't know how to do that. I Tried everything that a know... Please, Help Me..



Private Sub import()
Dim ImportFile As Variant

spath = CurDir
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path

On Error GoTo ws_exit
Application.EnableEvents = False

ImportFile = Application.GetOpenFilename(filefilter:="csv Files (*.csv),*.cvs", _
MultiSelect:=False)

Workbooks.Open Filename:=ImportFile

Workbooks.OpenText Filename:=ImportFile, Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, xlDMYFormat, 1, xlDMYFormat), Array(4, xlDMYFormat, 1, _
xlDMYFormat), Array(5, 1), Array(6, 1), Array(7, 1)), _
ThousandsSeparator:=Period, TrailingMinusNumbers:=True

Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D:D"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, xlDMYFormat, 4, xlDMYFormat), TrailingMinusNumbers:=True


ws_exit:
Application.EnableEvents = True
End Sub



Edited 12-Jun-07 by geekgirlau. Reason: insert line breaks

[geekgirlau]

Provided the dates are being recognised correctly, you use Format | Cells to set the date format you want to display - you should be able to record this and copy and paste the code into your macro.

[gohan123]

Hi,

I formated the column used a code but I need to get in a cell and press enter to cell know the number format.?

I'm trying to import a CVS file, How I format the data like import assist in excel (using a import assistence it's work).

Tks

[geekgirlau]

Once the data is imported, you apply the format. If you were doing this manually, you would select columns C and D, select Format | Cells and add a custom number format of "dd/mm/yyyy hh:mm". In your macro, you just need to add this line:

Columns("C:D").NumberFormat = "dd/mm/yyyy hh:mm"

after the data is imported - applying the number format is NOT part of importing, it's a step that you perform afterwards.