Application

[Guest]

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
2005/12/31 1 4.46
2005/12/30 1 4.45
2005/12/29 1 4.45
2005/12/28 1 4.45
2005/12/27 1 4.44
2005/12/26 1 4.44
2005/12/25 1 4.44
2005/12/24 1 4.44
2005/12/23 1 4.44
2005/12/31 2 4.55
2005/12/30 2 4.54
2005/12/29 2 4.53
2005/12/28 2 4.53
2005/12/27 2 4.53
2005/12/26 2 4.53
2005/12/25 2 4.53
2005/12/24 2 4.53
2005/12/23 2 4.53
2005/12/31 3 4.61
2005/12/30 3 4.6
2005/12/29 3 4.59
2005/12/28 3 4.59
2005/12/27 3 4.58
2005/12/26 3 4.58
2005/12/25 3 4.58
2005/12/24 3 4.58
2005/12/23 3 4.58
2005/12/31 4 4.66
2005/12/30 4 4.65
2005/12/29 4 4.65
2005/12/28 4 4.64
2005/12/27 4 4.64
2005/12/26 4 4.64
2005/12/25 4 4.64
2005/12/24 4 4.64
2005/12/23 4 4.64

 

[Guest]

Chris,
Your example shows data of yyyy/mm/dd not mmddyy as stated
earlier; and you want to tranform columns into rows (and always/only 4
columns of data)?

 

[Nigel]

Sub LoadTextFile()
Dim myTextFile As String

'change path and name to suit your app
myTextFile = "C:\Path\FileName.txt"


Workbooks.OpenText Filename:= _
myTextFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=True _
, Space:=True, FieldInfo:=Array(Array(1, 5), Array(2, 1), Array _
(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
Columns("A:A").NumberFormat = "yyyy/mm/dd;@"
End Sub