Solved

VB & Excel

Posted on 2001-06-04
9
138 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
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…
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…

743 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

11 Experts available now in Live!

Get 1:1 Help Now