To Query a Excel Sheet as a database


To Query a Excel Sheet as database   , use following code:
Steps:

1) Create an ADODB connection  and recordset

2) Open connection

3) Create a Recordset

4) Execute SQL and store results in reocrdset

5) Read all fields data   and store in a array

6) Close and Discard all variables


Code:
'sFileName= xls file name with path
'Sheetname = table name
'VRstatmt = where statment for query
'Colname=Column name to fetch value from
'==============================================
Public Function QryXls_GetData( sFileName, SheetName, VRstatmt,Colname )
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = "&H0001"

sql_text="Select * FROM [" & SheetName & "$]" & VRstatmt
 '1) Create an ADODB connection and recordset
Set objConnection = CreateObject("ADODB.Connection")

'2) Open connection'
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"

'3) Create a Recordset'
Set objRecordSet = CreateObject("ADODB.Recordset")

'4) Execute SQL and store results in reocrdset'
objRecordset.Open sql_text , objConnection, adOpenStatic, adLockOptimistic, adCmdText

'5) Read all fields data   and store in a array'
'For Multiple records
'=================
ReDim  SQLExpectedData(objRecordset.recordcount -1)
For i=0  to objRecordset.recordcount -1  'objRecordset.fields.item(1).properties.count
    SQLExpectedData(i)= objRecordset.fields(Colname)
    objRecordset.movenext
Next

'6) Close and Discard all variables '
objRecordset.Close
objConnection.Close
End Function

Create and update a excel File at given path

To Create a Excel File at given path, use following code:
Steps:
1) Create a Excel Object

2) Add a workbook

3) write data in sheet

4) Save File to a location

5) Close workbook and Excel



Code:


'Create a Xls File at given path
'==============================================
Function Create_Update_Xls(File_Location)
'1) Create a Excel Object'
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  objExcel.DisplayAlerts = False

'2) Add a workbook '  
  Set wb = objExcel.Workbooks.Add()

'3) write data in sheet
  wb.Sheets("Sheet1").Cells(1,1).Value = "Test Data1"
  wb.Sheets("Sheet1").Cells(2,2).Value = "Test Data2"
  wb.Sheets("Sheet1").Cells(3,3).Value = "Test Data3"
  wb.Sheets("Sheet1").Cells(4,4).Value = "Test Data4"
  wb.Sheets("Sheet1").Cells(5,5).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(6,6).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(7,7).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(8,8).Value = "Test Data5"
 
'4) Save File to a location'  
  wb.SaveAs File_Location
 
'5) Close workbook and Excel
  wb.Close
  objExcel.Quit
End function

Open a Password Protected excel and save as unprotected

 To Open a password Protected excel file, use following code:
 
 
1) Create a Excel application object

2) Set Visible property as True

3) Open Workbook and activate

4) Save sheets without any password

5) Close Workbook  and excel

6) Discard all variables

'1) Create a Excel application object'
     Set objExcel=CreateObject("Excel.Application")

'2) Set Visible property as True     
     objExcel.Visible=True
     fileName ="C:\......\abc.xls"
     excelpwd="pass"
 
   Set oWorkbook=objExcel.Workbooks
'3) Open Workbook and activate
   Set myWkbook=objExcel.Workbooks.open (fileName,0,False,5,excelpwd,excelpwd)
   objExcel.DisplayAlerts=False
   oWorkbook(fileName).Activate

'4) Save sheets without any password'
   For Each wSheet in objExcel.Workbooks
        wSheet.SaveAs fileName,,"",""
   Next
'5) Close Workbook  and excel
   objExcel.Workbooks.Close
   objExcel.Quit
'6) Discard all variables '   
   Set oWorkbook=Nothing
   Set objExcel=Nothing   

Find a value in Excel Sheet


To Find a value in excel sheet, use following code:
Steps:
1) Create a Excel application object

2) Open Excel file

3) Select sheet

4) Find the matching value

5) change cell color to Gray

6) Save the sheet

7) close the Excel file


Code:

filepath="C:\......\abc.xls"
matchdata="test"

'1) Create a Excel application object'
Set appExcel = CreateObject("Excel.Application")
appExcel.visible=true

'2) Open Excel file'
 Set objWorkBook = appExcel.Workbooks.Open (filepath)'opens the sheet

 '3) Select sheet

 Set objSheet = appExcel.Sheets("Sheet1")' To select particular sheet
With objSheet.UsedRange ' select the used range in particular sheet
'4) Find the matching value'
            Set finddata = .Find (matchdata)' data to find
        For each excelcell in objSheet.UsedRange' Loop through the used range
                     If finddata=matchdata then' compare with the expected data
                     '5) change cell color to Gray'
                                excelcell.Interior.ColorIndex = 40' make the gary color if it finds the data
                    End If
                    Set excelcell = .FindNext(excelcell)' next search
              
        next
End With

'6) Save the sheet
'
objWorkBook.save

'7) close the Excel file'
objWorkBook.close
set appExcel=nothing

Save as a Password Protected Excel sheet

To Open existing excel sheet and save a password protected excel spreadsheet, use following code:

1)  Create a Excel object

2) Set Visible property as True

3) Open workbook and activate

4) Save workbook with a password.

5) Close workbook and Excel

6) Discard all variables

 

'1)  Create a Excel object
     Set objExcel=CreateObject("Excel.Application")

'2) Set Visible property as True
     objExcel.Visible=True
     fileName ="C:\......\abc.xls"
     excelpwd="pass"

'3) Open workbook and activate
     Set oWorkbook=objExcel.Workbooks
     Set outputWkbook=objExcel.Workbooks.open (fileName,0,False)
     oWorkbook("sheet1").Activate
     objExcel.DisplayAlerts=False 

'4) Save workbook with a password.
     outputWkbook.SaveAs fileName,,excelpwd,excelpwd

'5) Close workbook and Excel  
     outputWkbook.Close
     objExcel.Workbooks.Close
     objExcel.Quit

'6) Discard all variables

     Set outputWkbook=Nothing
     Set objExcel=Nothing