Solved

How create a new Excel worksheet from VB recordset?

Posted on 2001-08-21
12
1,840 Views
Last Modified: 2010-07-27
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.

Thanks.
0
Comment
Question by:EYoung
  • 4
  • 2
  • 2
  • +4
12 Comments
 
LVL 5

Expert Comment

by:jklmn
ID: 6411669
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
0
 
LVL 3

Expert Comment

by:nzjonboy
ID: 6411670
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
0
 
LVL 7

Author Comment

by:EYoung
ID: 6411744
OK I think I got it.  How do I automatically save the spreadsheet?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Author Comment

by:EYoung
ID: 6411767
I have to leave for the day.  Will check back in tomorrow.
0
 
LVL 8

Accepted Solution

by:
stefanx earned 40 total points
ID: 6411878
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
  E.Workbooks.Add
  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
  E.Quit
  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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6411946
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
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6412057
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
0
 
LVL 8

Expert Comment

by:stefanx
ID: 6412059
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 ? ;)
0
 
LVL 8

Expert Comment

by:glass_cookie
ID: 6412369
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 : )
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6413569
I didn't test it but i like pierrecampe code .
0
 
LVL 7

Author Comment

by:EYoung
ID: 6414455
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.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6429524
My thanks to everyone for the support and suggestons.  This works just fine.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question