Solved

Get data out of excel and into Varibles

Posted on 2001-06-14
3
171 Views
Last Modified: 2010-05-02
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...
Thanks..
0
Comment
Question by:strongd
3 Comments
 

Accepted Solution

by:
nongravity earned 200 total points
ID: 6192909
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)
  Else
    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
openExcelFile

'get the data...readexcel(row,column)
MyVar = readExcel(1,1)
MsgBox "The data in that cell is " & MyVar, vbOkOnly
0
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 6193430
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
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6196357
It would be nice if you expand the contents of question, i mean, what you are trying to do?
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now