Copy Range Values from Closed Workbook from any Worksheet Item

Copy Range Values from Closed Workbook from any Worksheet Item

Hi
A quick spin off from a few Threads and Posts.
Function Arguments:
_1) The Function takes as first argument the Full File Path and Name ( As String ) of a closed File.
_2) The second argument is the Range (Rectangular Area of contiguous cells ( greater than 1) ) to be copied ( As a String in “A1:G32” OR “$A1:$C$4” type format )
_3) The Third argument is the Range ( As Range Object ) of Top left of where the Imported Range Values should start.
_4) The Forth optional argument is the Worksheet Name if known, which if known should be given**_.....
_5) **_...The Fifth optional argument is Worksheet Item number (Consecutive number of Tab counting from the left). If this Fifth argument is given, then this will be used rather than the Worksheet Name. This option should only be taken if necessary as the Function is considerably faster if the Worksheet Name is available. ( The Fifth argument when given takes precedence over the Worksheet name, which if also given will be ignored )

Whilst in the Function a CSE entered Array formula of Links to the Range in the Closed Workbook is entered into the receiving Worksheet. The Function Itself returns an Array() of the Range Values.
So if you want to Paste in Links, then you simply
Call LValsClsdWB( ____ )

The returned Array can then be used if necessary to overwrite the CSE Links. So then you would use the Function thus_...
Let arr() = LValsClsdWB( ____ )
_.... and then overwrite the Range of Links
This is demonstrated in the accompanying test Program.

The Function is simplified, ( no error handling etc..) and spared of too many ‘explanations. Detailed explanations and extended Codes are to be found in the referenced Threads and Posts

I tested the code with this data in the second sheet of a closed worksheet, ( “ProAktuelle01.06.2016.xlsx” )

Using Excel 2007 32 bit

Row\Col	C	D
3	FromC3	FromD3
4	FromC4	FromD4

Tabelle1

_....

Line 150 to 170 in the Test Sub Routine give these results

Row\Col	B	C
2	FromC3	FromD3
3	FromC4	FromD4

Sheet1

_.....

Lines 120 or 130 in the Test Sub Routine give the same values in the Worksheet as above, but in the cells are the following links ( as seen in the Formula Bar )

Row\Col	B	C
2	='C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016\[ProAktuelle01.06.2016.xlsx]Tabelle1'!$C$3:$D$4	='C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016\[ProAktuelle01.06.2016.xlsx]Tabelle1'!$C$3:$D$4
3	='C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016\[ProAktuelle01.06.2016.xlsx]Tabelle1'!$C$3:$D$4	='C:\Users\Elston\Desktop\petra ernährung ab juli\Juni2016\[ProAktuelle01.06.2016.xlsx]Tabelle1'!$C$3:$D$4

Sheet1

_.....................

Function and Test code:

Please Login or Register  to view this content.

' Rem Ref
' Trevor TM Schucks
' Robert Trebor
' Alan Elston ( Doc.AElstein )
' http://www.mrexcel.com/forum/excel-q...ml#post3637224
' http://www.mrexcel.com/forum/excel-q...tml#post216171
' http://www.excelforum.com/excel-prog...ml#post4321000
' http://www.excelforum.com/excel-prog...ml#post4416867
' http://spreadsheetpage.com/index.php...a_closed_file/
' http://www.thecodecage.com/forumz/sh...post1055012583
' http://www.excelforum.com/developmen...ml#post4213824

Hi
I noticed some typos / mistakes in my Function
_A) _ I missed the line that checks If for no given Worksheet name or Index, and Then if so assigns 1 to the Index. _..

Please Login or Register  to view this content.

_..and subsequently adjusted also the check __ ShtIndex > 1 __ to __ ShtIndex >= 1
The code would not have actually eroded, but it would have asked you for the worksheet name. ( The point of the function however is to automate getting the worksheet name required for the string to the closed workbook data range)

What is strange is that the pop up that asks for the worksheet name comes up very quickly and offers you all the Worksheet names. So somehow that info is obtained very quickly. Why I find this strange is that one part of the function is designed to get this information. This part, in particular making the connection, takes relatively much longer.. ??.

_B) _ And I noticed that I do not actually need to use Address Property to get the address string for use in the reference to the closed Workbook. - This is because for a CSE type 2 formula Array entry I can use any notation and either relative or absolute referencing for the address string. – The address string, strCells , is taken into the Function anyway!! – so I can use that directly.
I made the classic mistake of mixing up what is meant by two different things:
_ 1. Notation ( Column letter and row number or Row number and column number )
_2. Relative and absolute referencing
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelforum.com/developme...ml#post4618975
( Note my Function requires Column letter and row number for the given address string taken in, strCells )




Here is the corrected Function

Please Login or Register  to view this content.

@ JackSheet_1

Your message box is full, so I'll say it here:

Hi Doc,

Good to hear from you! Thanks for the rep - but the forum won't let me return the favor.

I'll just owe it to you and won't cheat you out of it.

Until next time,

Orrin