Get data out of excel and into Varibles

I posted this question this morning and thought the answer was right but I did test all of the code.  I need to query a excel spread sheet and then if is what I want I need to grab some field out of excel and put to varibles...Here is what someone gave me this morning...

'Dim xl As Excel.Application

    If xl Is Nothing Then
       Set xl = CreateObject("Excel.Application")
       xl.DisplayAlerts = False
    End If

    xl.Workbooks.Open "Mysheet.xls"
    xl.DisplayAlerts = True
    yourVariable = _xl.sheets("sheetname").range("your column, row).value
    MsgBox yourVariable
    Set xl = Nothing

yourvarible = xl.sheet(etc..) did not work,
I need to make this work...
Who is Participating?
nongravityConnect With a Mentor Commented:
Try This...
Create a new module and add this code:

Dim wBook As Integer
Dim excelFileName As String
Dim readPassword As String
Dim writePassword As String

Dim msExcelApp As Excel.Application
Dim msExcelWorkbook As Excel.Workbook
Dim msExcelWorksheet As Excel.Worksheet

Public Function openExcelFile()
Dim no As Integer
no = 0

  Set msExcelApp = GetObject("", "excel.application")
  msExcelApp.Visible = True
  If readPassword = "" And writePassword = "" Then
    Set msExcelWorkbook = Excel.Workbooks.Open(excelFileName, 0, , , , , 1)
    Set msExcelWorkbook = Excel.Workbooks.Open(excelFileName, , , , readPassword, writePassword)
  End If
End Function

Public Function setActiveSheet(excelSheet As Integer)
  Set msExcelWorksheet = msExcelWorkbook.Worksheets.Item(excelSheet)
End Function

Public Function readExcel(row As Integer, Col As Integer)as String
  readExcel = msExcelWorksheet.Cells(row, Col)
End Function

On Your form put a command button and put in this code:

Private Sub Command1_Click()
Dim MyVar as String
'or whatever you need it to be

'set the filename
excelFileName = "C:\test.xls"

'open the file

'get the data...readexcel(row,column)
MyVar = readExcel(1,1)
MsgBox "The data in that cell is " & MyVar, vbOkOnly
What values did you use in the line:

yourVariable = _xl.sheets("sheetname").range("your column, row).value

The range syntax should be something like this:

yourVariable = xl.Sheets("Sheet1").Range("A1").Value

or if the range is named then:

yourVariable = xl.Sheets("Sheet1").Range("NamedData").Value
Richie_SimonettiIT OperationsCommented:
It would be nice if you expand the contents of question, i mean, what you are trying to do?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.