How to retrieve data from other Excel using VBA and SQL?

I have a problem with my code. I tried retrieving data from other Excel file. My code works but I received full data in one cell (A1). I’m sorry but I’m just beginner, believe that’s the problem related to output, but I’m not find out why:

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

No need to wrap recordset values wtih semicolon delimiters using a Do loop. Simply use 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

Advertisement

Answer