none
How To Read a excel with range of cells RRS feed

  • Question

  • Hi,

    It is working fine with range of cells like A4 to x40.

     

    select * from [SHEET1$A4:X40]

     

      My case is , range starts from A4, end range is dynamically changed. how to read excel from A4 upto the data available.


    Regards,Akila

    Wednesday, April 18, 2012 12:32 PM

Answers

  • There is a worksheet.usedrange property that defines the used area on a worksheet, however AFAIK, it remains set even if the contents of ending rows or columns have been cleared. The only way the usedrange property is reset is if the row or column is deleted.

    So the technique I've used is to find the ending row or column myself. Here's some code you can experiment with.

            Dim rRng As Excel.Range
            Dim cRng As Excel.Range
            Dim sheet As Excel.Worksheet = xApp.ActiveSheet
            rRng = sheet.Range("A1").Offset(sheet.Rows.Count - 1, 0).End(Excel.XlDirection.xlUp)
            MsgBox(rRng.AddressLocal.ToString)
            cRng = sheet.Range("A1").Offset(0, sheet.Columns.Count - 1).End(Excel.XlDirection.xlToLeft)
            MsgBox(cRng.AddressLocal.ToString)
    You might also want to ask your question on the Excel Developer forum. Someone there might have a better method.


    Kind Regards, Rich ... http://greatcirclelearning.com

    Wednesday, April 18, 2012 8:57 PM