• Home
 · 
• Miscellaneous
 · 
• Retrieves value from an Excel 97/2000 worksheet cell and inserts the value into a textbox

Retrieves value from an Excel 97/2000 worksheet cell and inserts the value into a textbox

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




Rate Retrieves value from an Excel 97/2000 worksheet cell and inserts the value into a textbox

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

 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