使用 VBA 导入分号分隔的 CSV 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39890471/
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
Import semicolon separated CSV file using VBA
提问by Limak
I have a problem with opening .csv files with Excel by VBA code. I have data organised like:
我在通过 VBA 代码使用 Excel 打开 .csv 文件时遇到问题。我的数据组织如下：
Number;Name;Price1;Price2;City
1234;"John Smith";"1,75 EUR";"2,15 EUR";"New Mexico"
3456;"Andy Jahnson";"12,45 EUR";"15,20 EUR";London
3456;"James Bond";"42,34 EUR";"9,20 EUR";Berlin
When I open this file manually by Excel from Windows Explorator, everything looks fine, all values are separated correctly. It looks like that:
当我从 Windows 资源管理器中通过 Excel 手动打开此文件时，一切看起来都很好，所有值都正确分隔。它看起来像这样：
When I try to open this by VBA, using
当我尝试通过 VBA 打开它时，使用
Workbooks.Open fileName:=strPath & "thisFile.csv"
data is separated by commas, so it looks like that:
数据用逗号分隔，所以它看起来像这样：
The same wrong result pops out when I am using OpenText function
当我使用 OpenText 函数时会弹出同样的错误结果
Workbooks.OpenText filename:=strPath & "thisFile.csv", DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False
and when I try use solution from this thread. Any ideas?
当我尝试使用此线程中的解决方案时。有任何想法吗？
回答by arcadeprecinct
I hope you are using something newer than Excel 2000. This is what I experience with Excel 2016 on a machine with German format settings: Apparently the Workbooks.Openoptions for delimiters (Formatand Delimiter) are only applied when you open a .txtfile. If Localis set to False(the default value), the file will be opened with the VBA language settings, using a comma as delimiter. Setting Local:=Truewill prevent this so
我希望您使用的是比 Excel 2000 更新的东西。这就是我在具有德语格式设置的机器上使用 Excel 2016 的体验：显然Workbooks.Open，分隔符 (Format和Delimiter)选项仅在您打开.txt文件时应用。如果Local设置为False（默认值），文件将使用 VBA 语言设置打开，使用逗号作为分隔符。设置Local:=True将防止这种情况
Workbooks.Open FileName:=strPath & "thisFile.csv", Local:=True
should work for you. If you rename your file to .txt, you can use the Formatand Delimiteroptions:
应该为你工作。如果将文件重命名为.txt，则可以使用Format和Delimiter选项：
Workbooks.Open FileName:=strPath & "thisFile.txt", Format:=4 'Format = 4 is semicolon delimited
Workbooks.Open FileName:=strPath & "thisFile.txt", Format:=6, Delimiter:=";" 'Format = 6 is custom delimited
See MSDNfor more details
However this will mess up your decimal numbers, see my edit.
有关更多详细信息，请参阅MSDN
但是这会弄乱您的十进制数字，请参阅我的编辑。
Edit:I misread the documentation. The Formatand Delimiteroptions are actually only applied when using a .txtfile and not .csv(even the .OpenTextmethod behaves that way).
编辑：我误读了文档。在Format和Delimiter选项实际上只使用时，应用.txt文件，而不是.csv（甚至是.OpenText方法的行为这种方式）。
If you want to make sure it opens on a machine with different format settings the only solution I have right now is to rename it to .txtand use
如果您想确保它在具有不同格式设置的机器上打开，我现在唯一的解决方案是将其重命名为.txt并使用
Workbooks.OpenText Filename:=FileName:=strPath & "thisFile.txt", DataType:=xlDelimited, Semicolon:=True, DecimalSeparator:=",", ThousandsSeparator:="."
