• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

VB & Excel

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
NJordan72
Asked:
NJordan72
  • 4
  • 4
1 Solution
 
chandukbCommented:
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
 
JackDevCommented:
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
 
NJordan72Author Commented:
I always get a null reference exception on the line where I open the file.  The file definetly exists, any idea why?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
JackDevCommented:
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
 
NJordan72Author Commented:
I get the error that both GetObject and CreateObject are not declared.
0
 
JackDevCommented:
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
 
NJordan72Author Commented:
It definetly is referenced and I'm getting the same problem!
0
 
JackDevCommented:
Can you post the text of the error message you are recieving?  Also, indicate which line is generating the error.  
0
 
NJordan72Author Commented:
Actually... I'm going to scrap the idea of using excel, but thanks for the help anyways!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now