how to read data from excel

[vbscript1976]

How to read the data from Excel sheet with file name. i am getting error.

'Extra Object
Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object
Set Sys = CreateObject("EXTRA.System")
Set Sess = Sys.ActiveSession
Set MyScreen = Sess.Screen



'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
Set Excel_Obj = GetObject(,"excel.application") 'gets excel assuming it's open
Set ExcelSheet = CreateObject("excel.sheet")
Set XLApp = CreateObject("Excel.Application")
Set Wbook = XLApp.workbooks.open("C:\Searchmodule\search.xls")
Set Wsheet = Wbook.Worksheets("Sheet1")
' Set Excel_Sheet = Excel_Obj.ActiveSheet 'get the open sheet

Dim TotalRows, Counter As Integer
Dim Column1(),xy,xy2
TotalRows = 0
Redim Column1(TotalRows)
Redim Column2(TotalRows)

'get Excel info
'While Excel_Sheet.Cells(TotalRows+1,1).value <> ""
While Wsheet.Cells(TotalRows+1,1).value <> ""
Redim Preserve Column1(TotalRows+1)
Redim Preserve Column2(TotalRows+1)
Column1(TotalRows+1) = Wsheet.Cells(TotalRows+1,1).value
Column2(TotalRows+1) = Wsheet.Cells(TotalRows+1,2).value
TotalRows = TotalRows+1
wend

 

[jges]

What error is reported (description and error number)?

My first guess would be this line gives an error if Excel is not running:

 Set Excel_Obj = GetObject(,"excel.application") 'gets excel assuming it's open

You will want to trap that potential error and add branch accordingly. Or, just eliminate that line since you never use Excel_Obj - at least not in the code snippet given.

 

[SkipVought]

try this...

'all declarations here
    Dim Sys As Object, Sess As Object, MyScreen As Object, MyArea As Object
    Dim Excel_Obj As Object, Wbook As Object, Wsheet As Object, rng As Object, r As Object
    Dim TotalRows As Long, Counter As Integer
    Dim Column1() As Variant, xy, xy2
    
    'Extra Object
    Set Sys = CreateObject("EXTRA.System")
    Set Sess = Sys.ActiveSession
    Set MyScreen = Sess.Screen
        
    On Error Resume Next    'turn ON error detection
    
    'Excel Object
    Set Excel_Obj = GetObject(, "excel.application") 'gets excel assuming it's open
    
    If Err.Number = 0 Then
        'excel is open
    Else
        'excel is NOT open
        Excel_Obj = CreateObject("excel.application")
        Err.Clear
    End If
    
    On Error GoTo 0    'turn OFF error detection
    
    Set Wbook = Excel_Obj.Workbooks.Open("C:\Searchmodule\search.xls")
    Set Wsheet = Wbook.Worksheets("Sheet1")
    
    'get Excel info
    TotalRows = 1
    
    With Wsheet
        Set rng = .Range(.Cells(TotalRows, 1), .Cells(TotalRows, 1).End(-4121))   'excel constant xlDown = -4121
        
        ReDim Columns1(1, rng.Columns.Count - 1)
        
        For Each r In rng
            Columns1(TotalRows, 0) = .Cells(TotalRows, 1).Value
            Columns1(TotalRows, 1) = .Cells(TotalRows, 2).Value
            TotalRows = TotalRows + 1
        Next
    End With
    
    'Save workbook
    
    
    'Close applications
    
    
    'Close Extra Objects
    Set MyScreen = Nothing
    Set Sess = Nothing
    Set Sys = Nothing
    
    'Close Excel Objects
    Set Wsheet = Wbook.Worksheets("Sheet1")
    Set Wbook = Excel_Obj.Workbooks.Open("C:\Searchmodule\search.xls")
    Set Excel_Obj = Nothing

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}