Excel on a form

[krtxmrtz]

I like flexgrids and have used them quite a bit but now I need something "more flexible" or powerful. Is it possible to place an actual Excel spreadsheet on a form?

[RhinoBull]

You may use OLE control.

EDIT: or launch entire Excel and make it a child of your form. Let me know if you want to go this way so I can post some sample.

[krtxmrtz]

My idea is to have this spreadsheet on a separate form so I would then store in it the results from calculations performed on the main form. At the same time I could do statistics and make plots with those data using all the power built-in in Excel.

[RhinoBull]

For that purpose you don't even need to keep it open as you may simply set new values to cell(s) in the background.

Private Sub Command1_Click()
'=======================
Dim exl As Excel.Application
Dim wbk As Workbook
Dim wst As Worksheet
Dim filename As String
Dim varValue
 
    Set exl = New Excel.Application
    exl.DisplayAlerts = False
    
    filename = App.Path & "\test.xls"
    exl.Workbooks.Open filename
    
    Set wbk = exl.ActiveWorkbook
    Set wst = wbk.ActiveSheet
    varValue = "abc"
    
    wst.Range("A2").Value = varValue
    
    exl.Quit
    Set wst = Nothing
    Set wbk = Nothing
    Set exl = Nothing
 
End Sub

[krtxmrtz]

That's a nice example, though I assume you must end the job with SaveWorkspace rather than QUit or else you'll lose the data.

Thanks.

[RhinoBull]

Oops, of course ...

wst.Range("A2").Value = varValue
wbk.Save
...