http://msdn.microsoft.com/en-us/libr.../ff836161.aspx

I'm trying to use this and other tutorials to create a query table that references the active workbook. I first created the connection manually and then copied all the relevant information from the connection string into my VBA code. The connection string and SQL statements both work separately, but I can't get the QueryTable to work at all. I get an error 1004 on the line "With ActiveSheet.Qu.....".

When I create it all manually, and use the MSDN code snippet "MsgBox ActiveSheet.QueryTables.Count", it returns "0".

In extensive Googling I can't find a single example of someone getting QueryTables to work in VBA, does this actually work for anybody?

Sub MakeExcelQT()
    
    Dim sConn As String
    Dim sSQL As String
    
    sConn = "DSN=Excel Files;DBQ=H:\Excel\ExcelDatabase.xlsm;DefaultDir=H:\Excel;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT * FROM tblDARE"
    
    With ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveSheet.Range("A1"), Sql:=sSQL)
        .Refresh
    End With
    
End Sub