Include SQL headers into New WorkBook

[dboone25]

Hi. Little stuck on this one.

I have a script that reads an SQL query and outputs the information into a new excel workbook. The problem i am having is getting the headers from the same script into the new workbook.

'Declare variables'    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset


'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=np-2;Initial Catalog=TESTDB;Integrated Security=SSPI;"
    objMyConn.Open


'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = "select * from table"
    objMyCmd.CommandType = adCmdText
    objMyCmd.Execute


'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open objMyCmd
    
'Loop Names'
    ' Loop to include SQL headers
    Dim i As Integer


'Copy Data to Excel'
    Workbooks.Add.Worksheets("Sheet1").Range("A2").CopyFromRecordset (objMyRecordset)
    
    For i = 0 To objMyRecordset.fields.count - 1
        Sheet1.Cells(1, i + 1) = objMyRecordset.fields(i).Name
    Next i


objMyRecordset.Close
objMyConn.Close
Set objMyRecordset = Nothing
MsgBox "Complete", vbOKOnly, "Finished"




End Sub

My eyes are starting to hurt now.

When I change the 4 loop to include Workbooks.Add this opens up 8 different workbooks. Any help on this one?

 

[Norie]

In your code Sheet1 here will refer to Sheet1 in the workbook the code is in, it won't refer to any sheet in the workbook you added.

        Sheet1.Cells(1, i + 1) = objMyRecordset.fields(i).Name

Try replacing Sheet1 with ActiveWorkbook.Sheets("Sheet1").

 

[dboone25]

Many thanks Norie. Knew it would be as simple as something like that