VBA to copy data from one xl workbook to anotherq

[sunilkeswani]

I would like to have a Onclick command on access to copy data from a
specific worksheet in a speicific excel workbook, to another speicific
worksheet in another excel workbook.

Can someone please help with the code? I have been struggling to get
it.

Regards
Sunil

 

[John Nurick]

Hi Sunil,

You'll need to do something along these lines, after setting a reference
to the Microsoft Excel Object library. Note that this is untested and
incomplete air code: you'll need to fix it up to do just what you want:

Dim oXL As Excel.Application
Dim oWKSource As Excel.Workbook
Dim oWKDestination As Excel.Workbook
Dim oBook As Excel.Workbook
Dim strSourceBook As String
Dim strDestinationBook As String
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim raSource As Excel.Range
Dim raDestination As Excel.Range

strSourceBook = "D:\Folder\File.xls"
strSourceSheet = "Sheet1"
strDestinationBook = "blah"
strDestinationSheet = "SheetX"

'Get instance of Excel and open the workbooks
Set oXL = CreateObject("Excel.Application")
Set oWKSource = oXL.Open(strSourceBook)
Set oWKDestination = oXL.Open(strDestination)

'Define the range to copy and the location to paste to
'Modify this as required!
raSource = oWKSource.Worksheets(strSourceSheet).Range("A1:AA99")
'Normally just specify the top left cell of the destination
raDestination = oWKDestination(strDestinationSheet).Range("A1")

'Copy
raSource.Copy raDestination

'Finish
'Save workbook we modified
strDestinationBook.Save
'Close all workbooks
For Each oBook in oXL.Workbooks
oBook.Close False
Next
oXL.Quit