Importing data from Access database to Excel. Dont want headers/"field names".

Hi All

Really hope you can help me with this one...its killing me!.

In advance...thank you for helping ;-)

I'm using Office 2010, and i have made a connection to an external database.
When i extract the data, i dont want the headers/"field names" from the Access database table to be exported to my Excel Woorkbook/sheet, just the data form the rows in the table.

I have tried to import with headers/"field names", and hide them using:
ActiveSheet.ListObjects("MyListName").ShowHeaders = False
Then delete the row. Ant put ".RefreshStyle = xlOverwriteCells" in the Query.
But this method leaves "traces" of the row, where the headers/"field names", used to be.
This results in "overlap" errors when i refresh the workbook.

Here is the code that works just fine...besides the headers/"field names" :-)

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=SQLOLEDB.1;Data Source=ly-db-01;Initial Catalog=QALY;Integrated Security=SSPI" _
, Destination:=Range("MyRange")).QueryTable
.CommandType = xlCmdSql
.CommandText = "SELECT XXX_Nr, Init, Date, XXX_Closed_Date FROM Q_XXX_List WHERE XXX_Nr = " & MyListValueToGetDataFromRow
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.SourceConnectionFile = _
"C:\Documents and Settings\MyPath\MyDataBase.adp"
.ListObject.DisplayName = "MyListName"
.Refresh BackgroundQuery:=False
End With

Im still having the same problem :-(
Found searching the web today, that another had the very same problem: http://www.excelforum.com/excel-gene...ut-header.html
There must be a genius "out there" that knows the answer...it must be very simple.

Found out, that when using ADO as connection, ".FieldNames = False/True" can be used.
But i dont know how to put up an ADO connection :-(
The above code vas made using Excel 2010's own macro-recorder...im new to this xD

Please help!

No easy/simple solution to this problem :-(
It seems that this "refresh data connections" (with or without headers) is a bug that is inherited from 2007. (Google: Removed Part: /xl/queryTables/queryTable1.xml part. (External data range))
A bugfix exists for Excel 2007, but not for 2010 (((((((((((
It also seems that ListObjects/QueryTabels can not "perform"/exist without headers :-(

I do need to refresh data, as they change every day...therefore...to the "not so good" but working solution.

I got the ADO connection working all right using an example from the WWW, but it does not store/hide the data connection.
It "just fetches" the data, and nothing else. Therefore no "automatic" refresh by "Excel itself" :-(
The refreshing of data fetched from Access has to be done by a macro that cycles through every "data row" in the sheet, and "gets the (mabye altered) data again", the very same way that the data vas fetched the first time.
A solution i am not proud of...but it will do allright :-)

If anybody is interested...here is the ADO code:

Sub GetADOdata()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Dim MyValue As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

'If specific option is used: MyValue = InputBox("NCR number", , 10237)

cn.Open ( _
"User ID=" & _
";Password=" & _
";Data Source=C:\Documents and Settings\MyDatabase.adp" & _
";Provider=SQLOLEDB.1;Data Source=MyServerName;Initial Catalog=QALY;Integrated Security=SSPI")

rs.Open "SELECT * from dbo.Q_NCR_List", cn
'More specific: rs.Open "SELECT NCR_Nr, Init, Date, NCR_Closed_Date FROM dbo.Q_NCR_List WHERE NCR_Nr = " & MyValue, cn

col = 0

' First Row: names of columns <---This loop is the one i skip = no headers :-D
Do While col < rs.Fields.Count
Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

' Now actual data as fetched from select statement
row = 1
Do While Not rs.EOF
row = row + 1
col = 0

Do While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Loop

rs.MoveNext
Loop

End Sub