Open and Read MS Excel Files

[Beatle Bailey]

Greetings,

Here is my Problem.
I have written a VB6 program that works just fine. I wrote it with all of my Variable Arrays written within the program itself. This has made the program very long and I would like to shorten it. I have the Arrays also written in a MS Excel Workbook File with each Worksheet a different file.
What I would like to do is have a routine to open the Excel Workbook and then Read into the VB Program each of the Worksheets. Some of the Worksheet contains Numerical Values with the remaining have Test Values. The Arrays Dimensions will range from Array(x) to Array(x,y,z). I now that I can set up the Reading of the Dimensions using the Do/For Routine. What I do not know how to do is Open the Excel File and the Reading of the File Code needed in the VB Program.
One last Question is it possible to have the Array’s Dimensions in the VB Program set as a Dynamic Number. The “X” Dimension in each of the files will change as the Files are updated. The “Y” and “Z” Dimensions will always remain constant but it is the “X” that will change. So, is there a way to have the “X” Dimension in the VB part be Declared after the File is Read from the Excel Worksheet?

Thanks,
Beatle Bailey

[vb5prgrmr]

Okay, several way in which to do this but let me start at the bottom and work my way up...

Yes it is possible to redimension arrays based upon a variable value...

Dim MyArray() As Integer
Dim MyValue As Integer
MyValue = 10
ReDim MyArray(MyValue) As Integer
Debug.Print UBound(MyArray)

Now, as for accessing an excel file there are several ways. You can use ADO and treat it like a database and return your data in a recordset. See http://www.connectionstrings.com for connection string information if you need it or you can search the www at your favorite search engine for vb6 excel and you will find many examples out there.

Good Luck

[aktharshaik]

Check the following thread. I have written some code. May be of some help. If not then u may post back for further requirement stating some sample data.
http://www.daniweb.com/forums/thread139729.html

[aktharshaik]

http://www.daniweb.com/forums/thread160872.html

[tonym001]

As an example use this.

Dim objExcel As Object
    Dim objWorkBook As Object
    Dim mArray() As String
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    Set objWorkBook = objExcel.Workbooks.Open("C:\test\test.xls")
    Dim intCount As Integer
    
    intCount = objWorkBook.Worksheets.Count
    ReDim mArray(intCount - 1)
    For intCount = 1 To objWorkBook.Worksheets.Count
        mArray(intCount - 1) = objWorkBook.Worksheets(intCount).Name
        Debug.Print mArray(intCount - 1)
    Next
    objWorkBook.Close True
    Set objWorkBook = Nothing
    objExcel.Quit
    Set objExcel = Nothing

DO NOT USE THIS - Dim mExcel as Excel.Application
it's no good if you wish to distribute your application

[vipin saxena]

i think this can be done in a better way using access database, then u may also need not use multidimensional array.