just to correct.... *Non empty cells* Regards Shalabh Dixit
On Wed, Sep 14, 2011 at 10:17 AM, Shalabh Dixit <[email protected]>wrote: > Supeeth, > > Use the code below. It will give you data of all the non empty rows into an > array of arrays with each sub array containing data for each row. > > *'<Sample Example For Function Usage>* > GetNonEmptyCellsData "C:\Shalabh\Automation Framework\Templates\Copy of > Views_File_Names_Updated.xls","Sheet1" > *'</Sample Example For Function Usage>* > > Function GetNonEmptyCellsData(str_xlFilePath,str_xlSheetName) > Dim arr_Temp_DataStore(),arr_Main_DataStore(),int_Empty_Cells > 'Intializing Excel Objects > Set xlApp = CreateObject("Excel.Application") > Set xlWBook = xlApp.Workbooks.Open(str_xlFilePath) > Set xlWSheet = xlWBook.Worksheets(str_xlSheetName) > '-------------------------------------------------------- > 'Get Total Rows and Columns in the Sheet > int_Total_Rows = xlWSheet.UsedRange.Rows.Count > int_Total_Cols = xlWSheet.UsedRange.Columns.Count > '-------------------------------------------------------- > For int_xl_Row = 1 To int_Total_Rows > int_Empty_Cells = 0 > For int_xl_Col = 1 To int_Total_Cols > If xlWSheet.Cells(int_xl_Row,int_xl_Col) <> "" Then > ReDim Preserve arr_Temp_DataStore(int_xl_Col - 1) > 'Copying Data of Each Row column-wise into a Temporary > Array > arr_Temp_DataStore(int_xl_Col - 1) = > xlWSheet.Cells(int_xl_Row,int_xl_Col).Value > Else > int_Empty_Cells = int_Empty_Cells + 1 > End If > 'If No. of Empty Cells > 1 then move to Next Row > If int_Empty_Cells > 1 Then > Exit For > End If > Next > ReDim Preserve arr_Main_DataStore(int_xl_Row - 1) > 'Copying Final Data of Each Row column-wise into a Main Array > arr_Main_DataStore(int_xl_Row - 1) = arr_Temp_DataStore > Next > '-------------------------------------------------------- > 'Closing Excel Worbook & Application > xlWBook.Close > xlApp.Quit > '-------------------------------------------------------- > 'Destroying All Excel Objects > Set xlApp = Nothing > Set xlWBook = Nothing > Set xlWSheet = Nothing > GetNonEmptyCellsData = arr_Main_DataStore > End Function > > > Regards > Shalabh Dixit > > > > > On Wed, Sep 14, 2011 at 6:43 AM, Ganesh muralidharan > <[email protected]>wrote: > >> Pasting a sample code in which I am just taking a count of number of non >> blank cells. You can try out some similar logic >> >> Set ObjIPExcel = CreateObject("Excel.Application") >> ObjIPExcel.DisplayAlerts = False >> ObjIPExcel.Workbooks.Open FilePath >> ObjIPExcel.visible=true >> Set ObjIPExcel_Wb=ObjIPExcel.Activeworkbook >> Set strIPSheet = ObjIPExcel_Wb.Sheets(1) >> >> intIPCol=1 >> Do while strIPSheet.Cells(1,intIPCol)<>"" >> intIPCol=intIPCol+1 >> Loop >> >> On Tue, Sep 13, 2011 at 9:05 PM, supreeth mc <[email protected]>wrote: >> >>> Hello People, >>> >>> This is my first request on this Blog can any one help me out on >>> this :- >>> I need to compare blank cells in excel row wise is there are more then >>> one blank cells then I have to jump to next row.... >>> >>> >>> >>> for example:- >>> >>> SYID >>> INFO >>> Login UN PA >>> INFO INFO >>> URL Web Browser Buttona >>> INFO INFO >>> Result Module1 Module 2 Module 3 >>> INFO INFO >>> PS: I need to fetch INFO from all these rows can any one help me with >>> the codes ....Plz:) >>> >>> -- >>> You received this message because you are subscribed to the Google >>> "QTP - HP Quick Test Professional - Automated Software Testing" >>> group. >>> To post to this group, send email to [email protected] >>> To unsubscribe from this group, send email to >>> [email protected] >>> For more options, visit this group at >>> http://groups.google.com/group/MercuryQTP?hl=en >> >> >> >> >> -- >> Regards, >> >> >> Ganesh Muralidharan >> 'God Never Forgets' >> >> -- >> You received this message because you are subscribed to the Google >> "QTP - HP Quick Test Professional - Automated Software Testing" >> group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/MercuryQTP?hl=en >> > > -- You received this message because you are subscribed to the Google "QTP - HP Quick Test Professional - Automated Software Testing" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/MercuryQTP?hl=en
