Using PULL function

[vbacoder]

Hi everyone,

I'd be grateful if someone could show me an example of Harlan Grove's PULL function works for a path reference that has been formed from components in several cells:

A1 = " 'C:/work/"
B1 = "data
C1 = "[ClosedBook.xls]Sheet1'!$A1"

D1 = A1 & B1 & C1

I have tried E1=PULL(D1), to obtain the value in the cell referenced in D1, but it doesn't work, and the way the argument is passed to the function is not very clear from the instructions on the page where the code is posted:

http://groups.google.co.uk/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd

Many thanks.

vcoder

 

[Dave Patton]

Is your string concatenation similar to the example?
Highlight the formula and compare it to the example.

Possibly

A1 =C:\work\
B1 = data\
C1 = [ClosedBook.xls]Sheet1'!$A1

Cell with formula ="'"&A1&B1&C1

 

[ZVI]

There was lost backslash in B1: “data\“

But using of events suggested earlier is more efficiently because the time & memory consumptions for creating of new Excel instance are not required.
Learn more about events on CPearson site

After solving of issue please mark it in duplicate threat to save the other helper’s time.

Regards,

 

[vbacoder]

Thanks Dave and Vladimir for your help.

Just in case anyone else stumbles upon this thread, I thought it may be helpful to close it off with some working examples. The pull function can be applied in the example below as follows:

A1 =C:\work\
B1 = data\
C1 = [ClosedBook.xls]Sheet1'!$A1

D1 = "'" & A1 & B1 & C1

E1 = PULL("'"&A1&B1&C1)

OR

E1 = PULL(D1)

The way the split is applied does not seem to matter, so A1 could hold just C:\ and B1 the remainder of the filepath + filename and these cells could then be concatenated within the pull function to yield the value in the external, closed, worksheet.

It works just great and many thanks to Harlan Grove for the code in the PULL function.

vcoder

 

[vbacoder]

Just wanted to add that I am using a number of PULL functions on my worksheet (close to 100) and it's taking a long time to recalculate the cells after opening the sheet.

I also notice that when I perform certain modifications to the workbook (like removing a worksheet) this kicks off another cycle of recalculations for the PULL functions. It takes more than 5 mins to recalc the full set of statements.

I wonder if there is a way to embed the PULL statements in a field array expression, which may display the results faster. It seems that having several individual PULL statements invokes the function in a serial fashion, whereas embedding in an array - if possible - will run recalcs in batch.

Has anyone tried this?

Many thanks,

vcoder