使用 VBA 将 CSV 文件读入 excel，然后将结果输出到特定工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14589653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading CSV file into excel using VBA then outputting the results to a specific worksheet
提问by user1898958
I've been searching for a while and can't find a simple solution. I've got a csv file that I would like to read into excel using vba and output the results into a specific cell range on a specific worksheet.
我已经搜索了一段时间，但找不到简单的解决方案。我有一个 csv 文件，我想使用 vba 将其读入 excel 并将结果输出到特定工作表上的特定单元格范围。
I've been using the following code, but if I run the excel macro twice it basically appends the data on the next blank column instead of copying over it. It also only pastes it into the active sheet instead of my specific sheet.
我一直在使用以下代码，但是如果我运行 excel 宏两次，它基本上会将数据附加到下一个空白列上，而不是复制它。它也仅将其粘贴到活动工作表中，而不是我的特定工作表中。
Any suggestions on how I can do this?
关于我如何做到这一点的任何建议？
Thanks,
谢谢，
Public Sub Example()
Const csPath As String = "starting_positions.csv"
Dim ws As Excel.Worksheet
Set ws = Excel.ActiveSheet
With ws.QueryTables.Add("TEXT;" & csPath, ws.Cells(1, 1))
.FieldNames = True
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileCommaDelimiter = True
''// This array will need as many entries as there will be columns:
.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat)
.Refresh
End With
End Sub
回答by Peter L.
Assuming you run the code in the same workbook you need to collect CSV data to, try the following:
假设您在需要收集 CSV 数据的同一工作簿中运行代码，请尝试以下操作：
- Replace
Set ws = Excel.ActiveSheetwithSet ws = ThisWorkbook.Sheets(1)orSet ws = ThisWorkbook.Sheets("Sheet1")to define any specific sheet using either its index or name. ws.UsedRange.ClearContentsplace priorWith...block will clear the whole sheet, and therefore new data will be placed to the same place as before.
- 替换
Set ws = Excel.ActiveSheet为Set ws = ThisWorkbook.Sheets(1)或Set ws = ThisWorkbook.Sheets("Sheet1")以使用其索引或名称定义任何特定工作表。 ws.UsedRange.ClearContents放置优先With...块将清除整个工作表，因此新数据将放置在与以前相同的位置。
One more hint: in case you're going to use this macro many times- do not create every time a new connection. In case all the options are similar - only use .Refreshmethod.
还有一个提示：如果您要多次使用这个宏- 不要每次都创建一个新连接。如果所有选项都相似 - 仅使用.Refresh方法。
If you're stuck with the merging of code parts - try to use macro recorder passing any of the steps manually. For your case this will solve the majority of troubles. Good luck!
如果您坚持合并代码部分 - 尝试使用宏记录器手动传递任何步骤。对于您的情况，这将解决大多数问题。祝你好运！
