Excel vba how can extract only a column from a csv into spreadsheet

Question

I am new to VBA. How can I extract the only column from the CSV data and place it into anywhere in the spreadsheet?

I have a CSV file that looks like this that came from an API/URL

A,B,C,D,E
1,2,3,4,5
6,7,8,9,10
11,12,13,14
14,15,16,17

I have this code but it displays all the CSV data. How can I get only one or two columns from the data and place it anywhere in the spreadsheet?

Sheets(1).Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=True, Space:=False, Other:=False, _
FieldInfo:= _
Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))

Your question is very unclear on specifics. What 1 column and/or 2 columns? What destination is 'anywhere in the spreadsheet'? — user4039065
– user4039065, Commented Jul 31, 2018 at 0:36
@Jeeped I will update the question. I want any of the columns and anywhere in the spreadsheet — John
– John, Commented Jul 31, 2018 at 1:12

user6585761 · Accepted Answer · 2018-07-31 01:01:59Z

3

For each of the two element array included in FieldInfo, the first element specifies the column number (1 based), and the second element specifies the data type. To skip a column, set the second element or data type to 9. So, for example, let's say you want to skip columns 1, 3, and 4, you'll need to set it as follows...

FieldInfo:=Array(Array(1, 9), Array(2, 2), Array(3, 9), Array(4, 9), Array(5, 2))

answered Jul 31, 2018 at 1:01

user6585761

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

And adding the Destination:= argument can determine the destination.

Collectives™ on Stack Overflow

Excel vba how can extract only a column from a csv into spreadsheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related