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

Copying Filename While Merging Workbooks?

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

Linking To Other Workbooks

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

Linking Workbooks ...

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

Linking Data Between 2 Workbooks

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

Linking Lists Across Workbooks

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

Linking To Other Workbooks W/ IF Statements

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

Linking Worksheets In Different Workbooks

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

Linking To Password Protected Workbooks

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

Linking Multiple External Workbooks

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

Linking Cells To Closed Workbooks

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

Linking Names To Closed Workbooks

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

Linking Multiple Workbooks To One Master Workbook

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

Linking Macro Workbook To Other Workbooks That Use Macros

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

Amending The Filename In Cell("filename")

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

Filename As Cell Value

Oct 11, 2009

Is there a way to take filename and place value in a cell

View 9 Replies View Related

Alternatives To CELL (filename)

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

Filename = Cell Contents

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

Change Filename To A Name In A Cell

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

Insert Filename From A Cell?

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

Filename As Cell Contents

Sep 19, 2006

How can I have the filename stored in a certain cell of the worksheet?

View 4 Replies View Related

Saving Filename From Cell Text

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

How To Print Sheet1 Into PDF With Filename Given In Cell A1

Jul 15, 2009

How to print sheet1 into a PDF file with the file-name given in cell A1.

View 9 Replies View Related

Saving Filename From Cell Value In Sheet

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

Cell Function With FileName Changes Sheet Name

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

Insert Filename In A Cell Into Formula In Another Cell

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

Make Formula Pick Up Filename From A Different Cell...

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

Link Filename Of Workbook As Text In A Cell....

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

Needed Function To Display Filename In A Cell

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

Creating A Folder And Saving In It With A Filename Taken From Cell

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







Copyrights 2005-15 www.BigResource.com, All rights reserved