DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Open and Read MS Excel Files

•

Join Date: Jul 2009

Posts: 1

Reputation: Beatle Bailey is an unknown quantity at this point

Solved Threads: 0

Beatle Bailey Beatle Bailey is offline

Offline

Newbie Poster

Open and Read MS Excel Files

Jul 2nd, 2009

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

•

Join Date: Mar 2009

Posts: 810

Reputation: vb5prgrmr will become famous soon enough

Solved Threads: 147

vb5prgrmr

Offline

Practically a Posting Shark

Re: Open and Read MS Excel Files

Jul 2nd, 2009

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...

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim MyArray() As Integer
Dim MyValue As Integer
MyValue = 10
ReDim MyArray(MyValue) As Integer
Debug.Print UBound(MyArray)
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

If anyone has helped you solve your problem, please mark your thread as solved.

Thanks

•

Join Date: Aug 2008

Posts: 304

Reputation: aktharshaik is an unknown quantity at this point

Solved Threads: 37

aktharshaik aktharshaik is offline

Offline

Posting Whiz

Re: Open and Read MS Excel Files

Jul 24th, 2009

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

Regards
Shaik Akthar

•

Join Date: Aug 2008

Posts: 304

Reputation: aktharshaik is an unknown quantity at this point

Solved Threads: 37

aktharshaik aktharshaik is offline

Offline

Posting Whiz

Re: Open and Read MS Excel Files

Jul 24th, 2009

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

Regards
Shaik Akthar

•

Join Date: May 2009

Posts: 11

Reputation: tonym001 is an unknown quantity at this point

Solved Threads: 0

tonym001

Offline

Newbie Poster

Re: Open and Read MS Excel Files

Jul 24th, 2009

As an example use this.

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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