RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\projects\Excel2007Book\Files\Northwind 2007a.accdb"
.ListObject.DisplayName = "Table_Northwind_2007a.accdb"
.Refresh BackgroundQuery:=False
End With
End Sub
The SourceType and Source settings of the ListObject.Add method tell whether the data is
from an Excel sheet (xlSrcRange = 1) or an external source (xlSrcExternal = 0). When the
SourceType is external, the source is an array of string values specifying a connection to the
source data.
Buried at the end of our lengthy source data string is this line of code:
Destination:=Range("$A$1")).QueryTable
A QueryTable object is a worksheet table that is created any time data is returned from an
external data source like an Access or SQL Server database. Table 2-1 lists the members of the
QueryTable object and describes them.
Table 2-1. QueryTable Object Members
QueryTable Object Members Description
CommandType Returns/sets one of the xlCmdType constants. The xlCommandType
constants define whether an SQL statement, cube, or OLE DB data
source will be requested. The default value is xlCmdSQL.
CommandText Returns/sets the command string for the data source.
RowNumbers True if row numbers are added as the first column of the query table.
Pages:
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72