Solved

VB & Excel

Posted on 2001-06-04
9
141 Views
Last Modified: 2010-05-02
Could someone post some sample code that would open up an excel document and retrieve the data in cel A1 and store it in a variable. Thanks!
0
Comment
Question by:NJordan72
  • 4
  • 4
9 Comments
 
LVL 2

Expert Comment

by:chandukb
ID: 6154089
Dim xl As Excel.Application                 'Excel Object
If xl Is Nothing Then
    Set xl = CreateObject("Excel.Application")
    xl.DisplayAlerts = False
End If

    xl.Workbooks.Open lstrExcelFile 'Your file name
    xl.DisplayAlerts = True
    variable = xl.Sheets(1).Range("A1").Value
    'replace 1 with sheet name in quotes
     Set xl = Nothing

Chandu

0
 

Accepted Solution

by:
JackDev earned 25 total points
ID: 6155803
Private Sub GetExcelData()

' This is the excel program
Dim Excelapp As Excel.Application
Dim ExcelWS As Excel.Worksheet ' This is the sheet

Dim row as integer
Dim col as integer
Dim retVal as Variant

' Test whether Excel object already exists
On Error Resume Next
Set excelapp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Set excelapp = CreateObject("Excel.Application")
End If

excelapp.Workbooks.Open FileName:="C:\Test.xls"

' Check for later versions.
If Val(Excelapp.Application.Version) >= 8 Then
    Set ExcelWS = Excelapp.ActiveSheet
Else
    Set ExcelWS = Excelapp
End If

row = 1
col = 1

'Store the value in A1 in variable retVal
retVal = ExcelWS.Cells(row, col)


' Close without saving
excelapp.ActiveWorkbook.Close False
excelapp.Quit
Set ExcelWS = Nothing
Set Excelapp = Nothing

End Sub

Hope this helps...
JackDev68
0
 

Author Comment

by:NJordan72
ID: 6155844
I always get a null reference exception on the line where I open the file.  The file definetly exists, any idea why?
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Expert Comment

by:JackDev
ID: 6155882
Make sure that the Excel object has bee instantiated. Step through the code paying close attention to this part:

=====================================================
On Error Resume Next
Set excelapp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
   Set excelapp = CreateObject("Excel.Application")
End If
=====================================================

Also, try removing the On error resume next to see if a runtime error is being generated.

Another thing to try:  Assign the path to the Excel file to a variable and modify this line of code:

excelapp.Workbooks.Open FileName:=strFilePath

This is exactly how I have it in my code.  Let me know if you have any success.

Contact me if you have any more problems.

JackDev68

0
 

Author Comment

by:NJordan72
ID: 6155930
I get the error that both GetObject and CreateObject are not declared.
0
 

Expert Comment

by:JackDev
ID: 6155954
Sounds like you don't have a reference to the Excel object library.  From within VB, click on the Project menu and select References.  Look for the Microsoft Excel 9.0 Object Library (or Microsoft Excel 8.0 object library depending on the version your using) and check it. Now give it another try.

JackDev68

0
 

Author Comment

by:NJordan72
ID: 6155997
It definetly is referenced and I'm getting the same problem!
0
 

Expert Comment

by:JackDev
ID: 6156004
Can you post the text of the error message you are recieving?  Also, indicate which line is generating the error.  
0
 

Author Comment

by:NJordan72
ID: 6156052
Actually... I'm going to scrap the idea of using excel, but thanks for the help anyways!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

828 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