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
