Solved

How create a new Excel worksheet from VB recordset?

Posted on 2001-08-21
12
1,830 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now