Hello,
I am working on a public function that gets called by the click event of a button on a form.
The function is supposed to execute a stored procedure, read the record set into an excel file, and finally open the excel file.
I have been searching for any examples of how to do this routine, and finding bits and pieces I put together the code for the function as I understood it. Lamentably it is not working.
I am hoping to get help in this forum, someone who has already completed a task like this one.
Any help is very much appreciated.
Here is my code, please if more details are needed, just let me know.
Code:
Public Function WriteReport()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim fld As Object
Set con = New ADODB.Connection
With con
.ConnectionString = GetConnectionString("DB")
.Open
End With
Set rs = New ADODB.Recordset
rs.ActiveConnection = con
rs.Open "SELECT * FROM [dbo].[ReportView]", con
' column headers
i = 0
Sheets(1).Range("A1").Activate
For Each fld In rs.Fields
ActiveCell.Offset(0, i) = fld.Name
i = i + 1
Next fld
' data rows
Sheets(1).Range("A2").CopyFromRecordset rs
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing
ThisWorkbook.SaveAs ("C:\Report.csv")
End Function