linking workbooks

K

Kaby

How do I tell excel to find a cell value from a folder of files by giving it
part of the filename and link that value to a master workbook?
 
J

John Hodgson

Kaby said:
How do I tell excel to find a cell value from a folder of files by giving it
part of the filename and link that value to a master workbook?

HI, i'm not to sure what you are asking, if you wish to display a value in one cell that is on another worksheet or work book you just need to type = in the formula box and then navigate to the cell you wish to link, then press enter.
Click to expand...

If you want to link to a file or folder you need to use a hyperlink. The
important thing to remember with this is that if you are emailing the file
this link will be broken if the recipient does not have the file saved
locally in the same place, of if it is not shared on a network.
 
K

Kaby

Thanks for the hint John...I will look to see if I can figure this hyperlink
function out.

To give you more on what I am trying to do, I have workbooks that are being
created from time to time depending on the actual product. In other words
each product will have its own workbook with its own name. What I want to be
able to do is extract certain data from those workbooks that are being
created into a master workbook. In the master workbook, I plan to have a
column that has part or all of the file name. What I want excel to do is
look through those files, which will be stored in a specific folder, and
choose and open the corresponding workbook and retrive cell values into the
master workbook.

I hopw this makes sense...
 
K

Kaby

Two things I should add is that I don't want to go to (or open) the source
file, which is what hyperlink seems to do; I just want the value to show up
in the destination file. Further, the thing that is throwing me off is that
I don't know the file names before hand. I am trying to avoid the product
managers from entering same information in two places.

Somehow, I feel like I am not making sense...
 
T

thechilipino

hello kaby:

i think i understand your problem. do you know any VBA, only a little
would be necessary? there is a solution, but it would require a some VBA.

reply to this thread and i can give you code examples for how to perform this.

cheers. chili.
 
T

thechilipino

Hello Kaby:

I'm not sure this is going to make sense, but here is a high-level
framework of how one might tackle the problem:

********************************
Sub ProcessFiles()
Dim sFile$
Dim master_workbook, sheet_name, Path_Name As String
Dim r as integer

'initialise variables
master_workbook = ActiveWorkbook.Name
sheet_name = "Master" 'where the data will be written
r = 1

'if you make a list of the paths to your specific folders, cycle through
'them using path_name variable using Do Until Cells(row, col)<>""

sFile = Dir(Path_Name & "*.xls") 'assumes source files will be *.xls file
types
Do While sFile <> ""

Workbooks.Open (Path_Name & sFile)
Worksbooks(master_workbook).Sheets(sheet_name).Cells(r,col).value =
Cells(row,col).value
'before the equal sign is where you will be writing the data
'after the equal is where on the source file you are pulling data

r = r+1 'increments row in master file

Loop

End Sub
*******************************************

There are many locally defined items which make it difficult to have
one-size-fits-all code.

Please let me know if this is useful, or if you have any further questions.

Cheers. chili.
 
K

Kaby

Hi thechilipino...thanks for the info. I've copied and pasted what you've
sent me into the VBA and now need to figure out how to tweek it so that the
right inoformation goes in the right place.
 
K

Kaby

Hi thechilipino...is there any non-VBA solution to this? I still haven't
figured out how to run that macro succesfully.
 
T

thechilipino

Hi Kaby:

Eh, one kludgy possibility is that if all of the information you are
linking to are in the same places in a workbook, you can setup links in your
master workbook to an initial file. You ensure that the links are referenced
($ signs on both row & column), and just copy those cells to a different
area on your master. Then find & replace the link to the new workbook.

So say, that in your master file, in cell A2 you are linking to

server1/folder2/[template1.xls]Sheet1!$A$2

if one of your product managers saves his template into

server2/folder5/[template2.xls]

you can copy the formula in cell A2 to A3 and just use the find & replace
(ctrl-H) to replace the "server1/folder2/[template1]" with
"server2/folder5/[template2]".

not elegant, but will work.

Cheers.
 
Top