vba QueryTables.Add 方法 (Excel)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24957541/
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
QueryTables.Add Method (Excel)
提问by Minion-kunfu
strFile = Application.GetOpenFilename 'Workbooks.Open strFile
strFile = Application.GetOpenFilename 'Workbooks.Open strFile
With ActiveSheet.QueryTables.Add( _
Connection:="strFile", _
Destination:=Range("$A"))
'I have tried with : With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;strFile", _ Destination:=Range("$A$1"))
'我尝试过：使用 ActiveSheet.QueryTables.Add(_ Connection:="TEXT;strFile", _ Destination:=Range("$A$1"))
This is throwing up an error saying "Run-time error '1004'; So you have any clue why it is showing me error ??
这是抛出一个错误，说“运行时错误'1004'；所以你知道为什么它会显示我的错误吗？？
回答by Soulfire
After some quick testing, I think I have found the issue. You need to be sure you are not passing in the string "strFile" into the connection, and you need to concatenate the TEXT to the connection string. This is demonstrated here:
经过一些快速测试，我想我已经找到了问题所在。您需要确保没有将字符串“strFile”传递到连接中，并且需要将 TEXT 连接到连接字符串。这在这里演示：
strFile = Application.GetOpenFilename 'Workbooks.Open strFile
strFile = "TEXT;" & strFile 'Add the TEXT; to beginning of connection string
With ActiveSheet.QueryTables.Add(Connection:= _
strFile, Destination _
:=Range("$A"))
... 'Remainder of code
End With
For good measure, you may want to put the With ActiveSheet.QueryTables.Add ...code inside an IF statement that checks to make sure a file was selected.
为了更好地衡量，您可能希望将With ActiveSheet.QueryTables.Add ...代码放在一个 IF 语句中，该语句检查以确保选择了一个文件。
For example:
例如：
Dim strFile As String
strFile = Application.GetOpenFilename 'Workbooks.Open strFile
strFile = "TEXT;" & strFile
If strFile <> False Then
With ActiveSheet.QueryTables.Add(Connection:= _
strFile, Destination _
:=Range("$A"))
... 'Remainder of code
End With
Else
MsgBox "No file was selected!"
End If
