sql - 如何使用 VBA 和 SQL 从其他 Excel 中检索数据？

Question

我的代码有问题。我尝试从其他 Excel 文件中检索数据。我的代码有效，但我在一个单元格 (A1) 中收到了完整数据。对不起，我只是初学者，相信这是与输出有关的问题，但我不知道为什么:

Sub RefreshData()

'Refresh data

Dim CreateNew As Object
Dim RunSELECT As Object
Dim Data As String
Dim SQL As String

FolderPath = ActiveWorkbook.path

path = Left(FolderPath, InStrRev(FolderPath, "\") - 1)

Set CreateNew = CreateObject("ADODB.Connection")
With CreateNew
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & path & "\Task1.xlsm; Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1;CorruptLoad=xlRepairFile"
    .Open
End With

'Run SQL

SQL = "SELECT * FROM [tw$]"
Set RunSELECT = CreateNew.Execute(SQL)
Do
   output = output & RunSELECT(0) & ";" & RunSELECT(1) & ";" & RunSELECT(2) & vbNewLine
   Debug.Print RunSELECT(0); ";" & RunSELECT(1) & ";" & RunSELECT(2)
   RunSELECT.Movenext
Loop Until RunSELECT.EOF

ThisWorkbook.Worksheets("Dic").Range("A1").Value = output

    RunSELECT.Close
    CreateNew.Close
    Set CreateNew = Nothing
    Set RunSELECT = Nothing

End Sub

Answer 1

无需使用 Do 用分号分隔符包装记录集值环形。只需使用 Range.CopyFromRecordset :

SQL = "SELECT * FROM [tw$]"
Set RunSELECT = CreateNew.Execute(SQL)

ThisWorkbook.Worksheets("Dic").Range("A1").CopyFromRecordset RunSELECT

RunSELECT.Close
CreateNew.Close

Set CreateNew = Nothing
Set RunSELECT = Nothing