Solved

Get data out of excel and into Varibles

Posted on 2001-06-14
3
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

696 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