Linking To Workbooks Whose Filename Is In A Cell
May 9, 2006
One folder on the drive contains excel files, which are created daily from a template. I use these files, the 20 most recent days' worth, to create an analysis file. This new file is organized in rows, starting with the date. The files I am using are all saved in the form "Stats yyyy mm-dd.xls" The first line of each row of the analysis spreadsheet starts with the date, in the format "yyyy mm-dd". Is it possible to create a macro, or in another way, for the analysis spreadsheet to automate the reading-in of data from these different files?
View 2 Replies
ADVERTISEMENT
Jan 16, 2014
I have a few hundred workbooks that I am merging into a single file. I found the following vba code that performed this task very well.
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object[code]....
After I merged the data, I realized that I needed to know the source file that each row of information comes from.
How can I modify the above script to copy the filename and add it as an additional column of information when each sheet is copied into the master file?
For Example:
Currently If I have 3 files:
File 1:
1 2 3
4 5 6
File 2:
a b c
d e f
File 3:
v w x
y a b
Running what I currently have will give me:
1 2 3
4 5 6
a b c
d e f
v w x
y a b
View 1 Replies
View Related
Jun 20, 2014
It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
View 1 Replies
View Related
Apr 4, 2007
I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,
Period 11 will contain the same workbooks but named workbookA11wk1 etc.
The data from the workbooks is then used in graphs / tables for each year.
I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.
Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1
View 9 Replies
View Related
Jul 16, 2014
You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
View 4 Replies
View Related
Jan 20, 2009
I am trying to create a master price list, where the 1st file called MasterPriceList will list all of our ingredients and their prices.
It would look like this:
A_________B
ING______Price
1 Ing 1___$1.00
2 Ing 2___$1.50
3 Ing 3___$2.00
4 Ing 4___$3.00
My second workbook is is a template for when we need to formulate blends.
It looks something like this:
A______B______C_______D__
1 Ing 1__50%__$1.00__$0.50
2 Ing 3__50%__$2.00__$1.00
We want to pull the cost of the ingredient from the MasterPriceList and populate column C with that value.
I have tried copying and pasting a link. This works fine until I insert a row. Even If I make the link relative (='[MasterPriceList.xls]Sheet1'!$C1), it will only adjust if both worksheets are open at the same time. Since we will have over 200 pricing sheets, it would be impractical to have them all open every time we have to insert a new ingredient into the MasterPriceList workbook.
I have tried to use VLOOKUP but it will not work across workbooks and the same is true with Drop Down lists. I read a tutorial, http://office.microsoft.com/en-us/ex...995141033.aspx , which details how to create a drop down list across workbooks but when I come to the step of defining the validation list, I get a message saying that it can't be done across workbooks.
View 4 Replies
View Related
Aug 8, 2008
I have created a link in workbook "A" to another workbook "B" by copying the cell in workbook "B" and using "Paste Special" and "Paste Link" in workbook "A". I did this for 1,000 rows so that when I update the workbook "A", it will take in any new data.
However, if a cell does not have a value in it, the cell in workbook "A" returns a "0".
To avoid this, I figured I could use a statement like =IF('C:[B.xls]tabname'!A45="","",'C:[B.xls]tabname'!A45).
It worked in the case of worksheets, but in others, it only returns "#########". If I hit "F9" it doesn't refresh the data to return the value, so I assume something silly is going on here.
View 9 Replies
View Related
Jul 7, 2006
I have three workbooks stored in the same drive(shared drive). Let's call them 1.xls, 2.xls and 3.xls. Each workbook has one sheet.Column A in all sheets is the same. I want to make the following linking:
-Column A of the sheets in 2.xls and 3.xls to be linked and get data from column A of the sheet in 1.xls.
-Column B of 1.xls to be linked and get data from column B of 2.xls and column C of 1.xls to be linked to column B of 3.xls.
So far it sounds easy. What I cannot find is what to do when I insert a row in the sheet in 1.xls and write something in column A. I managed from DATA- IMPORT EXTERNAL DATA to refresh column A of the sheets in 2.xls and 3.xls, so that these columns contain the updated information. I cannot do the vice-versa procedure: for example, to insert something in column B of the sheet in 2.xls and refresh the values of column B of the sheet in 1.xls. The fact is that when I insert a row in 1.xls the right references get lost and move one cell up. I want the references to remain stable. In a way the sheets are interdependent.
View 2 Replies
View Related
Jan 24, 2012
I have all of the passwords. I just want to avoid having to type in all 30+ of them each time I open the master sheet.
I have a master for each manager that pulls a few values from each of their employees personal sheets.
The personal sheets (about 30) are individually password protected so only the owners can open them.
This is creating a security issues as the links in the master require the passwords for each of the "source" workbooks.
Is there any way to add some code so that the passwords do not need to be entered each time?
View 1 Replies
View Related
Apr 12, 2007
I have a workbook called Pricing.xls that contains *multiple* external links. The Pricing WB summarized monthly data, the trick is that each month is in a different WB (*DEC06.xls), and the big problem is that there are dozens WBs a month.
I am using vlookups to get the data from external WBs.
The way that it is being done currently is; the formula is copied across month to month, and them manually editing the link in the formula bar. This seems to half work, but causes Excel grief. Is there a way to link to multiple work books without manually linking them each time?
View 9 Replies
View Related
Aug 9, 2006
I have multiple workbook files using the same template but saved under different files names usually by date. I need to summarize the data in a summary worksheet that pulls the same cells from the various individual closed workbooks. I need to be able to insert the file's path as an input to pulling data from a cell or cells.
View 8 Replies
View Related
Feb 14, 2007
I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.
View 9 Replies
View Related
Dec 27, 2012
At our small firm, each staff member keeps a small excel spread sheet on the network that contains their "work in progress." This spreadsheet is roughly six columns wide and roughly twenty lines long.
What I am attempting to do is have one "master" spreadsheet with a tab for each staff member that links their "work in progress" so that partners and managers can easily see how much work each of the fifteen or so staff members have.
I can create fifteen different tabs and fifteen different files on the network and link =[Staff1.xlsx]Sheet1!$A$1:$G$25 =[Staff2.xlsx]Sheet1!$A$1:$G$25 =[Staff3.xlsx]Sheet1!$A$1:$G$25 and so on.
If I make a change to the layout of the work in progress sheet, I have to delete and copy the file fifteen times, then go back through and re-link fifteen tabs to fifteen workbooks in order to keep it uniform.
View 1 Replies
View Related
Jan 10, 2014
I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.
I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:
Application.Run "CodeWb.xlsm!DataFileOpen"
So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.
How can I use the code in CodeWb to handle all events occurring in DataWb?
View 2 Replies
View Related
Jul 7, 2006
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies
View Related
Oct 11, 2009
Is there a way to take filename and place value in a cell
View 9 Replies
View Related
Apr 7, 2009
CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.
What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?
View 2 Replies
View Related
Dec 11, 2008
I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.
View 2 Replies
View Related
Jan 3, 2010
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet. DETAILS: The cell with the new file name is always D8. The cell is blank initially (although it doesn't have to be). OTHER INFO: OS= WIN 7 Excel= 2007
EXAMPLE: If I input "Mike T" into cell D8, the name of the file will be Mike T.xls
View 5 Replies
View Related
Dec 10, 2013
Is it possible to insert the filename of another excel file based on the information in a cell within your current file?
I have a file named 12-07.xlsx, which has a sheet Data 12-07, and that sheet has information all through it. I want to populate a cell in a new file with inform from the 12-07.xlsx cell B3.
I know the Formula is ='[12-07.xlsx]Data 12-07'!$B$3
Is there a way that I can use a cell within the current file, say cell B1 (which has a value of 12-07 - which is the same as the file I want) to populate the filename? Something like
='["=B1".xlxs]Data 12-07'!$B$3 (whereas "=B1" would be 12-07)
I know that will not work as is, but is this concept doable?
View 3 Replies
View Related
Sep 19, 2006
How can I have the filename stored in a certain cell of the worksheet?
View 4 Replies
View Related
Nov 18, 2009
When the user goes to File, Save As, I would like the filename to be populated from the contents of cells B2 and B3, i.e. 11/18/09-23. Is this possible? Also in the same code, can I specify that I want the destination folder to be K:Sheets?
View 11 Replies
View Related
Jul 15, 2009
How to print sheet1 into a PDF file with the file-name given in cell A1.
View 9 Replies
View Related
Dec 2, 2011
I would like code to automatically save my spreadsheet with the name that is present in cell F4 of the activesheet. For example, I would like to save my file to desktop and cell F4 contains the text 'hitec 348'
View 5 Replies
View Related
Dec 2, 2007
Excell 2003
I put = cell("filename") in cell A1 on every sheet to display the full pathname of that sheet.
it seems that the path does not always display the correct sheet name- rather it seems
that when changing from one sheet to another the path is not always updated.
Is there a switch that needs to be activated to ensure that the path is updated correctly when changing sheets on the same worksheet?
View 3 Replies
View Related
Feb 3, 2014
I have tried =INDIRECT but that will not work.
I have a file name typed into cell C3. I want the formula in cell C4 (C5,C6,C7......) to include the name of the file typed into cell C3. The formula in cell C5 shows what the content should look like.
I want the users to be able to type the file name in Row 3 (C3,D3,E3.......) and have the formulas below them insert the file name and access the information from the files (yes the files can/will be open when we do this).
I need to to have at least twenty different formulas (rows) referencing specific cells in these files.
The formulas would look like this ='["File name from C3"]Audit Information'!$D$35.
I am hoping it may only be a syntax thing and someone here can "show me the way" I have tried &$C$3& with and without quotations with no luck.
Here is the test file. Test3.xlsm‎
View 5 Replies
View Related
Feb 9, 2010
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32
There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.
View 2 Replies
View Related
Jan 13, 2005
I have a workbook that gets saved many times with many different filenames.
All I want to do is have cell A1 display as text the name of the file.
eg similar to the date function, there should be a = filename function .
View 9 Replies
View Related
Aug 1, 2008
I am running excel 2003 and I am looking for a function that will display the filename or part of the file name in a cell. I know you can do this as part of a header or footer but is there anyway to have an individual cell display the file name of the document?
View 9 Replies
View Related
Mar 21, 2009
I would like excel to create the folder C:/Bill if it doesn't already exist when I click save/save as option and save the workbook in it with a filename that is a combination of text/values in cells A1 and A2 on Sheet1. Better if the save as dialog box appears with this option selected so that I have to just click Save in the save as dialog box to save it in "C:/Bill".
View 9 Replies
View Related
