Referencing Excel in VB Documentation ?????

[Paul282]

I have a project to manipulate Excel in VB.

I can open and read basic column info into VB arrays but there is a lot more to the RANGE object etc (from looking in the object browser)

anyone know where there is some documentation or tips web pages on reading excel data in VB??

I'm after getting details like:

Sheet count
First Row in row count (the row count figure starts at the first row with data, so if only row 23 - 25 have data, return of rowcount is 3)

First Col in col count (same as above)
data type in cell

just for starters anyway

[Judd]

Paul,

The range object is a bit of a ***** to get your head around.

But here is some stuff that might help:

'PURPOSE: Count the number of records
Dim intLastRow
  
'Method 1
intLastRow = ActiveCell.SpecialCells(xlLastCell).Row
  
'Method 2
intLastRow = ActiveSheet.UsedRange.Rows.Count
  
'Method 3
intLastRow = ActiveCell.EntireRow.Count

These could be adapted for counting columns,

The Workbooks collection has both a Sheets and a Worksheets collection (identical) which have a Count method.

[Paul282]

THX Dood,

I used "ActiveSheet.UsedRange.Rows.Count" previously and it returned the number of used rows.

The first row was blank in that column so it counted from row 2

How do I find out where the data starts? If I ran a loop to grab the data into an array and started at row 1 then I'd miss the last row!

columns had the same problem.

I have to read a excel sheets into a DB but the format is not specified.

Dynamic DB I can handle, Dynamic Excel is a *****!

[Judd]

Paul,

Is this what you want:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim rRng As Range
    
    Set rRng = ActiveSheet.UsedRange
    
    MsgBox rRng.Address()
    MsgBox rRng.Address(ReferenceStyle:=xlR1C1)
    
End Sub

Range.Address returns a string which can be parsed to get the absolute location of the data range. However, this works only from the upper left corner to the lower right.
Rows and columns in between can be blank. That shouldn't be a problem if your data is contiguous, but otherwise you'd have to do a for each row/column in usedrange, and check if the data is there (you could nest a for each cell in row/column too if you want).

[Paul282]

I don't think so,

The Excel sheets don't need to be opened to view and won't be being edited while I read them.

I just need to read the data from an entire sheet to write to a DB but the sheet may be of any size.

EG:

500 rows
20 cols

first row starts at row 10
first col starts at col B

basically a square table
read to 2d array or array of UDT

User will select xls file from dialog control.

I can open it
I can read a set number of rows
I'm just not sure how to get the rows I want because the row and col counts in the range object ironically enough, don't specify a range! just the number of rows but not the first and last.

I need this info from the objects because the software only displays progress bars for what it's doing, the user can't check or correct the fact that the rows are correct.

[Judd]

UsedRange is a property of Worksheet, so if you can open the sheet, you can used UsedRange. The address of used range can be used both to determine the number of rows and columns and where they start.

From you example in R1C1 style:

UsedRange.Address(ReferenceStyle:=xlR1C1) = "R10C2:R509C21"

UsedRange.Rows.Count = 500
UsedRange.Columns.Count = 20

The UsedRange is intrinsically located.

Try:

(psuedo code)
OpenWorkbook

Dim MyRange as Range
Dim MyCell as Cell

Set MyRange = MySheet.UsedRange

For Each MyCell in MyRange

    MsgBox Location MyCell.Address & " " & MyCell.Value

Next MyCell

This will cycle through all the cells in a range, by column, then row (i.e. A1, A2, A3, B1, B2, B3 ... etc)

If you want to 'locate yourself' within your used range use:

UsedRange.Cells(1,1)

this is the upper left corner of your range.

To traverse the range you can then use:

UsedRange.Cells(1,1).Offset(r,c)

Offset is a row and column offset from the original location.

It should also be relatively easy, knowing the bounds and location of your UsedRange, to construct subranges relating to rows and/or columns if necessary.



[Edited by Judd on 06-13-2000 at 06:52 AM]

[Paul282]

THANK YOU

Sorry for not catching on earlier...

Onya, You're a Bloody legend!

[Judd]

Good to know all the fighting I've done with the perculiarities of the Excel Object Model has not been wasted!

[Paul282]

Thanks Judd,
Here's what I ended up with.
I think you saved me about 3 days of pissing about by myself... Think I'll take a long lunch

' Module Code

Public Type Excell_Cell
    Address As String
    value As String
End Type

Public Cells() As Excell_Cell

'Form Code

Private Sub Command1_Click()

    Dim FileName As String
    Dim wkbObj As Workbook
    
    Dim MyRange As Range
    Dim MyCell As Range ' you wrote "cell" but I couldn't find that object
    Dim i As Long
    
    Diag1.ShowOpen
    FileName = Diag1.FileName
    
    If Len(FileName) < 3 Then Exit Sub

    Set wkbObj = GetObject(FileName)

    Set MyRange = wkbObj.Worksheets(1).UsedRange


    ReDim Cells(MyRange.Cells.Count)

    For i = 1 To MyRange.Cells.Count

        Cells(i).Address = MyRange.Cells(i).Address
        Cells(i).value = MyRange.Cells(i).value
        
    Next



End Sub

[Judd]

I wrote 'Cell' but I meant 'Range', as you cleverly deduced....

[Paul282]

hmmmmmmmm...