Cast Excel Data into a 2-D Array

[ravi.gajul]

Here is an example to cast an excel range to a 2D- array

Code:

Call ReadExcel (sFileName,sSheetName)
Function ReadExcel(sFileName,sSheetName)                                            
   Dim oExcel                                                                                
   Dim oRange                                                                                
   Dim arrRange                                                                              
                                                                                            
    'Open the file and set the sheet                                                        
    'On Error Resume Next                                                                    
       Set oExcel = CreateObject("Excel.Application")  
       oExcel.Visible=True
           oExcel.Workbooks.Open "C:\Documents and Settings\testingsvs_qa\Desktop\Book1.xls"  
           oExcel.Workbooks(1).Activate                  
      Set oRange = oExcel.Worksheets(sSheetName).UsedRange                              
                                                
                                                                                            
      If Err.Number <> 0 Then                                                                
         ReadExcel = Array("Error")    
         msgbox Err.Description                                                    
         Exit Function                                                                      
      End If                                                                                
   'On Error Goto 0                                                                          
                                                                                            
   'Cast excel data into a two-dimentional array                                            
   arrRange = oRange.Value                                                                  
                                                                                            
    oExcel.WorkBooks.Close    
     Set oRange = Nothing                                                                                                                    
     oExcel.Quit                                                                              
     Set oExcel = Nothing                                                                      
                                                                                              
     'return the arrRange and then return to the function                                      
     ReadExcel = arrRange                                                                      
                                                                                              
      End Function