Create excel file and enter some data save it
view plainprint?
'###############################################
'Create excel file and enter some data save it
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Add New Workbook
Set workbooks=excel.Workbooks.Add()
'Set the value in First row first column
excel.Cells(1,1).value="testing"
'Save Work Book
workbooks.saveas"D:\excel.xls"
'Close Work Book
workbooks.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set workbooks=Nothing
Set excel=Nothing
Reading Values from a Specific excel Sheet
view plainprint?
'###############################################
' Reading Values from a Specific excel Sheet
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open the Excel File
Set workbook=excel.Workbooks.Open("D:\excel.xls")
'Get the Control on Specific Sheet
Set worksheet1=excel.Worksheets.Item("Sheet1")
' Display the Values
Msgbox worksheet1.cells(1,1).value
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set worksheet1=Nothing
Set workbook=Nothing
Set excel=Nothing
Deleting Rows from Excel Sheet
view plainprint?
'###############################################
' Deleting Rows from Excel Sheet
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open the Excel File
Set workbook=excel.Workbooks.Open("D:\excel.xls")
'Get the Control on Specific Sheet
Set worksheet1=excel.Worksheets.Item("Sheet1")
'Delete Row1
worksheet1.Rows("1:1").delete
'Save Excel
workbook.SaveAs("D:\excel.xls")
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set worksheet1=Nothing
Set workbook=Nothing
Set excel=Nothing
Add and Delete ExcelSheet
view plainprint?
'###############################################
' Add and Delete ExcelSheet
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open Existing Excel File
Set workbook=excel.Workbooks.Open("D:\excel.xls")
'Add New Sheet
Set newsheet=workbook.sheets.Add
'Assign a Name
newsheet.name="raj"
'Delete Sheet
Set delsheet=workbook.Sheets("raj")
delsheet.delete
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set newsheet=Nothing
Set delsheet=Nothing
Set workbook=Nothing
Set excel=Nothing
Copy an Excel Sheet of one Excel File to another Excel File
view plainprint?
'###############################################
' Copy an Excel Sheet of one Excel File to another Excel File
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open First Excel File
Set workbook1=excel.Workbooks.Open("D:\excel1.xls")
'Open Second Excel File
Set workbook2=excel.Workbooks.Open("D:\excel2.xls")
'Copy data from first excel file sheet
workbook1.Worksheets("raj").usedrange.copy
'Paste Data to Second Excel File Sheet
workbook2.Worksheets("Sheet1").pastespecial
'Save Workbooks
workbook1.Save
workbook2.Save
'Close Workbooks
workbook1.Close
workbook2.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set workbook1=Nothing
Set workbook2=Nothing
Set excel=Nothing
Comapre Two Excel Sheets Cell By Cell for a specific Range
view plainprint?
'###############################################
' Comapre Two Excel Sheets Cell By Cell for a specific Range
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open Excel File
Set workbook=excel.Workbooks.Open("D:\excel.xls")
'Get Control on First Sheet
Set sheet1=excel.Worksheets.Item("Sheet1")
'Get Control on Second Sheet
Set sheet2=excel.Worksheets.Item("Sheet2")
'Give the specific range for Comparision
CompareRangeStartRow=1
NoofRows2Compare=4
CompareRangeStartColumn=1
NoofColumns2Compare=4
'Loop through Rows
For r=CompareRangeStartRow to(CompareRangeStartRow+(NoofRows2Compare-1))
'Loop through columns
For c=CompareRangeStartColumn to(CompareRangeStartColumn+(NoofColumns2Compare-1))
'Get Value from the First Sheet
value1=Trim(sheet1.cells(r,c))
'Get Value from the Second Sheet
value2=Trim(sheet2.cells(r,c))
'Compare Values
If value1<>value2 Then
' If Values are not matched make the text with Red color
sheet2.cells(r,c).font.color=vbred
End If
Next
Next
'Save workbook
workbook.Save
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set sheet1=Nothing
Set sheet2=Nothing
Set workbook=Nothing
Set excel=Nothing
Reading complete data from excel file
view plainprint?
'###############################################
' Reading complete data from excel file
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open Excel File
Set workbook=excel.Workbooks.Open("D:\excel.xls")
'Get Control on Sheet
Set worksheet=excel.Worksheets.Item("raj")
'Get the count of used columns
ColumnCount=worksheet.usedrange.columns.count
'Get the count of used Rows
RowCount=worksheet.usedrange.rows.count
'Get the Starting used Row and column
top=worksheet.usedrange.row
lft=worksheet.usedrange.column
'Get cell object to get the values cell by cell
Set cells=worksheet.cells
'Loop through Rows
For row=top to (RowCount-1)
rdata=""
'Loop through Columns
For col=lft to ColumnCount-1
'Get Cell Value
word=cells(row,col).value
'concatenate all row cell values into one variable
rdata=rdata&vbtab&word
Next
'Print complete Row Cell Values
print rdata
Next
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set worksheet=Nothing
Set workbook=Nothing
Set excel=Nothing
Read complete data from an Excel Sheet content
view plainprint?
'###############################################
' Read complete data from an Excel Sheet content
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Open Excel File
Set workbook=excel.Workbooks.open("D:\excel.xlsx")
'Get Control on Sheet
Set worksheet=excel.Worksheets.Item("Sheet1")
'Get Used Row and Column Count
rc=worksheet.usedrange.rows.count
cc=worksheet.usedrange.columns.count
'Loop through Rows
For Row=1 to rc
'Loop through Columns
For Column=1 to cc
'Get Cell Data
RowData=RowData&worksheet.cells(Row,Column)&vbtab
Next
RowData=RowData&vbcrlf
Next
'Display complete Data
msgbox RowData
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set worksheet=Nothing
Set workbook=Nothing
Set excel=Nothing
Assign Colours to Excel Sheet Cells, Rows
view plainprint?
'###############################################
' Assign Colours to Excel Sheet Cells, Rows
'###############################################
'Create Excel Object
Set excel=createobject("excel.application")
'Make it Visible
excel.Visible=True
'Add a New work book
Set workbook=excel.workbooks.add()
'Get the Excel Sheet
Set worksheet=excel.worksheets(1)
'Coloring Excell Sheet Rows
Set objrange=excel.activecell.entirerow
objrange.cells.interior.colorindex=37
'Coloring Excell Sheet Cell
worksheet.cells(2,1).interior.colorindex=36
'Save Excel
workbook.SaveAs("D:\excel.xls")
'Close Work Book
workbook.Close
'Quit from Excel Application
excel.Quit
'Release Variables
Set objrange=Nothing
Set worksheet=Nothing
Set workbook=Nothing
Set excel=Nothing
No comments:
Post a Comment