How do I post data in an XL spread sheet?

[willys36]

Understand that I am rellay stupid when it comes to VB so avoid jargaon at all cost! I write programs in VB to solve petroleum reservoir oil production problems. Output from the routines is a table of oil and water production rate, temperatures, pressures, etc., vs. time. Typical table has time (typically months) down the left side, a row of column titles across the top, and several columns of data. Very simple. I can get a read-only table to display on a VB window but need to have the data in a XL spreadsheet. PLEASE tell me how to do this in english. I have tried to cipher the MS user manual and can't even get to first base.

 

[zKaleb]

If you are using Excel 2000 or newer, there is a easy way to do that if you use ADO.
If you get your information on an ADODB.Recordset (ie: named rs) and if you get and object making a reference for a Worksheet in Excel (ie: named excSheet) all you have to do is:
excSheet.Cells(3,3).CopyFromRecordset rs

If you have any doubt just let me know, hope it helps

 

[willys36]

Thanks for the quick reply. I do have the latest version of XL but I told you I am jargon challenged! I don't know what AOD, ADOB, and rs are, where to find them or what to do wiht them if I do find them! Is there any way you could send me a few cook-book example lines of code that would penetrate my thick head? I.e., once my program as generated a piece of data (sgOilRate = 5.2), how do I assing it to an XL cell? Also, how do I set up and name the XL sheet in the first place? I told you REALLY stupid. Thanks again.

 

[zKaleb]

If you need help let me know and i will email you an example

 

[zKaleb]

I know u are new to VB so:
1. Make a new project
2. Put a references to Microsoft Excel and to Activex Data Objects
3. Put a Datagrid Control named Datagrid1
4. Put a Command Button named Command1
5. Open the Code window and paste this code

Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
Dim objExcel As New Excel.Application
Dim objWrkBook As Excel.Workbook
Dim objWrkSheet As Excel.Worksheet
Set objWrkBook = objExcel.Workbooks.Add
Set objWrkSheet = objWrkBook.ActiveSheet
objWrkSheet.Cells(1, 1).CopyFromRecordset rs
objExcel.Visible = True
Set objExcel = Nothing
End Sub

Private Sub Form_Load()
rs.Fields.Append "ID", adInteger
rs.Fields.Append "Name", adVarChar, 30
rs.Fields.Append "Comment", adVarChar, 50
rs.Open
Set DataGrid1.DataSource = rs
End Sub

Hope it helps

 

[willys36]

zKaleb; Thanks!!!! That is EXACTLY what I needed. Whoopee!!

 

[willys36]

Did all the steps you recommended. Tried to run and compiler didn't like this line;

Set Datagrid1.DataSource = rs

Got " Method or data member not found" error message.

 

[zKaleb]

Are you sure u are using a Datagrid?
And in the references, what version of ADO are you using?

 

[willys36]

I am so rusty on VB that I can't even remember how to do the basics to follow your instructions. Rather than wasting your time, I think I should go to the local JC and take a refresher course. Thanks for the help. You took me a long way towards conceptually understanding what I need to do.