如何在 VBA 中使用 .OpenText 打开文本文件，该文件以第一列（如“ID”）开头，而不会出现 SYLK 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13078256/
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
How to open Text File with .OpenText in VBA that starts with first column like "ID" without getting SYLK error
提问by Andy Raddatz
I've come across a nasty little bug in Excel VBA's .OpenTextmethod.. It errors on opening any text or CSV file when the first two letters are upper-case "ID". Here is the article from MS so you know I'm not crazy: http://support.microsoft.com/kb/323626
我在 Excel VBA 的.OpenText方法中遇到了一个讨厌的小错误。当前两个字母是大写的“ID”时，它在打开任何文本或 CSV 文件时出错。这是 MS 的文章，所以你知道我没有疯：http: //support.microsoft.com/kb/323626
So, I'm trying to figure out a workaround that does NOT involve copying the entire file just to re-name the first header. I am working with some large text files and this would be an unsatisfactory last resort.
所以，我试图找出一种解决方法，它不涉及复制整个文件只是为了重命名第一个标题。我正在处理一些大型文本文件，这将是一个不令人满意的最后手段。
I've tried On Error Resume Nextbefore the .OpenTextcall but that didn't work.. Has anybody come across this and found a simple solution I'm missing? Is there a way to just crack open the first line and find/replace inside of a text file? Or extra parameters to .OpenTextI could use?
我On Error Resume Next在.OpenText打电话之前试过，但没有奏效..有没有人遇到过这个问题并找到了我遗漏的简单解决方案？有没有办法打开第一行并在文本文件中查找/替换？或者.OpenText我可以使用的额外参数？
回答by Daniel
I wrote this for you. Just call it passing the filepath prior to attempting to open it. I deliberately wrote this with late binding, so no references are required. it will add an apostrophe to the beginning of the file, if the file starts with "ID".
我为你写了这个。只需在尝试打开它之前通过文件路径调用它。我特意用后期绑定写了这个，所以不需要引用。如果文件以“ID”开头，它将在文件开头添加一个撇号。
Sub FixIDProblem(filePath As String)
Dim fso As Object
Dim text As Object
Dim contents as String
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(filePath) Then
'Open the file for reading
Set text = fso.OpenTextFile(filePath, 1)
'Load the text contents to variable
contents = text.ReadAll
'Check for the forbidden text at the beginning
If Left(contents, 2) = "ID" Then
text.Close
'Overwrite textfile with it's contents plus an apostraphe
Set text = fso.OpenTextFile(filePath, 2)
text.Write "'" & contents
End If
text.Close
Else
MsgBox "File does not exist"
End If
Set fso = Nothing
End Sub
回答by user2359520
Just turn the alerts off:
只需关闭警报：
Application.DisplayAlerts = False
Application.Workbooks.OpenText Filename:="startwithID.tab"
Application.DisplayAlerts = True
回答by Antoine Turmel
I did this instead :
我这样做了：
Application.DisplayAlerts = False
On Error Resume Next
Workbooks.OpenText Filename:=myfile, DataType:=xlDelimited, Tab:=False, Semicolon:=True, Local:=True
Workbooks.OpenText Filename:=myfile, DataType:=xlDelimited, Tab:=False, Semicolon:=True, Local:=True
Application.DisplayAlerts = True
The first OpenText fails, but the second one works.
第一个 OpenText 失败，但第二个有效。
The FixIDProblem is a good idea but fails on big files (~ 40MB)
FixIDProblem 是个好主意，但在处理大文件 (~ 40MB) 时失败
