0

Tools: asp.net VB, SpreadsheetGear 2010

I need to read in Excel files from external users and I do not control the format. There are 3 sections of data. I am able to read in the header. The challenge is that the body details can contain an infinite number of rows, and sometimes those rows are blank. I can determine the footer row because there is a piece of text that is unique. Therefore I can perform the following:

Dim dFoot As SpreadsheetGear.IRange = worksheet.Cells("20:21").Rows

Dim rngDet As SpreadsheetGear.IRange  

rngDet = dFoot.Find(what:="UNIQUE TEXT HERE", 
                    lookIn:=SpreadsheetGear.FindLookIn.Values, 
                    lookAt:=SpreadsheetGear.LookAt.Whole, 
                    searchOrder:=SpreadsheetGear.SearchOrder.ByColumns, 
                    searchDirection:=SpreadsheetGear.SearchDirection.Next, 
                    matchCase:=False, 
                    after:=dFoot)

Unfortunately, I am now stuck here. I cannot determine where the cell is (i.e. V79) or how to move 7 columns over and 3 down to get the first value for inserting. I have tried offset and different methods of reading the rngDet, but no luck. I have also tried rngDet.Activate() to read the active cell, but it does not work the same as Microsoft.

I appreciate any help you can provide.

Adam Lear
  • 38,111
  • 12
  • 81
  • 101
user1821800
  • 21
  • 2
  • I don't quite follow what what exactly you are trying to do. For instance, what cell are you trying to find in relation to your 3 sections of data? Are you trying to insert additional cells or rows at this point? What is the significance of moving 7 columns over and 3 down? Feel free to elaborate on your requirements. – Tim Andersen Nov 14 '12 at 17:36

1 Answers1

0

Tim, Thank you for your questions. First time asking so I probably was not very clear. I did find the answer for myself today. I have a group of cells that I need to upload. The problem is that they will be moving up and down the page depending on how many rows were entered in the section above. I realized that I did know what column it was going to be in so I performed the following:

Dim x As Integer
    Dim xFooter As Integer
    Dim columnnumber As Integer = 21 'Column V

    txtTest.Text = worksheet.Cells.RowCount
    For x = 500 To 1 Step -1

        If worksheet.Cells(x, columnnumber).Value = "TOTAL PRODUCT COST CALCULATION" Then
            txtTest.Text = x
            xFooter = x
            Exit For
        End If
    Next x

I am now able to use my x value as the left hand corner of my group of cells. I know that the values go over 7 columns from here and down 20 rows which gives me my quandrant of values.

Thought I would share just in case anyone has the same problem.

user1821800
  • 21
  • 2