Accessing Excel From VB

Posted by Doug Sloane on July 24, 2001 4:16 AM

Hi
I have an excel file with a table of data. I have written a VB app that needs to use the data in the excel file. I've been trying to use OLE to do this but can't seem to get the code right. Is there an easier way or can someone show me how to access a cell in an excel file from vb.

Also, on the VB side I will be using 2 variables to decide which cell I am going to take the data from. Can you reference cells with say A(x) or y(x), where x and y are variables.

Thanks in advance.

Posted by Dax on July 24, 2001 5:31 AM

Assuming you're using early binding by setting a reference to the Excel object library you could use code like this to refer to a cell's value.

Sub ReferToExcel()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim x As Long, y As Long

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open("C:\Temp\Anybook.xls")
Set ws = wb.Sheets("Sheet1")

x = 5
y = 10

'Now reference the cell in the 5th row, 10th column
MsgBox "Row 5, Col 5 value=" & ws.Cells(x, y).Value
wb.Close False

'Quit Excel
xlApp.Quit
Set xlApp = Nothing
Set wb = Nothing
Set ws = Nothing
End Sub

Hope this helps,
Dax.

Posted by Doug Sloane on July 24, 2001 5:44 AM

Thanks Dax, That code looks like just what I need.
I don't understatnd your first line however "
Assuming you're using early binding by setting a reference to the Excel object library you could use code like this to refer to a cell's value. "

Also I'm getting a user function not defined error from the Dim ..... As Excel.Application.

I'm using VB4, could that be the problem ?

Posted by Dax on July 24, 2001 5:54 AM

Doug,
Early binding just means that you're referencing the object library of the application in question (in this case Excel) rather than just declaring xlApp as Object and then using the CreateObject function in VB. Early binding will mean your code runs faster and it also means that all the properties and methods of Excel will be available in the drop down boxes as you're coding. To set a reference to Excel choose Project, References and then select Microsoft Excel x.0 Object Library from the list. The version will obviously depend on what version of Excel you have installed on your machine. This will also get rid of the run time error you're experiencing.

Regards,
Dax.

Posted by Doug Sloane on July 24, 2001 9:50 AM

Dax
That's fantastic, much appreciated, and I've included the Excel object library. It now runs a bit further but gives an OLE Automation error when it gets to opening the file. Specifically Run-Time error "1073741819 (c0000005)'

Posted by Dax on July 25, 2001 6:10 AM

Doug,

If you post the code you're using I'd be happy to help sort the problem out.

Regards,
Dax.