Integrating VB6 with Excel?

How to send tabular data to a spreadsheet?

[RossWal]

I've got a big ol' grid of tabular data that gets derived from a bigger and ol'er database :). I'd like to get the data into Excel, either by embedding the spreadsheet into the VB6 application, or by somehow linking the data into Excel. Exporting a CSV file is of course a possibility, but that seems so 1980's, no?

Thanks folks!

[giggle]

Hi RossWal

I use code similar to the following to populate our Access database from Excel spreadsheets:

You'll need "Microsoft Excel 10.0 Object Library" in your Project > Preferences list

Set AppExcel = Excel.Application
Set AppExcel = CreateObject("Excel.Application")
' Location of your spreadsheet here
AppExcel.Workbooks.Open "C:\CCC\AUSTRALIA.xls"

intStarts = 1' set the first row
intEnds = 2' set the last row

For Down = intStarts To intEnds

strLocName = Trim(AppExcel.Cells(Down, 1))
strLocCode = Trim(AppExcel.Cells(Down, 2))
strLocAdd = Trim(AppExcel.Cells(Down, 3))

' update your database here

Next Down

Hope that helps (and works!).

Mick

[aspdaddy]

Excel supports SQL over adodb so with a ref to Microsoft active X Data Objects in your project I guess you could perform a single Insert Into statement.

Alternatively, in MSSQL SQL its possible to create and use a ADODB Connection or Fileysytem object from within a stored procedure.

[RossWal]

Sorry. Rereading my post, I see I wasn't too clear. I have a VB6 app that derives the data I want to present - the data came from about 10 tables in an oracle DB. Now I want to display the massaged data in Excel. Giggle's post gives me a good starting point - I'll work it out from there. Any other tips are most welcome.

Ross

[aspdaddy]

Ross,
I just meant you you could still use an adodb recordset from VB even if te data is in arrays, putting it in a record set has advantages.

The Excel object model has a built in method so you can avoid all that looping etc and do it in a single line of code.

objWorkBook.Cells(1, 1).CopyFromRecordset objRS