如何通过ADO从CSV文件中使用VBA选择名称中带空格的变量？

Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
' example: GetTextFileData "SELECT * FROM filename.txt", _
              "C:\FolderName", Range("A3")
' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
              "C:\FolderName", Range("A3")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    ' the field headings
    For f = 0 To rs.Fields.count - 1
        rngTargetCell.Offset(0, f).Formula = rs.Fields(f).name
    Next f
    rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

I can't remember where I got the above code but it's really good. It allows me to use SQL on a CSV and extract the data I need.

我不记得上面的代码在哪里，但它确实很好。它允许我在CSV上使用SQL并提取我需要的数据。

Now I have a csv file where the column names can have a space in it and have other symbols such as % in the name. Now the code doesn't work. How do I tell VBA to select a column with a name such as "Swing %" the code below didn't work

现在我有一个csv文件，其中列名可以有一个空格，并在名称中有其他符号，如％。现在代码不起作用。如何告诉VBA选择名称如“Swing％”的列，下面的代码不起作用

GetTextFileData "select Swing % as swing_pct from data.csv", "c:\somewhere", Range("A1")

1 个解决方案

#1

Try [] escaping;

试试[逃避];

select [Swing %] as ...

Note: Alex K mentioned this in the comment.

注意：Alex K在评论中提到了这一点。

#1