Discussion Locked

Update of spreadsheet from Visual Basic

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am interested in the code for opening an Excel spreadsheet and updating and retrieving data in a particular cell from Visual Basic.

Thank you.

1 reply

by shanghai sam

This code should do the trick. It was tested on Win 2k and Office 2k. It assumes an Excel spreadsheet is located on the h: drive called “book1.xls”. Change this accordingly. In the VB project, make sure you set a Project | Reference to the Microsoft Excel 9.0 Object Library. There is nothing else to do. You will have to become familiar with navigating Excel cell structure. The message box in this case returns what ever is in the very first cell of the first page. P.S. Greets from Southcom.

Private Sub Command1_Click()

Dim xlObj As Excel.Application
Err.Number = 0

'if a copy of excel is already in memory then get it
On Error GoTo notLoaded
Set xlObj = GetObject(, "Excel.Application.9")

'if not in memory then create it
notLoaded:
If Err.Number = 429 Then
Set xlObj = CreateObject("Excel.Application.9")
End If
'uncomment to make the excel sheet visible
' xlObj.Visible = True

'open the file.
xlObj.Workbooks.Open FileName:="h:\book1.xls"
'simple message box displaying the value of the first cell
With xlObj
MsgBox (.Range("A1").Value)
End With

'destroy the object
Set xlObj = Nothing
End Sub

This discussion is closed to new replies.