Data Connection: Access Query to Excel How to get rid of header ro

  • Thread starter Thread starter Der Musensohn
  • Start date Start date
D

Der Musensohn

Hello.

I have created a data connection in Excel to an Access query. It does
exactly what I need however the header rows of the query are imported as well
and all I want are the data in the simplest way possible.

Thanks in advance.
 
It depends on how you setup the data connection. If you're doing it in
VBA using ADO and a reference to the query, the default is to not import the
field headings.
If you're doing it without VBA, using Get External Data on the Data
ribbon in Excel 2007 you'll get the headings.
Here's a short example using Microsoft's Northwinds sample database:

Public Sub AccessQuery()

Dim rsData As ADODB.Recordset
Dim sConnect As String

sConnect = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
"Data Source=C:\VB 2008\Northwind 2007.accdb"

'create the recordset object and run the query
Set rsData = New ADODB.Recordset
rsData.Open "[Inventory on Hold]", sConnect, adOpenForwardOnly,
adLockReadOnly, adCmdTable

'put the contents of the recordset into the worksheet
Sheet6.Range("A1").CopyFromRecordset rsData
Sheet6.UsedRange.EntireColumn.AutoFit
'close the record set object
rsData.Close
'destroy the record set object
Set rsData = Nothing

End Sub

I should note that you will also have to add a reference to the ADO
object library when you create the project (In VBA editor > Tools >
References & look for Microsoft Active X Data Objects Library, whichever is
the latest edition).
 
Right-click anywhere in the datarange of the query. Select "Data Range
Properties..." from the menu. Uncheck the "Include field names" box.
Refresh the query. That should do it.

HTH,

Eric
 
Thank you Arjen and Eric. I had time enough to go ahead and change the
properties as Eric suggested but thank you both.
 
Back
Top