How to read values of an excel worksheet cells into an array in visual basic

Hi experts:

I am getting a set of words from the back end and I am writing them back into a specific worksheet of an excel file in the following fashion. I am also pasting the code for getting values from the data table:

 objConn1.ConnectionString = "Provider=SQLOLEDB;database=" & LibName & ";SERVER=" & Srvr & ";User id=sa;password=dserv"
 objConn1.Open
 Set objcommand1.ActiveConnection = objConn1        
 sqlStatement1 = "SELECT DISTINCT USR.U_NAME FROM " & LibName & ".dbo.USR USR, " & LibName & ".dbo.ELEMENT ELE WHERE ELE.E_INA07 IS NULL AND USR.U_NAME=ELE.E_OWNER "
 objcommand1.CommandText = sqlStatement1
 Set recset = objcommand1.Execute
 recset.MoveFirst

For example purpose, let us say I have a workbook with three worksheets (Sheet1, Sheet2 and Sheet3). And worksheet (Sheet3) has the following two columns with corresponding values

Header 1(In Column A)             Header 2 (In Column B)
Value1                                        Valu1A
Value2                                        Value2A
Value3                                        Value3A

I am trying to read values off of an excel file and I want to load the values of each cell into an array.
For e.g.
In the above example, I want to read all the values under Header 1 into vArray1 and values under header 2 into vArray2. But at the same time both the values should be properly related - meaning - if I want to print the value of vArray1 and vArray2 at index 1, then I should get Value1 and Value1A

Thanks for all the help offered
Jammer
JammerulesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JammerulesAuthor Commented:
Thanks a bunch. Will try it and let you know the results.
0
 
JammerulesAuthor Commented:
Hello oleggold,

I have gone thru the examples in MSDN from the link you gave and did not find them much of help to me. One of the articles talks about writing and manipulating data in the excel workbook using ADO object. My scenario is much beyond that point. I have the data already existing in the worksheet and all that I need is to read the values of each cell into an array.
Any further SIMPLE ideas?
0
 
JammerulesAuthor Commented:
Here is my solution. It worked for me after a lot of research. Thanks for oleggold for guiding me!


Public Sub dbGetData()
Dim iCnt, z As Integer
Dim oSheet As New Excel.Worksheet
Dim oBook As New Excel.Workbook

Set oBook = GetObject("c:\Output.xls")
oBook.Activate

iCnt = 1
'    objConn1.ConnectionString = "Provider=SQLOLEDB;database=" & LibName & ";SERVER=" & Srvr & ";trusted_connection=yes"
    objConn1.ConnectionString = "Provider=SQLOLEDB;database=" & LibName & ";SERVER=" & Srvr & ";User id=sa;password=dserv"
    objConn1.Open
    Set objcommand1.ActiveConnection = objConn1
   
    sqlStatement1 = "SELECT DISTINCT USR.U_NAME FROM " & LibName & ".dbo.USR USR, " & LibName & ".dbo.ELEMENT ELE WHERE ELE.E_INA07 IS NULL AND USR.U_NAME=ELE.E_OWNER "
    objcommand1.CommandText = sqlStatement1
   
    Set recset = objcommand1.Execute
    recset.MoveFirst
    reccount = 0
       
    If Not recset.EOF Then
        recset.MoveFirst
        Do While Not recset.EOF
          DataArray(iCnt, 1) = StrConv(recset(0), 1)
          iCnt = iCnt + 1
          recset.MoveNext
        Loop
    Else
    End If
   
    z = 1
    iCnt = iCnt - 1
    Do While z <= oBook.Worksheets.Count
        If StrComp((oBook.Worksheets(z).Name), (StrConv(Trim(LibName), 1)), vbTextCompare) = 0 Then
            oBook.Worksheets(z).Range("A1").Value = "USER ALIAS"
            oBook.Worksheets(z).Range("A1").Font.Bold = True
            For Each Cell In oBook.Worksheets(z).Range("A2:A100")
              If iCnt > 0 Then
                Cell.Value = DataArray(iCnt, 1)
                iCnt = iCnt - 1
              End If
            Next Cell
             

            For Each Col In oBook.Worksheets(z).Columns
              Col.ColumnWidth = 30
            Next Col
        End If
        z = z + 1
    Loop
       
    oBook.Save
    objExcelApp.Quit
    recset.Close
    objConn1.Close

End Sub
0
All Courses

From novice to tech pro — start learning today.