"create" path to a file

**squatter87** · 11-09-2021, 11:17 PM

excel.jpg

hello!
I would to create a path like in the image.
Is difficult to explain because I'm Italian...

cartel.xlsx need to take some data from other file, in the real world I've a lot of file (like apple, pear....) and I have a column with all path.
I need to create this path from the cells with path and not write it manually every time.
I try with INDIRETTO but give me an error #RIF

thanks for your help

**GeoffW283** · 11-09-2021, 11:41 PM

Welcome to the forum

The following worked for me with a reference to one of my workbooks in A4

Formula:

Please Login or Register  to view this content.

Note that the referenced file needs to be open or you will get a #REF! (#RIF!?) error.

Hope this is enough to let you make progress.

**davesexcel** · 11-10-2021, 12:02 AM

All though you can reference cells to closed workbooks.
Using indirect may be too complex for closed workbooks.
It works when opened, but not when closed.
-

indirectFormulaNoWork.jpg

**squatter87** · 11-10-2021, 07:01 AM

Geof, Dave work so good!!
now we can find the way to do it without open the file please?
(I do a Macro that can open-refresh-save-close alla file but for 200 file is slow...)

**DJunqueira** · 11-10-2021, 07:32 AM

A good alternative to access data in closed files is using Power Query (Excel tool), specially if they all are in the same folder and have the same structure.

**squatter87** · 11-10-2021, 07:50 AM

Can you do it please with my 3 attached files? (link A1 in the apple.xlsx in the cartel.xlsx)

**Greg M** · 11-10-2021, 08:21 AM

Hi there,

See if the approach used in the attached workbook gets you moving in the right direction.

Cell E2 uses "native" Excel formulas to create a formula based on the Path, File Name, Sheet Name and Cell Address specified in Cells A2:D2.

The following VBA copies this formula and inserts it as the Formula property into cell F2 - this will retrieve the value from the closed workbook:

Please Login or Register  to view this content.

Spero que questo possa aiutarti

Regards,

Greg M

**squatter87** · 11-10-2021, 01:56 PM

wow!!!!!!! THANKS A LOT

Alessio

**DJunqueira** · 11-10-2021, 02:49 PM

See the instructions for importing with Power Query from folder with many files.

https://www.youtube.com/watch?v=a7E29H5ZUmE

"create" path to a file

LinkBack

Thread Tools

Rate This Thread

Display

"create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Re: "create" path to a file

Thread Information

Users Browsing this Thread

Similar Threads

Can one include the content of a cell in an "Import Data" file path?

How to make commandbutton to open another workbook "with file path change"

create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"

Create a "XLTM" from a "XLSM" file.....HELP!

Set Variable = File Path generates "Object Required" Error

insert>object>create from file. ("link to file" and "display as icon" left .

Macro to create "path" for save

Bookmarks

Bookmarks

Posting Permissions