Solved

VB & Excel

Posted on 2001-06-04
9
140 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 56
Saving history changes to sub form 4 43
vb6 connector to mongodb 2 84
Visual Studio search word table and return Cell index 8 61
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

810 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