export data from an ADO recordset into an excel file

[tornadoproggie]

I know that data from an ADO recordset can be exported to a text file but my client wants the data to be downloaded into an excel sheet which should be done whenever he , say, clicks a button.Is this possible and if so how?

 

[Bastien]

thread333-160062 reference...it is possible and you need to progam a boatload of loops to fill in the columns and cells.

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[lpatnaik]

I think this will definitely Help you.


Private Function GenerateExcel(rsSet As Recordset)
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Dim nRow, nCol As Integer
Dim rngTitle As Excel.Range


Dim i, j, k As Integer
Dim nFldCnt As Integer

Set objExcel = CreateObject("Excel.Application&quot
Set objBook = objExcel.Workbooks.Add
Set objSheet = objBook.Worksheets.Add
objSheet.Name = "Sheet Name"

objExcel.Visible = True

nRow = 1
nCol = 1

rsSet.MoveFirst
nFldCnt = rsSet.Fields.Count

Set rngTitle = objSheet.Rows(nRow).EntireRow
With rngTitle
.Merge
.Value = "Your table heading"
'.Font.Parent
.Font.Size = 18
.Font.FontStyle = "Arial"
.Font.Bold = True
.Interior.ColorIndex = 16
End With

nRow = nRow + 3
For i = 0 To nFldCnt - 1
objSheet.Cells(nRow, nCol).Value = rsSet.Fields(i).Name
objSheet.Columns.AutoFit
nCol = nCol + 1
Next

Set rngTitle = objSheet.Rows(nRow).EntireRow
With rngTitle
.Font.Size = 11
.Font.FontStyle = "Arial"
.Font.Bold = True
.Interior.ColorIndex = 16
End With

rsSet.MoveFirst
nRow = nRow + 1
nCol = 4
Do While Not rsSet.EOF
For i = 0 To nFldCnt - 1
objSheet.Cells(nRow, nCol).Value = rsSet.Fields(i).Value
objSheet.Columns.AutoFit
nCol = nCol + 1
Next
nRow = nRow + 1
nCol = 4
rsSet.MoveNext
Loop

strFilename = "D:\myfile.xls"
objBook.SaveAs (strFileName)
objExcel.Quit

Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing

End Function
--Lopa

 

[kaveri00]

For the above coding do u need to install anything?becas Oject required error is coming.

 

[Bastien]

need to have excel on the server Bastien

There are many ways to skin this cat,
but it still tastes like chicken