How create a new Excel worksheet from VB recordset?

Hi EYoung,

Here is a simple example:

Private exlApp As New Excel.Application
Private exlBook As Excel.Workbook

Private Sub Command1_Click()
Set exlBook = exlApp.Workbooks.Add
exlApp.Visible = True 'not necessary, but if you want to see it
'populating data.......
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set exlBook = Nothing
Set exlApp = Nothing
End Sub

havwe a look here

http://support.microsoft.com/support/kb/articles/Q112/1/94.asp

here is some sample code

Sub AutomateExcel()
' This procedure is a brief sample showing
' how to automate Excel.

' Remember to set a reference to the most current available
' Microsoft Excel object library.

    ' Declare object variables.
    Dim appXl As Excel.Application
    Dim wrkFile As Workbooks
   
    ' Set object variables.
    Set appXl = New Excel.Application
    Set wrkFile = appXl.Workbooks

    ' Open a file.
    wrkFile.Open "c:\data\tv.xls"
    ' Display Excel.
    appXl.Visible = True
    MsgBox "At this point Excel is open and displays a document." & Chr$(13) & _
    "The following statements will close the document and then close Excel."
    ' Close the file.
    wrkFile.Close
    ' Quit Excel.
    appXl.Quit

    ' Close the object references.
    Set wrkFile = Nothing
    Set appXl = Nothing
   
End Sub


hope this helps

nzjonboy

OK I think I got it.  How do I automatically save the spreadsheet?

I have to leave for the day.  Will check back in tomorrow.

Why use late-binding... and createobject!!!!
well, anyway, if you have a valid recordset, select the range where you want to start to populate sheet and, in code, use this:
'rs is a valid recordset object and A1 is an example range.
activeworkbook.activesheet.range("A1").copyfromrecordset rs

or without excell at all being installed:

Dim db As Database

Private Sub Command1_Click()
   Set db = opendatabase("a:/db1.mdb")
   AccesstoExcel "table1", "a:/test.xls", "sheet1"
   db.Close
   Set db = Nothing
End Sub
Private Sub AccesstoExcel(accesstable As String, wherepath As String, nameofworksheet As String)
   db.execute "SELECT * INTO [Excel 8.0;DATABASE=" & wherepath & "].[" & nameofworksheet & "] FROM
[" & accesstable & "]"
End Sub

Why ?

Because if you are distributing this you don't know if people have Excel or which version even. Same with applications that use ADO for example. Someone may have an app that requires ADO 2.1 and if you early bind, your app installs 2.6 (say). Late-binding is the only solution in such situations. Anyway - do you have a really slow PC that you would even notice the difference? Or do you like taking support calls ? ;)

Hi!

Here's some files for you over the net:

Download...
http://www.vb-helper.com/HowTo/toexcel.zip
Description: Use Visual Basic to write an Excel spreadsheet (3K)


Download...
http://www.vb-helper.com/HowTo/excel.zip
Descrition: Write an Excel file without using Excel (48K)


Download...
http://www.vb-helper.com/HowTo/doexcel.zip
Description: Write Excel files directly without using Excel (45K)


Hope it helps : )

That's it!

glass cookie : )

I didn't test it but i like pierrecampe code .

Morning everyone,

Thanks for the support.  The application will be put onto just a few computers at the client's offices.  Each computer will already have Excel installed.

I will be back there next Monday and will test this.  Sorry for the delay.

My thanks to everyone for the support and suggestons.  This works just fine.