Solved

Get data out of excel and into Varibles

Posted on 2001-06-14
3
175 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

920 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

20 Experts available now in Live!

Get 1:1 Help Now