Quick Learn Automation

Function RunMacro(sPath, sMacroName, sArg1, sArg2)    Set xlApp = CreateObject("Excel.Application")    Set xlWbk = xlApp.Workbooks.Open(sPath)    RunMacro = xlApp.Run(sMacroName, sArg1, sArg2)    xlApp.Quit End Function ***your macro in VBA should be public

Column headings         By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes . If you do not have column headings, you need to specify HDR=No ; the provider names your fields F1, F2, etc. Because the Extended Properties string now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values NOTE : Special characteristics can’t be accessed using ADODB Connection with Excel. deleting Excel data       You are more restricted in deleting ...

Your Excel data may be contained in your workbook in one of the following: An entire worksheet. A named range of cells on a worksheet. An unnamed range of cells on a worksheet. Specify a Worksheet To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example: strQuery = "SELECT * FROM [Sheet1$]" You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example: strQuery = "SELECT * FROM `Sheet1$`" Microsoft prefers the square brackets, which are the standing convention for problematic database object names. If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message: ... the Jet database engine could not find the specified object If you use the dollar sign but omit the brackets, you will see...

 The ConvertToLetter function works by using the following algorithm: Divide the column number by 27, and then put the resulting integer in the variable "i". Subtract the column number from "i" multiplied by 26, and then put the result in the variable "j". Convert the integer values into their corresponding alphabetical characters, "i" and "j" will range from 0 to 26 respectively. For example: The column number is 30. The column number is divided by 27: 30 / 27 = 1.1111, rounded down by the Int function to "1". i = 1 Next Column number - (i * 26) = 30 -(1 * 26) = 30 - 26 = 4. j = 4 Convert the values to alphabetical characters separately, i = 1 = "A" j = 4 = "D" Combined together, they form the column designator "AD". The following VBA function is just one way to convert column number values into their equivalent alphabetical charac...

Function Comparedata(strFileName1,strFileName2,ColumnName)       Dim iShtCnt1,iShtCnt2,strSheetName,strSQLStatement1,iCurRow,Query,iColCnt,iCol,curColName,curColValue,CurQuery    Dim bFound,j,strSQLStatement2,RecSet2,iRecordCnt     Set oBook1 = oExcel.Workbooks.Open(strFileName1)     Set oBook2 = oExcel.Workbooks.Open(strFileName2)     iShtCnt1 = oBook1.Sheets.Count     iShtCnt2 = oBook2.Sheets.Count     If iShtCnt1 <> iShtCnt2 Then Reporter.ReportEvent micFail,"Compare Report - Sheets Count Mismatch":Exit Function     oBook1.Close     oBook2.Close     oExcel.Quit     Set oExcel = Nothing     Set objCon2 = ConnectToExcel(strFileName2)     For i = 1 to iShtCnt1         strSheetName = allSheetNames(i-1)         strSQLStatement1...

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

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

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:\Jay\Docs1.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 Column : 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:\Jay\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...

'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:\Jaykrishna\Docs1.xls”) Set objWorkbook2= objExcel.Workbooks.Open(“C:\Jaykrishna\Docs2.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

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\Jay\Desktop\1.xls”) Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\jay\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

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

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...

strFileName = "C:\Jay\Sample.xls" strQuery =  "SELECT Col1,Col2 FROM SheetName$  WHERE Col1 = 1 AND Col2 = 2" Set oCurRowData = GetContentFromDB (strFileName, strQuery) If Not oCurRowData.EOF  Then         iRecordCnt = oCurRowData.RecordCount            Data = oCurRowData.Fields(i).Value End If 'DB Connection with Excel Driver Function GetContentFromDB(strFileName, strSQLStatement)      Dim objCon, objRecordSet      Set objCon = CreateObject("ADODB.Connection")      objCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"         If Err.Number <> 0 Then         Reporter.ReportEvent micFail,"Create Connection", "[Connection] Error has occured. Error : " & Err.Description         Set GetContentFromDB = Nothing   ...