Solved

VB & Excel

Posted on 2001-06-04
9
142 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
[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
  • 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
Industry Leaders: 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!

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

710 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