referencing OLE

[andyswinburne]

Hi,

I have an Excel worksheet as an object on one of my forms, I want to know how I can reference a single cell from within VB, as I will needed to read ad write values from this worksheet.

Any help would be much appreciated

cheers

Andy

 

[doulostheou]

I have often done this from Access through VBA. I would imagine you would do the same with VB.

First, make sure you add a reference to the Microsoft Excel 8.0 Object Library (I'm using office 97 so this might be different).

The following code will reference a cell on an Excel document:

Sub WorkWithExcel()
    Dim myObject As Excel.Application

    Set myObject = CreateObject("Excel.Application")

    myObject.Workbooks.Open "C:\YourPath\Spreadsheet.xls"
    myObject.Visible = True 'This is optional

    'Next line only necessary if there are multiple worksheets
    myObject.Worksheets("Sheet2").Select 

    'Put a value in cell B2
    myObject.Cells(2, 1) = "Value for B2"

    'Display a value from cell A5
    msgbox myObject.Cells(1,5)

    'Save file
    myObject.ActiveWorkbook.SaveAs FileName:="C:YourPath\Spreadsheet.xls"

    'Close Object
    myObject.Workbooks.Close    

    'Free up Memory
    Set myObject = Nothing
End Sub