Monday, January 21, 2008

Vbscript Code Copy / Move Data MS Access To MS Excel

Hi

I have written vbscript code that can copy data from access to excel and also copy data from one excel sheet to other excel sheet (Copy data from sheet 1 to 2) in same workbook. i have created the Ms access database with some data and named as Db.mdb in D:/ (D: drive). i have created Microsoft excel with empty.

I have created one Excel Application object and named it as XLobj.  Open the existing excel workbook from D drive.  Enabled the excel application Visible property to true, to know the data is coped or not.

I have created ADODB Connection and ADODB Recordset objects and named it as objConn , resultSet respectively. i have opened the connection by setting Provider name Microsoft.Jet.OLEDB.4.0 and Data Source to my Ms access. By using Recordset object i have copied data from emp table to resultSet. i have changed the resultSet object to fist location.

By default excel Worksheets sheet-1 is Activate.  I have changed default Worksheets sheet-1 to Worksheets sheet-2 .By using Range i have set excel shell to A1. By using CopyFromRecordset method i have data from Recordset to Excel sheet.  The word Offset indicates the starting position of filed to copy data from access to excel.

I have also copy the data from Worksheets sheet-2 to Worksheets sheet-1 (copy data sheet 1 to sheet 2). First I have taken Worksheets sheet-2 and selected all the data from sheet. Then I have copied the selected data by using XLobj.Selection.Copy methode.

I have selected data in Worksheets sheet-1. i pasted data from Worksheets sheet-2 to Worksheets sheet-1. I have closed the Connection, Recordset objects and Save the workbook.

VB CODE FOR COPY DATA FROM ACCESS TO EXCEL  :

Dim XLobj,objConn,resultSet,TargetRange,xlSheet,xlSheet1

Set XLobj = CreateObject("Excel.Application")
XLobj.Workbooks.Open "D:\db.xls"
XLobj.Visible = True

Set objConn = CreateObject("ADODB.Connection")
set resultSet = CreateObject("ADODB.Recordset")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Db.mdb"
Set resultSet = objConn.Execute("SELECT * FROM emp")
resultSet.MoveFirst

XLobj.Worksheets(2).Activate
Set TargetRange =XLobj.Range("A1")
TargetRange.Offset(1, 1).CopyFromRecordset resultSet

Set xlSheet = XLobj.Worksheets(2)
xlSheet.Select
XLobj.Selection.Copy

Set xlSheet1 = XLobj.Worksheets(1)
xlSheet1.Select
xlSheet1.Paste

resultSet.Close
objConn.Close
Set resultSet = Nothing
Set objConn = Nothing

XLobj.ActiveWorkbook.Save
XLobj.Quit

0 comments:

Google