MS EXCEL MACRO/VBA CODES & UTILITIES
PART I :
Excel VBA/Macro/VBScript codes.
Introduction:
MS Excel is very widely used application over the world from many years. We found almost every application either uses it as a input or output reporting or mid processing spreadsheets or easy level databases. Some tools like QTP includes excel file as a integrated data input or data manipulations or reporting purpose. In addition to this, some frameworks drivers are excel utilities itself.
Hence get handle of MS Excel is very essential for a developer who is dealing with front end or backend of application with excel.
So, here is good start to learn “How to get handle of excel objects” (Below codes are VB Oriented/ used codes. intelisance is dependent on VB editor.)
Let’s start with basics of excel object.
Create Excel Object:
( Object is a instance of an class and need to be created to get handler for efficient/reusable programming.
If Excel file is not opened, and need to create new instance of excel.
Set objExcel = CreateObject(“Excel.Application”)
If excel file is allready opened and need to get handle for current opened excel file.
Set objExcel = GetObject(,”Excel.Application”) ‘ First Paremeter is empty, it might give syntax error or warnings)
Display settings and alerts
To operate excel in invisible mode Visible Mode
objExcel.visible=False objExcel.visible=True
To prevent excel errors or alerts ( Some time it gives compatibility issues or version errors.)
objExcel.AlertBeforeOverwriting=False
objExcel.DisplayAlerts=False
Open Excel file from Path.
Set workbook object to open excel file as current workbook.
Set objWorkBook = objExcel.Workbooks.Open (sFilePath)
Working with Worksheets
- Create Object of WorkSheet
Set objSheet = objExcel.Sheets(“SheetName”)
- Get Count and name of present worksheets.
iTotalSheets = oExcel.Sheets.Count
For iSheets = 1 to iTotalSheets
Excel.Sheets(iSheets).Name
Next
- Delete all worksheets except specific sheet.
iTotalSheets = oExcel.Sheets.Count
For iSheets = 1 to iTotalSheets
If Trim(LCase(oExcel.Sheets(iSheets).Name)) <> “SheetName” Then
oExcel.Sheets(iSheets).Delete
End If
Next
- Activate specific sheet
objExcel.Worksheets(“SheetName”).Activate
- Add New Worksheet to workbook.
oExcel.Sheets.Add
oExcel.Sheets(“Sheet1”).Select
oExcel.Sheets(“Sheet1”).Name = sWorkSheetName
- Save Worksheet
objExcel.ActiveWorkbook.Save
- Insert new row into Worksheet.
objExcel.ActiveCell.EntireRow.Insert – Select Cell firstly
Working with Cells
- Resize Cells
oExcel.Sheets(sWorkSheetName).Columns(1).ColumnWidth = 20
oExcel.Sheets(sWorkSheetName).Columns(2).ColumnWidth = 40
- Customizing Cells
oExcel.Sheets(sWorkSheetName).cells(1,iCell).Font.Name = “Arial” – Set Font
oExcel.Sheets(sWorkSheetName).cells(1,iCell).Font.Size = “12” – Set Font size
oExcel.Sheets(sWorkSheetName).cells(1,iCell).Font.Bold = True – Bold
oExcel.Sheets(sWorkSheetName).cells(1,iCell).Interior.ColorIndex = “37”- Set Cell color
- Insert values into Cells
oExcel.Sheets(sWorkSheetName).Select
oExcel.Sheets(sWorkSheetName).Cells(1,1).Value = “Text”
- Erase Cell contents
oExcel.Sheets(sWorkSheetName)..Range (sCellPosition).ClearContents
- Select Cell with cell position
objSheet.Range(sCellPosition).Select
- Get Cell contents
Select cell and get contents
objExcel.ActiveCell.Value
objExcel.Range(Column&””&Row).Value
- Get ‘n’ consecutive values in row from selected cell.
For iCnt =0 to n
objExcel.ActiveCell.Offset(0,iCnt).Value
Next
- Get ‘n’ consecutive values in Column from selected cell.
For iCnt =0 to n
objExcel.ActiveCell.Offset(iCnt,0).Value
Next
Find Text in active worksheet
Set objvalueFind=objSheet.UsedRange.Find(“TextTOFind”)
If Not objvalueFind is Nothing Then
‘Capture address
sAddress=objvalueFind.Address
End If
Find Text in active worksheet iteratively.
Set objvalueFind=objSheet.UsedRange.Find(sDocumentName) ‘ Find document
If Not objvalueFind is Nothing Then
sFirstAddress =objvalueFind.Address ‘ Capture addres
sCellPosition= Split address with $ , OR Replace $ with blanks
End If
If objExcel.Range(sCellPosition).Value = TextTOFind Then
‘Condition True
Else
Do Until objvalueFind is Nothing AND objvalueFind.Address<> sFirstAddress
Set objvalueFind=objSheet.UsedRange.FindNext (objvalueFind)
If Not objvalueFind is Nothing Then sAddress=objvalueFind.Address
sCellPosition= Split address with $ , OR Replace $ with blanks
If objExcel.Range(sCellPosition).Value = TextTOFind Then
Exit Do
End If
End If
Find Merged area of selected cell.
If objExcel.ActiveCell.MergeCells Then ‘ If cell has merged then
GroupRange=objExcel.ActiveCell.MergeArea.Address
End If
Calculate Merged column count
sGroupRange=
sStart= Cell Position of starting area ‘ start cell of merged area
sEnd= Cell Position of End area ‘ end cell of merged area
iMergedCol= CInt(objExcel.Range(sEnd).Column)-CInt(objExcel.Range(sStart).Column)
Part II …….
!! शि.सा.रा. उवाच !! 