Necessary to open up one workbook in order to copy information to another workbook?

Hi all,

Just curious if it's at all necessary to physically open a spreadsheet in order to copy information to another workbook? Could one not possibly just list the range and file path you want copied?

Currently my code is having to open a file, select contents and then paste into another workbook on a specific tab and then close that file. I'd preferably not want the user to see the file physically opening in the toolbar and then closing if even just for the slightest fraction of a second.

Workbooks.Open Filename:="\\server\shared\Project Zeus\Report X.xls"
    Range("A1:X65536").Copy
    Workbooks("Reporting model - GM").Sheets(5).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
 Windows("Report X.xls").Close

No, it needs to be opened. Hide the action by switching off ScreenUpdating

Application.ScreenUpdating=False
'your code to copy
Application.ScrenUpdating=True

You can use ADO

That's why I didn't suggest it at first

And that is why I come up with this one:

With Getobject("\\server\shared\Project Zeus\Report X.xls")
    Workbooks("Reporting model - GM").Sheets(5).Range("A1:X65536")=.sheets(1).Range("A1:X65536").Value
    .Close True
End With

Oh wow i didn't think it was possible with so little code!

I've tried it out but got a debug error, should probably have put in the exact ranges i'm working with. I've most likely applied the incorrect ranges as i'm not 100% confident i understand what the code is trying to do, any help?

My actual code:

Workbooks.Open Filename:="\\server\shared\Project Zeus\Report X.xls"
    Range("A9:X65536").Copy
    Workbooks("Reporting model - GM").Sheets(5).Cells(7, 3).PasteSpecial Paste:=xlPasteValues
 Windows("Report X.xls").Close

Applied snb code:

 With GetObject("\\server\shared\Project Zeus\Report X.xls")
    Workbooks("Reporting model - GM").Sheets(5).Range("A7:C7") = .Sheets(1).Range("A9:X65536").Values
    .Close True
    End With

As Cutter already said: we try to accommodate your wishes as painstakingly as possible.
The reason you started this post is to prevent users seeing what is happening in the background. Then we should apply code that does exactly that sort of thing. If we want the code to be as fast as possible, we should preferably avoid the 'copy' method. Instead we write directly from one workbook to the other (avoiding the clipboard).
To do that we need 2 identically sized ranges. And also I do not believe that you need all rows from A9:X9 downwards to be copied.

With GetObject("\\server\shared\Project Zeus\Report X.xls")
    Workbooks("Reporting model - GM").Sheets(5).Range("C7:Z1998") = .Sheets(1).Range("A9:X2000").Value
    .Close True
End With

to answer the other question, rightly said i don't need to copy all the way to last row 65536. I did this as the row length varies on a monthly basis but i'll look into a fix for that if i can crack this query.

to add:

I've tried only copying ranges A1:C7 and pasting into sheet 5 A1:C7 just as a test but once again no luck. I'll keep cracking away at it..

A variable approach:

With GetObject("\\server\shared\Project Zeus\Report X.xls")
  With .sheets(1).cells(9,1).currentregion
     Workbooks("Reporting model - GM").Sheets(5).cells(7,3).resize(.rows.count,.columns.count) = .Value
  End With
  .Close True
End With

NB. Do not use values, but Value

Values was my fault (in my posts). you were too obedient copying it. . I made a correction in the posts.