Solved: VB6 & the XL spreadsheet control

[Dave]

I need a bit of help with understanding/using the MS spreadsheet control in VB6. I have, as a test, a form with a spreadsheet control and a command button on it. I have set a reference to XL. I was expecting that the control would not be greyed out and that it would be rather simple use. So far not. Here's the code I'm trialling if someone has a few minutes to spare. Thanks. Dave
[VBA]
Private Sub Command1_Click()
Dim ObjExcel As Object, ObjWorksheet As Object
Set ObjExcel = CreateObject("EXCEL.APPLICATION")
Set ObjWorksheet = Form1.Sheet1
'the line of code below crashes
ObjWorksheet.Cells(1, 1).Value = InputBox("Test No.")
'the line below doesn't seem to do anything?
Form1.Sheet1.Visible = True
ObjExcel.Quit
Set ObjWorksheet = Nothing
Set ObjExcel = Nothing
End Sub
[/VBA]

[Bob Phillips]

Dave,

Not quite following what you are doing, for instance, why do you start Excel?

What spreadsheet control are you using?

[XL-Dennis]

Dave,

I echo Bob's comment with the following addition:

If You intend to open an existent Excel file or a new Excel file as an OLE-object in the VB form then You need to use the OLE-control in the first place and then choose the Microsoft Excel Worksheet in the dialog that popups after You've placed the OLE-object on the form. You can't drag an Excel sheet-object directly to the Form which I assume You try to do based on the provided code.

If You refer to the Spreadsheet control shipped with the OWC-package then a good start is to take part of the following article:http://www.vbaexpress.com/forum/arti...ticle&artid=46


Kind regards,
Dennis

[Dave]

Thanks for the responses and the links. Dennis you nailed my missing OLE control knowlege deficit. I don't think the spreadsheet control is going to do what I wanted.. row deletion/insertion, interaction between worksheets, a print ability, & perhaps charts stuff as well. My new surfin has led me to believe that I actually want to use a data grid control which can offer these features (I downloaded a trial version of "flexcell"). Being a novice, I haven't figured out how to install or use it, but I do have a MS Flexgrid control 6 to learn with. Anyone with a link to a turorial, or a few lines of code to simply insert input box data to a cell (or location?) of the control would give me a start. Thanks for your time. Dave

[XL-Dennis]

Dave,

What is the primarily goal? Why I'm asking is that the FlexGrid control may be a low-budget option where a control from Component One can save lot of hours.

The following link cover how to use textbok to edit a FlexGrid control:
http://www.codeguru.com/vb/controls/...cle.php/c1509/

Being a novice, I haven't figured out how to install or use it, but I do have a MS Flexgrid control 6 to learn with

These controls are of the type ActiveX and they must be first registrated i the Windows System before they can be used. Usually they are registrated during installation and therefore should be available via the command Ctrl+T in VB.

Kind regards,
Dennis

[Dave]

Thanks again Dennis. Interestingly enough, the link you posted was in my collection along with these others that I will post. Hope they are of some benifit for others. The last link I borrowed heavily upon to complete this learning project. Hopefully the code will explain itself. Apologies for getting off my originally posted topic. I'll mark this thread solved. Dave
http://cuinl.tripod.com/tutorials.htm 'basic VB6 tutorials
http://cuinl.tripod.com/tutorials.htm
http://www.developerfusion.co.uk/vb/1/Article/ 'varied VB tutorials
http://www.developerfusion.co.uk/vb/1/Article/
http://www.codeguru.com/vb/controls/vb_activex/article.php/c3479 'register/unregister
http://www.codeguru.com/vb/controls/...icle.php/c3479
http://www.codeguru.com/vb/controls/vb_activex/activex/article.php/c3491/ 'data grid as disconnected data entry tool
http://www.codeguru.com/vb/controls/...cle.php/c3491/

Place this code in the form code..
[VBA]
Option Explicit
'
' Using the DataGrid for DataEntry Unbound
' Original code by Lothar Haensler
' Adapted for the web by Chris Eastwood
' This sample shows how to use the DataGrid as a simple
' data entry control. The control uses a disconnected,
' client-side ADOR Recordset to build up the data and then
' to query the values in the DataGrid.
'add MS datagrid6 contol 6.0 control(see components) to form
'add 2 command button to form
'reference MS activeX data objects recordset 2.7 library
Private Sub Command1_Click()
Dim r As ADOR.Recordset
Set r = DataGrid1.DataSource
'r.AddNew 'to add row
On Error GoTo ErFix
r.Delete 'to delete row
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "No data in first row"
End Sub

Private Sub Command2_Click()
Dim r As ADOR.Recordset
Dim Numtemp$, Coltemp As Integer
On Error GoTo ErFix
Set r = DataGrid1.DataSource
Numtemp = InputBox("Enter the Number")
Coltemp = InputBox("Enter the Column")
If Coltemp < 4 Then
r.AddNew
r.Fields(Coltemp - 1).Value = Numtemp
Else
MsgBox "Only three test Columns"
Exit Sub
End If

Set DataGrid1.DataSource = r
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "Enter NUMBERS"
End Sub

Private Sub Form_Load()
Dim r As ADOR.Recordset
Dim lCount As Long
' Create a new disconnected recordset object
Set r = New ADOR.Recordset
' Setup the fields - you can use any valid type of
' ado field type for these. I've used VarChar just
' for testing / demonstration purposes.
r.Fields.Append "Column 1", adVarChar, 10
r.Fields.Append "Column 2", adVarChar, 50
r.Fields.Append "Column 3", adVarChar, 50
r.CursorType = adOpenDynamic
r.Open

Set DataGrid1.DataSource = r
End Sub[/VBA]

[XL-Dennis]

Dave,

Thanks for coming back and also add the info as You did - highly appreciated

Kind regards,
Dennis