DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Excel Object

07.18.2007
| 3054 views |
  • submit to reddit
        // Excel object
// 'create object
Set excelobj = CreateObject( "Excel.Application")
Set exp_data_workbook=excelobj.workbooks.open("c:\debug_expected.xls")


'get the sheet count
exp_sheetcnt=exp_data_workbook.Sheets.Count
	

'select a sheet
For i = 1 to exp_sheetcnt
	sheet_tblname=exp_data_workbook.Sheets(i).Name
	If  UCASE(Trim(sheet_tblname)) = "Sheet_name" Then
		exp_data_workbook.Sheets(i).select

		'once selected set the range object	
		set exp_rangeobj=exp_data_workbook.Sheets(i).UsedRange
	end if
next


'Get the column count for the 

columncnt=exp_rangeobj.columns.count

'find  the column index of  the column column_name
		For i=1 to columncnt
			colname=exp_rangeobj.cells(1,i).value
			If UCASE(Trim(colname)) = "column_name" Then
				pos_id_idx = i
				Exit For
			End If
		Next		

'proceess the value from that column

rowcnt=exp_rangeobj.rows.count

		'Strat reading the values from the 2nd row since the first row contains the column names.
		If rowcnt > 1 Then
			For i=2 to rowcnt				 
				'process the expected file row if it has a value
				If  Len(Trim(exp_rangeobj.cells(i,1).value))>0 Then 
					posIndex = (exp_rangeobj.cells(i,pos_id_idx).value) -1
				end if
			next 
		end if
		
    

Comments

Snippets Manager replied on Fri, 2010/09/03 - 8:18am

how to add an new element to the excel by using this code.... NewSheet.Cells(row,col) = variable or string how to set the cells row,col value..... if i give any variable name and increment it....show an run time error... pls give me a solution