Monday, January 28, 2008

How to copy data from (database,Txt file.....) to specified sheet(say sheet 1|sheet2|sheet3|sheet4) in the Excel workbook

Dim xlApp
Dim xlBook
Dim xlSheet
Dim conn,rs,sql,strConnect
Dim objWorkbook
Dim colSheets
Dim ws
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
xlApp.SheetsInNewWorkbook=8
Set wbs=xlApp.Workbooks
'Set objWorkbook = xlApp.Workbooks.Add
wbs.add
Set objWorkbook=xlApp.Worksheets
Set ws=objWorkbook.Item(4)
ws.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


xlApp.quit

No comments: