Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 1039

Jul 2nd, 2009

Open and Read MS Excel Files

Expand Post »

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

Similar Threads

open and read all files from folder in Java
How to open and read a multi files in? in C++
retreive data from excel files in C++
Cannot open Excel Files anymore!! in Windows NT / 2000 / XP
Create modify insert del read from excel file in C++

Beatle Bailey

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jul 2009

Permalink

Jul 2nd, 2009

Re: Open and Read MS Excel Files

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

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

vb5prgrmr

Reputation Points: 156

Solved Threads: 296

Posting Virtuoso

Offline

1,670 posts
since Mar 2009

Permalink

Jul 24th, 2009

Re: Open and Read MS Excel Files

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

Reputation Points: 26

Solved Threads: 40

Posting Whiz

Offline

316 posts
since Aug 2008

Permalink

Jul 24th, 2009

Re: Open and Read MS Excel Files

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

aktharshaik

Reputation Points: 26

Solved Threads: 40

Posting Whiz

Offline

316 posts
since Aug 2008

Permalink

Jul 24th, 2009

Re: Open and Read MS Excel Files

As an example use this.

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

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

Last edited by tonym001; Jul 24th, 2009 at 5:55 pm.

tonym001

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since May 2009

Permalink

Jul 25th, 2009

Re: Open and Read MS Excel Files

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

vipin saxena

Reputation Points: 11

Solved Threads: 2

Newbie Poster

Offline

15 posts
since May 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: incrementing the number

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Sql Statement issue

DaniWeb Message

Cancel Changes

User Name
Password