Thursday, April 30, 2009

How to find the number of rows and columns used in an Excel sheet

strFilePath="D:\EmpDetails.xls"
Dim fileSysObj
Set xlObj=createobject("Excel.application")
Set xlWorkBookObj=xlObj.workbooks.open(strFilePath)
Set xlWorkSheetObj=xlWorkBookObj.worksheets(1)

intRowCount=xlWorkSheetObj.UsedRange.rows.count 'intRowCount=xlWorkSheetObj.rows.count - it returns the number of rown in an Excel sheet

intColCount=xlWorkSheetObj.UsedRange.columns.count
' intColCount=xlWorkSheetObj.columns.count - it returns the number of columns in an Excel sheet

msgbox "No. of rows used "&intRowCount&" and No. of columns used. "&intColCount

xlWorkBookObj.close
xlObj.application.quit

Set xlObj=nothing
Set xlWorkBookObj=nothing
Set xlWorkSheetObj=nothing

No comments: