copy from one spreadsheet and paste to another

copy from one spreadsheet to another

I download a report from Oracle,  data from "sheet 1" needs to be copied (Range A1: Y5000) without opening the file and needs to be pasted in 2nd sheet of destination file

Source File can be in any of the excel formats ( XLSX, xls, XLSM)

Destination file is excel 2007 ( paste in the same format of source file)

Please help

Thank you
NirvanalearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
"without opening the file" ?  Hmmm...  David Copperfield could do it. Maybe.

Even when you use some application for this purpose the application must open both spreadsheet files.

You may use Excel Automation or write a short VBA code to achieve this requirement.

The VBA code would look like this one:
Sub CopySheet()
Dim x As Workbook
Dim y As Workbook

' Open both workbooks
Set src = Workbooks.Open("<path to source XLS>")
Set dst = Workbooks.Open("<path to destination XLS>")

' Copy range of cells to clipboard
src.Sheets("<name of the 1st sheet>").Range("A1:Y5000").Copy

' Paste to destination worksheet
dst.Sheets("<sheetname>").Range("A1:Y5000").PasteSpecial

' Close source
src.Close

' Save and close destination
dst.Save
dst.Close

End Sub

Open in new window

Of course this is just an idea without any functionality testing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanalearnerAuthor Commented:
Thanks a lot works perfect I have just changed the variable names to src and dst.

if I can ask one change, when pasting can it paste current active workbook/sheet
0
ShumsDistinguished Expert - 2017Commented:
Hi Nirvana,

Please do the necessary changes (path, filename & sheet names) and paste below code in your destination workbook and run macro UpdateDestFile:
Sub UpdateDestFile()
Dim DestWB As Workbook, SrcWB As Workbook
Dim DestWs As Worksheet, SrcWs As Worksheet
Dim SrcLR As Long, DestLR As Long
Dim MyPath As String, MyExtn As String, SrcFName As String

'Define Variables
Set DestWB = ThisWorkbook
Set DestWs = DestWB.Worksheets("Sheet2") 'Change Sheet Name here
DestLR = DestWs.Range("A" & Rows.Count).End(xlUp).Row
MyPath = "C:\Test\" 'Change your path here
MyExtn = "Sample.xl*" 'Change your file name here
ChDir MyPath
SrcFName = Dir(MyPath & MyExtn)
Set SrcWB = Workbooks.Open(SrcFName)
Set SrcWs = SrcWB.Sheets("Sheet1") 'Change Sheet Name here
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Uploading Data !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Clear Old Data
DestWs.Range("A1:Y" & DestLR).Clear

'Copy Data
SrcWs.Range("A1:Y" & SrcLR).Copy
DestWs.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False

'Close Source Workbook
Application.DisplayAlerts = False
SrcWB.Close SaveChanges:=False
Application.DisplayAlerts = True
DestWs.Columns.AutoFit

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With

End Sub

Open in new window

0
pcelbaCommented:
Ah yes, Dim x and Dim y was my fault...

To Copy active sheet you could use
x.ActiveSheet.Range("A1:Y5000").Copy

and paste to active sheet should work the same way.

You have plenty of methods and properties available in Excel. Look e.g. here: https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/workbook-object-excel
0
NirvanalearnerAuthor Commented:
Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.