Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
"Kaby" wrote: 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. 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
I know little VBA...Alt-F11 to get to it right...
"thechilipino" wrote: 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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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).Cel ls(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. "Kaby" wrote: I know little VBA...Alt-F11 to get to it right... "thechilipino" wrote: 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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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. "thechilipino" wrote: 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).Cel ls(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. "Kaby" wrote: I know little VBA...Alt-F11 to get to it right... "thechilipino" wrote: 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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
Hi thechilipino...is there any non-VBA solution to this? I still haven't
figured out how to run that macro succesfully. "Kaby" wrote: 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. "thechilipino" wrote: 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).Cel ls(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. "Kaby" wrote: I know little VBA...Alt-F11 to get to it right... "thechilipino" wrote: 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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
linking workbooks
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. "Kaby" wrote: Hi thechilipino...is there any non-VBA solution to this? I still haven't figured out how to run that macro succesfully. "Kaby" wrote: 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. "thechilipino" wrote: 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).Cel ls(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. "Kaby" wrote: I know little VBA...Alt-F11 to get to it right... "thechilipino" wrote: 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. "Kaby" wrote: 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... "Kaby" wrote: 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... "John Hodgson" wrote: "Kaby" wrote: 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Linking WorkBOOKS across directories | Excel Worksheet Functions | |||
linking to multiple workbooks | Excel Discussion (Misc queries) | |||
Linking WorkBooks Based on Data Entered In One of Them | Excel Discussion (Misc queries) | |||
Linking Workbooks | Excel Worksheet Functions |