How To open Password Protected Excel sheets

Function UnprotectXL(filePath,fileName,pwd,writeresPwd)
   Set objExcel=CreateObject(“Excel.Application”)
   objExcel.Visible=false
   testData=filePath&”\”&fileName
 
   Set oWorkbook=objExcel.Workbooks
 
   Set myWkbook=objExcel.Workbooks.open (testData,0,False,5,pwd,writeresPwd)
   objExcel.DisplayAlerts=False
   oWorkbook(fileName).Activate
   For Each w in objExcel.Workbooks
        w.SaveAs testData,,””,””
      
   Next
 
   objExcel.Workbooks.Close
   objExcel.Quit
   Set oWorkbook=Nothing
   Set objExcel=Nothing
  
End Function
Function ProtectXL(filePath,fileName,pwd,writeresPwd)
     On Error Resume Next
     Set objExcel=CreateObject(“Excel.Application”)
     objExcel.Visible=False
     testData=filePath&”\”&fileName
     Set oWorkbook=objExcel.Workbooks
     Set outputWkbook=objExcel.Workbooks.open (testData,0,False)
     oWorkbook(testData).Activate
     objExcel.DisplayAlerts=False  
     outputWkbook.SaveAs testData,,pwd,writeresPwd
   
     outputWkbook.Close
     objExcel.Workbooks.Close
     objExcel.Quit
     Set outputWkbook=Nothing
     Set objExcel=Nothing
  
End Function

‘Call ProtectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)
‘Call UnprotectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)

Author: Mohan Kakarla

Search for a particular value in Excel

Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
 Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet

 Set objSheet = appExcel.Sheets(“Sheet1”)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
    Set c = .Find (“nn”)’ data to find  
For each c in objSheet.UsedRange’ Loop through the used range
 If c=”nn” then’ compare with the expected data
            c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
            Set c = .FindNext(c)’ next search
       
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing

Author:Mohan Kakarla

Copy an excel sheet to another excel

Following is the code to copy the conntents of a sheet in one excel to another excel sheet

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\2.xls”)
objWorkbook1.Worksheets(“Sheet1”).UsedRange.Copy
objWorkbook2.Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste =xlValues
objWorkbook1.save
objWorkbook2.save
objWorkbook1.close
objWorkbook2.close
set objExcel=nothing

 Author: Mohan Kakarla

Compare 2 Excel sheets cell by cell

This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color  in the first sheet.
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and

Settingsmohan.kakarlaDesktopDocs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

   For Each cell In objWorksheet1.UsedRange
       If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
           cell.Interior.ColorIndex = 3’Highlights in red color if any changes in cells
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next

set objExcel=nothing
 Author: Mohan Kumar Kakarla

Excel Sorting(Ascending , Descending) By Rows and Columns

Excel Sorting By Row:

Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and Settingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1).activate

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows
set objExcel=nothing

Excel Sorting By Colum :

Const xlAscending = 1’represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

Set objExcel = CreateObject(“Excel.Application”)’Create the excel object
objExcel.Visible = True’Make excel visible
Set objWorkbook = _
objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open the

document

Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range(“A1”)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Reference: MSDN

DELETE ROWS FROM XL SHEET

DELETE ROWS FROM

 XL SHEET

 Public Function BIP_xlsDeleteRowRange (sSrcPath, sDestPath, sStartRow, sEndRow) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False

‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Rows(sStartRow +”:”+ sEndRow).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function 

DELETE COLUMNS FROM XL SHEET

DELETE COLUMNS FROM

 XL SHEET

Public Function BIP_xlsDeleteColumnRange (sSrcPath, sDestPath, sStartCol, sEndCol) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Columns(sStartCol + “:” + sEndCol).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function

ADODB CONNECTION TO READ DATA FROM EXCEL SHEET

Function GetContentFromDB (strFileName, strSQLStatement)

 

Dim objAdCon, objAdRs

 

Set objAdCon = CreateObject(“ADODB.Connection”)

 

objAdCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=”&strFileName & “;Readonly=True”

If Err <> 0 Then

      Reporter.ReportEvent micFail,”Create Connection”, “[Connection] Error has occured. Error : ” & Err

      Set obj_UDF_getRecordset = Nothing

   Exit Function

End If

Set objAdRs = CreateObject(“ADODB.Recordset”)

objAdRs.CursorLocation=3                        ‘ set the cursor to use adUseClient – disconnected recordset

objAdRs.Open strSQLStatement, objAdCon, 1, 3

 

MsgBox objAdRs.fields(4).name

While objAdRs.EOF=false

   For i=0 to objAdRs.Fields.count

Msgbox objAdRs.fields(i)

Next

objAdRs.moveNext

Wend

 

If Err<>0 Then

      Reporter.ReportEvent micFail,”Open Recordset”, “Error has occured.Error Code : ” & Err

      Set obj_UDF_getRecordset = Nothing

     Exit Function

End If

 

Set objAdRs.ActiveConnection = Nothing

 

objAdCon.Close

Set objAdCon = Nothing

 

 

 

End Function

 

Set rsAddin = GetContentsFromDB(“C:\Documents and Settings\mohank\Desktop\Login.xls”, “Select * from [Login$]”)

Author:Mohan Kakarla