• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1885
  • Last Modified:

How create a new Excel worksheet from VB recordset?

I have been asked to write a VB 6 app that will download data from an external data source and write the records to an Excel spreadsheet.

I know how to download the external data, but I do not know how to create a new Excel spreadsheet, populate the cells with records from the recordset, and save the spreadsheet.

  • 4
  • 2
  • 2
  • +4
1 Solution
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


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.
    ' Quit Excel.

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

hope this helps

EYoungAuthor Commented:
OK I think I got it.  How do I automatically save the spreadsheet?
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

EYoungAuthor Commented:
I have to leave for the day.  Will check back in tomorrow.
Just to add a few thoughts to this. All the solutions so far have assumed that Excel is on your machine. I'm sure it is, but once you compile the application and give it to clients, it might be. To cater for situations like these, do NOT include the references to Excel in your project. Rather, do it in the following late bound fashion :

Sub DoIt()

  On Local Error Resume Next
  Dim E As Object
  Dim W As Object
  Dim FName AS String

  Set E = CreateObject("Excel.Application")
  If Err <> 0 Then
    Set E = Nothing
    MsgBox "A Microsoft Excel Object could not be created on your Computer.",vbCritical,"Cannot Create Object"
    Exit Sub
  End If
  Set W = E.Workbooks(1).Worksheets(1)
  W.Cells(1,1) = "Something in Cell A1"
  W.Cells(2,1) = "Something in Cell B1"
  W.Cells(3,1) = "Something in Cell C1"
  Set W = Nothing
  FName = "c:\test.xls"
  E.Workbooks(1).SaveAs FName
  Set E = Nothing
  MsgBox "All Done"  

End Sub

This approach shows you how to save the spreadsheet, but it actually shows you how to handle latebound references fore situations where you do not know whether the end user is going to have Excel installed or not.
Richie_SimonettiIT OperationsCommented:
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"
   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 ? ;)

Here's some files for you over the net:

Description: Use Visual Basic to write an Excel spreadsheet (3K)

Descrition: Write an Excel file without using Excel (48K)

Description: Write Excel files directly without using Excel (45K)

Hope it helps : )

That's it!

glass cookie : )
Richie_SimonettiIT OperationsCommented:
I didn't test it but i like pierrecampe code .
EYoungAuthor Commented:
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.
EYoungAuthor Commented:
My thanks to everyone for the support and suggestons.  This works just fine.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now