Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get data out of excel and into Varibles

Posted on 2001-06-14
3
Medium Priority
?
184 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 800 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

Technology Partners: 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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

609 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