by John Flaherty (5 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Mon 20th August 2001
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Retrieves value from an Excel 97/2000 worksheet "cell" and inserts the value into a textbox
API Declarations
Dim MySpreadSheet As Object
'Declare a temporary variable to hold the value obtained from the cell
'in Excel. In this case, the Excel cell is formatted as a Currency type.
'It may be safer not to indicate the data type in the declaration and let
'VB treat it as a variant data type.
Dim TempVariable As Currency
'MyWorksheet and MyCell hold values for the Excel functions that correlate to
'the user's choices as reflected in the dropdown boxes, cboWorkSheet and
'cboCell.
Dim MyWorksheet, MyCell As String
VBcoders
With MySpreadSheet
'Change the mouse pointer (aka: cursor) to an hourglass
Screen.MousePointer = vbHourglass
'Open the application object by giving the file name, telling it to
'use option 3 which means update all links, and to open the file in
'READ ONLY mode
.Workbooks.Open "c:\materials.xls", UpdateLinks:=3, ReadOnly:=True
'NOTE: The value of the variable MyWorksheet is an integer indicating
'the number of the worksheet in the Sheet collections object (array). I
'had inconsistent results when I tried to set the value to a string
'which contained the actual name of the worksheet. Specifically, I was
'getting a "Subscript Out of Range Error: 9" error.
'NOTE: The value of the variable MyCell contains the cell address.
'Below is an example with and without the variables:
'TempVariable = .Workbooks("materials.xls").Sheets("Sheet1").Range("D8")
TempVariable = .Workbooks("materials.xls").Sheets(MyWorkSheet).Range(MyCell)
'Display value obtained from cell to user
lblMaterialsMoneyRemaining.Caption = "$" & Str(TempVariable)
'Close the application object
.Workbooks("materials.xls").Close
'Quit the application object
.Quit
'Change the mouse pointer (aka: cursor) back to the default setting
Screen.MousePointer = vbDefault
End With
Set MySpreadSheet = Nothing
No comments have been posted about Retrieves value from an Excel 97/2000 worksheet cell and inserts the value into a textbox. Why not be the first to post a comment about Retrieves value from an Excel 97/2000 worksheet cell and inserts the value into a textbox.
