Programmatically add a query table

Sub Add_Dummy_Query_Table()
Dim cnn_str As String

cnn_str = “ODBC;DSN=Excel Files;DBQ=” & ThisWorkbook.FullName & “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”

With ActiveSheet.QueryTables.Add(Connection:=cnn_str, Destination:=Range(“A1″), Sql:=”SELECT ‘ABC’ AS [dummy]”)
.Name = “dummy”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s