Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1873
  • 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?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
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 : )
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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