Option Explicit
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim sql As String
Dim qtb As QueryTable
Dim it As String
Dim pm As String




Sub refCol()

Application.ScreenUpdating = False
'Returns or sets the number of sheets that Microsoft Excel automatically inserts into new workbooks.
Application.SheetsInNewWorkbook = 2

'creates a new workbook which becomes the active workbook.
Workbooks.Add

'create two new sheets and assign them to variables so we can reference them
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

ws1.Cells(1, 1) = "Number"
ws1.Cells(2, 1) = 1
ws1.Cells(3, 1) = 1
ws1.Cells(4, 1) = 4
ws1.Cells(5, 1) = 1
ws1.Cells(6, 1) = 3
ws1.Cells(7, 1) = 1
ws1.Cells(8, 1) = 1

sql = "SELECT ws1.[F1] " & _
      "FROM [" & Worksheets(1).Name & "$] AS ws1"

'sql = "Select Number" & _
      "FROM [" & ws1.Name & "$] as ws1"

ws2.Select

Call RunQuery

End Sub

Sub RunQuery()


With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=DSN;UID=USER;HDR=NO", _
            Destination:=Range("A1"))
        .CommandText = sql
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
        End With
        For Each qtb In ActiveSheet.QueryTables
        qtb.Delete
        Next