Prev: VB: Convert rectangular clusters of # within field: FindNext, Find
Next: Macro/VBA to loop through list of values and output row- Help
From: Der Musensohn on 26 Feb 2010 11:28
Thank you Arjen and Eric. I had time enough to go ahead and change the
properties as Eric suggested but thank you both.



"arjen van der wal" wrote:

>
> 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).
 | 
Pages: 1
Prev: VB: Convert rectangular clusters of # within field: FindNext, Find
Next: Macro/VBA to loop through list of values and output row- Help