show column names in excel from sql VBA

[ghynes]

hey folks,

i have populated a database using VBA connecting to an SQL server, but it is not showing the column names. anyone know how i can show the column names?

thanks.

 

[DonkeyOte]

It would help to know what code you're using but if we assume ADO etc then you can iterate the Fields within the recordset and return the field name.

 

[RoryA]

Which version of Excel and how are you populating the data? If it's via a recordset and CopyFromRecordset, then you need to loop through the fields and output the Name yourself.

 

[ghynes]

using excel 2007. here is my code.. not too sure how to write the code to loop throught the field. any help appreciated!

Sub dataextract()

' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=server.com;INITIAL CATALOG=table;"

'Use an integrated login.
strConn = strConn & " UID=****; PWD=****;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
        
    
    .Open "SELECT * FROM *


    
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsPubs
    
    
    
    ' Tidy up
    .Close
    
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing



End Sub

 

[RoryA]

With rsPubs
     For i = 1 To .Fields.Count
         sheet1.Cells(1, i) = .Fields(i - 1).Name
     Next i
end with
Sheet1.Range("A2").CopyFromRecordset rsPubs

for example.

 

[ghynes]

brilliant! thanks