2

Using VBA I import a csv file into excel from Yahoo Finance containing 7 comma separated values per row. I would like to only import the first and 5th comma separated value of each row. Currently I import the entire csv, extract it to columns and then delete the unwanted columns. However this does not suffice for future uses of this import. How can I make a selection of the columns I want to import??

Current code:

qURL = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol & "&a=" & Month(Startdate) - 1 & "&b=" & Day(Startdate) & _
        "&c=" & Year(Startdate) & "&d=" & Month(Enddate) - 1 & "&e=" & Day(Enddate) & "&f=" & Year(Enddate) & "&g=" & QuoteInterval & "&ignore=.csv"

        With Sheets(2).QueryTables.Add(Connection:="URL;" & qURL, Destination:=ActiveCell.Offset(1, 0))
            .BackgroundQuery = True
            .TablesOnlyFromHTML = False
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With

            Sheets(2).Range("A2:A" & Lastrow).TextToColumns Destination:=Sheets(2).Range("A2"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, other:=False
            Columns(2).EntireColumn.Delete
            Columns(2).EntireColumn.Delete
            Columns(2).EntireColumn.Delete
            Columns(3).EntireColumn.Delete
            Columns(3).EntireColumn.Delete
            Range("A3:A" & Lastrow).NumberFormat = "dd mmm yy"
            Range("B3:B" & Lastrow).NumberFormat = "0.00"
Vincent van V
  • 23
  • 1
  • 5
  • I'm not sure if there is a better solution so I'm leaving this as a comment, delete your columns from the largest index first, as in go backwards. That way it will make your column deletion much cleaner. You won't have the multiple `Columns(2)` and `Columns(3)`. Just do a delete 7,6,4,3,2. – Bmo Dec 27 '15 at 21:54

1 Answers1

1

You can supply an array as the FieldInfo parameter to TextToColumns

This will hide columns 2,3,4,6,7

.TextToColumns FieldInfo := Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 1), Array(6, 9), Array(7, 9))

1 = xlGeneralFormat
9 = xlSkipColumn

WhiteHat
  • 59,912
  • 7
  • 51
  • 133
  • Thanks, looks like the right track. However, this code skips the same number of columns as the number of arrays in the array starting at the first column, regardless what I fill out in the arrays. So for example, the .csv is: A,B,C,D,E,F,G, with the code you provided it skips columns A to E (because the array consists of 5 arrays) and puts F and G in columns. Consequently, with an array consisting of 4 arrays it skips the first 4 columns etc.I have tried every possibility but it starts at the first column no matter what. – Vincent van V Dec 28 '15 at 16:26