copy a range from a differnt workbook?Any help welcome

[navyaa]

I am working in a workbook called LifeIBNR and i need to copy a range called REINSPD from another workbook called Life into LifeIBNR.

Is it possible to do it without having to open the other workbook and then copying the range?

Thank you

 

[navyaa]

any suggestions????

 

[Dragon_SDC]

Nothing I can think of. I have managed to set the value of a single cell to that of a single cell of a closed file but that's about it.

Copying of a range is easily done with a macro that opens the file, copies the range and closes it, but then you said you did not want to open and copy, so it's not suitable.

Regards.

P.S. There really is NO need to repeat your question - people can read the first time. Actually, you are shooting yourself in the foot, as the posts with zero replies can be filtered out and shown separately - making it more likely that they will be seen by someone intending to answer other people's questions.
This message was edited by Dragon_SDC on 2002-08-23 16:08

 

[Ivan F Moala]

Yes, there are a number of ways to do this;
Here is one;

You will have to amend the code for your
own situation, but this should get you started;
<PRE><FONT color=blue>Option Explicit</FONT>

<FONT color=blue>Option Base</FONT> 1



<FONT color=blue>Sub </FONT>GetValueFromClosedFile_ViaFormula()

<FONT color=blue>Dim </FONT>sDir <FONT color=blue>As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>ShtCellLoc(3) <FONT color=blue>As</FONT><FONT color=blue> String</FONT>

<FONT color=blue>Dim </FONT>DataRg <FONT color=blue>As</FONT> <FONT color=blue>Range</FONT>

<FONT color=blue>Dim </FONT>Files

<FONT color=blue>Dim </FONT>x <FONT color=blue>As</FONT><FONT color=blue> Double</FONT>

<FONT color=#339966>'/////////////////////////////////////////////////////////////////
</FONT>
<FONT color=#339966>'// Info: /
</FONT>
<FONT color=#339966>'// Files :"P046000.xls" to "P057999.xls". /
</FONT>
<FONT color=#339966>'// /
</FONT>
<FONT color=#339966>'// Cells to extract information from: "C2, I57, I58". /
</FONT>
<FONT color=#339966>'// /
</FONT>
<FONT color=#339966>'// Info to be copied to A2, B2, C2 and so on for 12000 rows, /
</FONT>
<FONT color=#339966>'// keeping the 3 columns common. /
</FONT>
<FONT color=#339966>'// Amend as Required!
</FONT>
<FONT color=#339966>'/////////////////////////////////////////////////////////////////
</FONT>


<FONT color=#339966>'// This is the Dir to search in
</FONT>
sDir = "C:Excelfiles"



<FONT color=#339966>'// This is the Location/cell address
</FONT>
ShtCellLoc(1) = "Sheet1'!$C$2"

ShtCellLoc(2) = "Sheet1'!$I$57"

ShtCellLoc(3) = "Sheet1'!$I$58"



Files = Dir(sDir & "*.xls")



<FONT color=#339966>'// Clear area Column A to place data in
</FONT>
Columns("A:C").Clear



<FONT color=#339966>'speed things up
</FONT>
Application.ScreenUpdating =<FONT color=blue> False</FONT>

Application.Calculation = xlCalculationManual



x = 2

<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> FileError

<FONT color=blue>Do </FONT>While Len(Files) > 0

Cells(x, 1) = "='" & sDir & "[" & Files & "]" & ShtCellLoc(1)

Cells(x, 2) = "='" & sDir & "[" & Files & "]" & ShtCellLoc(2)

Cells(x, 3) = "='" & sDir & "[" & Files & "]" & ShtCellLoc(3)

x = x + 1

Files = Dir()

<FONT color=blue>Loop</FONT>



<FONT color=#339966>'// calculate NOW!!
</FONT>
Application.Calculate



<FONT color=blue>Set </FONT>DataRg = <FONT color=blue>Range</FONT>(<FONT color=blue>Range</FONT>("A2:C2"), <FONT color=blue>Range</FONT>("A2:C2").End(xlDown))

DataRg.Copy

DataRg.PasteSpecial Paste:=xlValues

Columns("A:C").Columns.EntireColumn.AutoFit

Application.CutCopyMode =<FONT color=blue> False</FONT>



<FONT color=blue>Set </FONT>DataRg =<FONT color=blue> Nothing</FONT>



Application.Calculation = xlCalculationAutomatic

Application.Calculate

Application.ScreenUpdating =<FONT color=blue> True</FONT>



MsgBox "Done!...updating complete", vbInformation + vbSystemModal, "Update Status" <FONT color=#339966>'64+4096
</FONT>


<FONT color=blue>Exit Sub</FONT>

FileError:

MsgBox Err.Number & Chr(13) & _

Err.Description & Chr(13) _

, vbCritical + vbMsgBoxHelpButton, _

"File Error", _

Err.HelpFile, _

Err.HelpContext

<FONT color=blue>End Sub</FONT>


</PRE>

 

[navyaa]

THANK YOU FOR YOUR HELP...HAD A QUESTION:

I was wondering where in the code is it that you specify the file where the range is going to be copied from and the file its copied to?For my example the range that needs to be copied is "REINSPD" located in F:ReinsREINDC2002.123"

so where in the code do i specify the file name to copy from and the range to copy and the file and range I am copying to.

Sorry to bother you again but I am new to this and just want to make sure I understand the code clearly.

Thanks a LOT!!

 

[navyaa]

URGENT SUGGESTIONS NEEDED...

THANK YOU.