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.
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?
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.
Code:
'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?