Creating Excel Spreadsheets and Graphs

[vbjewel]

OK Who can help me with creating graphs from Excel in VB?? OR even better yet sending data from Recordsets to a spreadsheet in Excel then creating a graph from that data??

If you can help me with this you are goooood.

[Achichincle]

As for how to send data to excel...

dim lngRow as long

dim objExcel as Excel.Application
dim objWorkBook as Excel.WorkBook
dim objWorkSheet as Excel.WorkSheet

' open your excel file
set objExcel = New Excel.Application
set objWorkBook = objExcel.WorkBooks.Open ("C:\Excel.xls")

' this assumes that you have at least one worksheet
set objWorkSheet = objWorkBook.WorkSheets(1)

' retrieve the data - i'm assuming you'll put all your code here to open the database connection etc...
rsData = "SELECT * FROM customers"

' loop through the data
Do While Not rsData.EOF

   ' increment your row number
   lngRow = lngRow + 1

   ' write the information to the worksheet
   objWorkSheet.Cells(lngRow,1) = rsData("name")
   objWorkSheet.Cells(lngRow, 2) = rsData("date")
   objWorkSheet.Cells(lngRow, 3) = rsData("total_purchased")

   rsData.MoveNext

Loop

' save and close the file
objWorkBook.Save
objWorkBook.Close

' cleanup
set objWorkSheet = nothing
set objWorkBook = nothing
set objExcel = nothing

' close the recordset, etc...

As for then creating a chart from that data I have nothing to offer. I'd suggest doing a search on the forums since someone has probably already asked that at some point.

Hope it gives you a starting point...

[Bootie]

Achichincle

Yes, I'm having a similar problem. Your code that you offered, does this require Excel to be present on the computer to run it, also does this cover all versions of Excel?

Rgds,
Dave R

[Achichincle]

Bootie,

This was from so long ago I don't really remember a whole lot. Looks like I was using the Excel object so yes, it does require that Excel be installed on the machine running the code.

I've done similar stuff since, but still haven't done any graphing.

Good luck.

[dglienna]

Create a macro in Excel, and copy it into a VB app. It may require some alteration before it runs, but it show you what you need to do.

[RobDog888]

Moved from Classic VB.