[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

VB6 and Excel

Posted on 2008-02-08
15
Medium Priority
?
388 Views
Last Modified: 2013-12-26
How can the contents of an excel workshet be read into an array in a vb6 application. There is no set number of rows in the worksheet but there is always 7 columns.
0
Comment
Question by:aidanmcg33
15 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20848881
You could use a function like this to get the last used row:

Public Function LastRowInSheet(wks As Worksheet) As Long
   ' Returns the number of the last row with data anywhere in it
   LastRowInSheet = 1
   On Error Resume Next
   With wks.UsedRange
      LastRowInSheet = .Cells.Find(what:="*", after:=.Cells(1), _
              SearchOrder:=1, _
              SearchDirection:=2).Row
   End With
End Function

then you can just use a variant to get the data:
varData = wks.Range("A1:G" & LastRowInSheet(wks)).Value

Regards,
Rory
0
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 20849046
If case you need to know how to access a worksheet from VB6

This code uses early binding, so set a reference in Project/References to the Microsoft Excel Object library.

    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim arv As Variant
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("C:\Excel Documents\Book1.xls")
    Set xlWks = xlWbk.Sheets(1)
    arv = xlWks.Range("A1:G" & xlWks.UsedRange.Rows.Count).Value
    xlWbk.Close False
    xlApp.Quit
    'process array in arv here

Open in new window

0
 
LVL 2

Expert Comment

by:rsomasundar
ID: 20849180
Yes. But, the array index starts from 1 here.  And ubound refers the no. of rows in sheet.

    Dim i As Integer, j As Integer
    For i = 1 To UBound(arv) - 1
        For j = 1 To 7
            Print arv(i, j)
        Next j
        Print
    Next i
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:aidanmcg33
ID: 20857300
im looking to read the entire worksheet into an array there are 6 columns in the worksheeet and usually about 1000 rows. i want to have the data in an array so i can loop through it and have a few extra sections in the array so i can put flags into some of the rows. so if i have     an array with field one through to field 6 i could have three more sections flag one flag two and flag three. how can this be done? i can read in the worksheet ok and reference one cell but dont know how to get entire contents into an array.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20858044
If you just needed the worksheet contents, then as I said this would work:
varData = wks.Range("A1:G" & LastRowInSheet(wks)).Value
but if you want additionaly columns to your array, you would have to loop through the rows and columns individually and grab the value from each cell

for i = 1 to LastRowInSheet(wks)
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
Next i

for example.
0
 

Author Comment

by:aidanmcg33
ID: 20858088
is there any documentation on this kind of stuff such as that lastrowinsheet and range etc. hard to find any stuff thats not .net!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20858121
LastRowInSheet refers to that function I posted earlier - it's not built in. You would ideally need an Excel VBA book (the John Walkenbach ones are good as is the Wrox Press Excel 2002 VBA) since it's the Excel object model you need to know.
Rory
0
 

Author Comment

by:aidanmcg33
ID: 20861087
but how is that stored in an array and how can i reference the values in the array?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20863177
LastRowInSheet just gets you the row number, hence its use as the top value for the row counter. You need to declare an array (called say arrData) , then populate it using something like what I posted earlier - i.e.:

for i = 1 to LastRowInSheet(wks)
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
Next i

which assumes you have already declared and instantiated wks as a Worksheet variable. You can then reference any item of the arrData array just as you normally would.
Regards,
Rory
0
 

Author Comment

by:aidanmcg33
ID: 20863193
thanks i think im gettin what you mean now but do i need to declare the size of the array at the start or do i just do arrdata = array() or do i work out total number of rows in sheet and as i know the number of fields across that i need is 9 do i do arrdata(1000,9)?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
ID: 20863212
What I would do is declare it dynamically, then resize it as you want:

Dim arrData()
Dim lngRowCount as Long
...

lngRowCount = LastRowInSheet(wks)
Redim arrData(1 to lngRowCount, 1 to 9)
for i = 1 to lngrowcount
    for j = 1 to 6
        arrData(i, j) = wks.Cells(i, j).value
    next j
    arrData(i, 7) = Flag1
    arrData(i, 8) = Flag2    
    arrData(i, 9) = Flag3
Next i

for example.

Regards,
Rory
0
 

Author Comment

by:aidanmcg33
ID: 20866180
thanks seems to work ok. how could i produce a printout of the data in the array?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20866238
Do you mean paper, or on screen? The easiest way would probably be to write it to a worksheet (or a text file) and print that if you want paper, otherwise you would use Debug.Print statments to output it to the Immediate Window.
0
 

Author Comment

by:aidanmcg33
ID: 20866907
to paper. any way to do it with crystal reports?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20867309
No idea - I don't have Crystal Reports. I'd be surprised if you could just print out an array though.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question