Import Cell Values From An Unopen XLS File

[vbman]

Hello all. I am trying to copy values from an unopened Excel spreadsheet to
one that I have open. I cannot figure out how to do this. When I use the
following, cells from the open sheet get selected:

Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
Range(Cells(1, 1), Cells(150, 6)).Copy

I am using Excel 2000. Please help. TIA.

Larry

 

[Alan]

Dim Wb1 As Workbook 'Workbook to copy to
Set Wb1 As ActiveWorkbook
Dim Wb2 As Workbook ' Workbook to copy from
Set Wb2 = Workbooks.Open("F:\Larry\TEST1_Backup.xls")

Wb2.Sheets("Field Sheet").Range(Cells(1, 1), Cells(150, 6)).Copy
Destination:= Wb1.Sheets("YourSheet").Range("YourRange")
Wb2.Close False

or

Wb2.Sheets("Field Sheet").Range(Cells(1, 1), Cells(150, 6)).Copy
Wb1.Sheets("YourSheet").Range("YourRange").PasteSpecial xlPasteValues


Regards,

Alan

 

[Ron de Bruin]

Hi vbman

See
http://www.rondebruin.nl/copy7.htm

 

[Tom Ogilvy]

You description is inconsistent. If you want to copy cells from the
currently open workkbook (the active book when you start the macro

set sh = Activesheet
Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
sh.Range(sh.Cells(1, 1), sh.Cells(150, 6)).Copy _
Destination:=Activesheet.range("A1")

if you want to select the cells on Field Sheet of workbook Test1_backup.xls,
it should do that now. however, you can do

Set sh = Activesheet
Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
With xlw.Sheets("Field Sheet")
.Select
.Range(.Cells(1, 1), .Cells(150, 6)).Copy _
Destination:= h.Range("A1")
End With