Storing Excel sheets in array

[Tomexx]

Hi,
1. I need to Open Excel file
2. Read all sheets into array, read sheet names into combo box
3. When user clicks on a sheet name in the combo box - show that sheet

This is how I'm doing it right now:

Dim garrSheet1 As Variant        ' arrays will hold workbook's sheets
Dim garrSheet2 As Variant
Dim garrSheet3 As Variant
Dim garrSheet4 As Variant
Dim garrSheet5 As Variant
 
Private Sub ReadFile()
  Me.MousePointer = vbHourglass
  
  On Error GoTo Leave 
 
  Dim xlApp As Excel.Application
  Dim wb As Workbook
  Dim ws As Worksheet
  
  Dim iSheets As Integer, ctr As Integer
  
  Set xlApp = New Excel.Application
  Set wb = xlApp.Workbooks.Open("c:\myFile.xls", 0)
  
  ' Get number of sheets in a workbook
  iSheets = wb.Worksheets.Count
  
  ' Clear combo box before populating with actual sheets
  cboSheet.Clear
  
  ' Go through the sheets, add their names to combo box and store them in arrays
  For ctr = 1 To iSheets
  
    Set ws = wb.Worksheets(ctr)
    
    ' Get sheet name & add to combobox
    cboSheet.AddItem ws.Name
    
[B]    ' Store each sheet in an array
    Select Case ctr
      Case 1:     garrSheet1 = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))
      Case 2:     garrSheet2 = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))
      Case 3:     garrSheet3 = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))
      Case 4:     garrSheet4 = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))
      Case 5:     garrSheet5 = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))
    End Select  [/B]
 
  Next ctr
  
  ' Show default sheet
  cboSheet.ListIndex = DEF_SHEET
  
Leave:
  xlApp.Application.DisplayAlerts = False
  wb.Close
  xlApp.Quit
   
  Set ws = Nothing
  Set wb = Nothing
  Set xlApp = Nothing
  
  Me.MousePointer = vbNormal
End Sub

This works but the number of sheet arrays must be hardcoded in the program and they must match number of sheets in the Excel. If I add another sheet to the Excel file I'll have to change the program to add another array. So, I'd like to make it more dynamic as I don't know how many sheets there are in the file.

I would really like to replace the bolded Select Case above with something like the code below which will hold all my sheets in one place regardless of how many sheets there are in the file, but I can't get it to work.

[b]arrMySheets(ctr) = ws.Range(ws.Cells(1, 1), ws.Cells(50,20))[/b]

Any ideas greatly appreciated.

[Tomexx]

Anybody...?

[Tomexx]

One more time, then I'm looking for a bridge...

[RobDog888]

Why are you loading all the sheet data into an array if the sheet is going to be displayed anyways?

For the hardcoding of the array you can use ReDim Preserve arrMySheets(SomeVariableCountValue +1) As Variant

[Tomexx]

Well, I want to store the sheets in an array because they'll be displayed based on selection in a combo box. This way the population of the grid is fast and user doesn't have to wait. Plus I want to do some formating before showing in the grid.

Also, I'm getting "Type mismmatch" error on this line:

ReDim Preserve arrMySheets(SomeVariableCountValue +1) As Variant

[RobDog888]

Oh, ok. The error is because I just used a bogus variable for an example. You need a var with a value in there.
Something like sheet count, but if your adding sheets then you need to redim it with the + 1. If you need to redim your
sheet array size then that is a two dimentional array. You will need to get the max rows and max columns for the
range and redim on that, I think.

But since your already opening the excel workbook, why not just leave it open but hidden. Then when the user
clicks on the combo youjust have to unhide it. Allot faster.

[Tomexx]

I can't leave it open because my program checks for newer xl file once per hour and if there, loads it into arrays to be displayed as needed.

I did change the redim to have a meaningfull variable but doesn't like it.
I'm not sure if you can redim an array that has a range in it like so:

For ctr = 1 To iSheets
  
    Set ws = wb.Worksheets(ctr)
 
      ReDim Preserve arrMySheets(ctr, MAX_ROWS, MAX_COLS)
      arrMySheets(ctr) = ws.Range(ws.Cells(1, 1), ws.Cells(MAX_ROWS, MAX_COLS))
 
  Next ctr

[RobDog888]

I double checked and you can only redim on the last element of the array.
So if you had an array ar(10, 10, 10) and you redimmed preserved it - Redim ar(10, 10 , 15) would only be valid.

You basically need to Erase the array variable and then redim it to the larger size but all data will need to be reloaded
into the array.