VBA:仅导入csv文件的所选列 [英] VBA: Only import selected columns of a csv file
问题描述
使用VBA我从Yahoo Finance导入一个csv文件到excel,每行包含7个逗号分隔值。我想只导入每行的第一个和第五个逗号分隔的值。目前我导入整个csv,将其解压到列,然后删除不需要的列。然而,这不足以供将来使用此导入。如何选择要导入的列?
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??
当前代码:
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"
推荐答案
您可以提供一个数组作为< a href =https://msdn.microsoft.com/en-us/library/office/ff193593%28v=office.15%29.aspx =nofollow> FieldInfo
参数到 TextToColumns
You can supply an array as the FieldInfo
parameter to TextToColumns
这将隐藏列2,3,4,6 ,7
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
这篇关于VBA:仅导入csv文件的所选列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!