We can connect to excel file using Adodb
object in vbscript. Following are the
steps in working with excel as database.
1. Create an instance of connection :
Set objConn = CreateObject("Adodb.connection")
2. Create connection string to connect to excel database as follows
strCon = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;Dbq=" & Workbook &";"
Here we have created a database for excel
Workbook . The sheets in the workbook acts as table of the database.
3. Connect to database as shown below:
objConn.open strCon
Now we are done with the connection to excel ,
we require SQL to run on the data to extract required information .
4. Create a SQL as required as shown below:
strSQL = "select name from [Table1$];"
Note here, we have to give sheet name
acting as table within square bracket and with suffix “$” as shown above.
5. Create a recordset as shown below and execute the query using recordset, all the records based on query will be stored in recordset.
Set rs = CreateObject("ADODB.recordset")
rs.Open strSQL,objConn
6. We can transfer the data extracted from query back in excel using copyrecordset method
XLSheet.range("A2").copyfromrecordset rs
Where XLSheet is the reference of required
sheet in the excel file and A2 is the
location in sheet from which data will be pushed in the sheet.
No comments:
Post a Comment