Monday, January 28, 2008

How to Copy data from Database to Excel sheet ?

Dim xlApp
Dim xlBook
Dim xlSheet
Dim conn,rs,sql,strConnect
Dim objWorkbook
Dim colSheets
Set conn=CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\BhavanaA\Desktop\VBscript\OFFICE.mdb"
conn.Open strConnect
sql="SELECT * FROM OF_MT_USERINFO"
Set rs=conn.execute(sql)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set objWorkbook = xlApp.Workbooks.Add("C:\Documents and Settings\BhavanaA\Desktop\VBscript\Book1.xls")
Set objWorksheet = objWorkbook.Worksheets(3)
Set colSheets = objWorkbook.Sheets
colSheets.Add ,objWorksheet,9
Set xlSheet = objWorkbook.Worksheets(9)
xlSheet.Activate
i=3
While not rs.EOF
xlApp.Range("A" & i).Value = rs("USR_LoginID")
xlApp.Range("B" & i).Value = rs("USR_Password")
xlApp.Range("C" & i).Value = rs("USR_Name")
xlApp.Range("D" & i).Value = ("USR_Role")
rs.MoveNext
i = i + 1
Wend
objWorkbook.close
xlApp.quit

No comments: