In worksheet("source"), there are around 10000 rows and 12 columns (fields).
All columns in worksheet("source") are going to be pasted on
worksheet("target") using the following codes. I have also simplified the
query which actually involved more complicated calculations.
Columns A to H: all rows have data
Columns I to L: the first 6000 rows are blank

The problem is that I could only extract the data from Columns A to H, but
not Columns I to L which are ALL BLANK. Also, all fieldnames from Col. A to
Col. L can be extracted correctly to row1 of worksheet("target")...
The most strange thing is that if I do not leave the the first row blank
(e.g. leave only Col. J, K & L blank, and input '1' in cell(I2), then Col. I
will be extracted correctly...

Can anyone help?
Thanks a lot!!

__________________________________________________ ________________________________
Sub test()

Dim varConn As String, varSql As String
Dim varQry As QueryTable

Worksheets("target").Columns.Delete

varConn = "ODBC;DefaultDir=C:\Work;driver={Microsoft Excel Driver
(*.xls)};DriverId=790;dbq=C:\Work\testDB.xls"

varSql = "SELECT * from [source$]"

Set varQry = Worksheets("target").QueryTables.Add(Connection:=v arConn,
Destination:=Worksheets("target").Range("a1"), Sql:=varSql)
varQry.BackgroundQuery = False
varQry.Refresh

End Sub