Link With Variable File Name

May 18, 2007

I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:

File#, Date, Status, Customer, Etc.

This is accomplished by copying a row of data in the active workbook and pasting as a link on the master. When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again. The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.

What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.

Thus, where the pasted link formula reads:
='C:Active Files[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:Active Files["&$A25&".xls]Home'!$O$1"

View 3 Replies


Link Cells From One File To Another And Insert A New Line On The File

Apr 24, 2009

when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:


How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.

View 3 Replies View Related

Using Variable In A Link?

Mar 26, 2014

I have a couple of workbooks, workbook1 pulls information from closed workbook2. The current link works fine, it returns the value that is in cell E10 from the worksheet 'Totals' from within the workbook 'Week 12 Yellow.xlsm'.


What I would like to do is make two parts of this link variable; Name (so 'Marc owens' in the above example) Worksheet (so 'Week 12 Yellow.xlsm in the above example)

As stated, the worksheet in the formula will be closed so I cannot use the 'Indirect' option. I have come across a lot of talk about the 'Concatenate' option but this this isn't working, don't know if its the way I have the formula or if I need something extra adding.

Cell A1 = name variable & Cell A2 = worksheet variable

[Code] ......

But this just returns "G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals!%E%10" in the cell the formula is in.

I've changed it so that it is

[Code] ......

But again I get "='G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals'!%E%10" returned which is the same formula as originally used (top of page) but it doesn't return a value.

View 9 Replies View Related

Link To External File With Changing File Name

Jun 12, 2007

Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...

View 9 Replies View Related

VBA Code For Opening File With Variable Version Number In File Name?

Jan 29, 2014

I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?

View 6 Replies View Related

Link To External CSV File?

May 16, 2013

My raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?

View 1 Replies View Related

Link A CommandButton To Another File

Jul 25, 2008

linking a CommandButton to another File. I made a CommandButton but would now like to know how to write the Macro to make that Button, when clicked, bring me to a file saved in Excel.

View 9 Replies View Related

Creating A Link To The .pdf File

Jan 29, 2009

I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.

The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.

Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?

123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.

Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20

x = 0

For i = StartRow To EndRow

Cells(i, 4).Select

MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)

If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub

View 9 Replies View Related

To Link File In Word Via Vba

Feb 26, 2007

What is the script in order for me to link a MS Word file to excel. What I intend to do is to customise the commandbutton, so that once a user click on it, the linked MS Word file (stored in the local drive) will pop-up and he/she can do the necessary amendments to the file?

View 3 Replies View Related

Sorting By Variable File Instead Of Specific Named File?

Jul 18, 2014

I recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.

I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?

View 4 Replies View Related

How To Automatically Change The Link To A New File

Dec 21, 2012

Basically, at month end, my company will create a new folder with the name as YYYY_MM (e.g. 2012_11). In this folder, a new file will be created as: earnings_YYYY_MM (e.g. earnings_2012_11).

What I want is to automatically update the link to the most recent file: earnings_YYYY_MM, whenever it is available. For example, next month there will be a new file earnings_2012_12 created in the new folder 2012_12, then the new file will be linked with.

View 1 Replies View Related

Paste Link Data From CSV File

Feb 22, 2013

I have a "cost.csv" file that contains the cost of the toys. The cost is show in $ with two decimal places i.e. $0.75

I created a new MS Excel 2010 workbook file named as "customers.xlsx" that has four columns i.e. Item, Cost, Order, [Total Amount] columns. I used the paste link command and the Cost column data is linked with the cost column in "cost.csv" file.

When I write order quantity in the order column and then try to use the following expression in [Total Amount] column i.e. "Cost * Order" then I received error due to following reason i.e. "Cost is a text value and Order is a numeric value and excel is unable to evaluate the expression." I have done it by first remove the $ sign from cost using RIGHT and LEN FUNCTIONS.

Now I want to change the format of "Cost" column i.e. I want to show the cost in Euro but not in $. Here I am stuck as I am trying to change the format of the Cost linked cell but unable to change it either I applied the new format, the text remains shown $0.75?

Is this the problem as data is coming from .csv file? If it is then how I can accomplish the task?

View 2 Replies View Related

Update Link With File Name In Cell

Jul 14, 2007

I'm sure this is an easy one but it's unfortunately beyond me. I have a update links formula in which I would like for it to update with the name of the file being in a cell. Example:

The file name is Week20.xls

Cell A1 has the value of Week20.xls using a formula (="Week" & A2 & ".xls")

Currently I have it by looking at the actual file location like this: ='C:Documents and SettingsMy DocumentsWeekly Sales6 Sales Comparisons[Week20.xls]Daily Comparisons'!$I$4

But rather then pointing directly to the file path I want it to update by the name of the file in A1. So I'm sure its something as simple as replacing [Week20.xls] with some formula but I don't know what that one is.

View 3 Replies View Related

Data Validation Criteria Link With Other File?

Jun 27, 2013

I have attached 2 files to explain what I really need:

In file A.xlsx I have 2 sheets. In the B1 cell from Sheet 1 I have created a data validation criteria based on the sheet 2.

I would like to get that same data validation criteria based on the A.xlsx onto the B.xlsx.

Is it possible? If not do you have any trick, solution, workaround to link a list from one excel file to another?

View 4 Replies View Related

Must Open External File To Link Formula

Oct 26, 2009

We're using SUMIF and it won't work unless the external excel file is open.

This is the formula we're using:

View 7 Replies View Related

Take Contents Of Cell To Generate File Name For Link?

Jan 9, 2012

I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)

I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:

=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))

Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.

What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.

View 3 Replies View Related

Link Error When File Saved In New Location

May 19, 2014

I have a template excel file that has links to data in another excel file in the same directory as the template. However, I save the template with a new name in a new folder and the link no longer works. The link changes to the location with the new file and of course the file I am trying to like to is not in that location.

The link shows the full path in the template file so I would think it would keep the full path when saving to a new location but the file doesn't.

How do I make the link address static?

View 6 Replies View Related

Update Link Without Open Resource File

May 31, 2006

I'm using Excel 2002.

I have one workbook with data linked to another CSV file (It's about 40000rows). When I open the workbook, "THis workbook contains one or more links that cannot be updated." message appears and asks me to open csv file if I wanna to update (although I set full path for links in cells). I wonder if there's any way to update link without opening csv file? Or Excel can not update link without openning the resource file?

View 3 Replies View Related

Replace Multiple File Link Locations

Nov 16, 2006

I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))


=IF(WEEKDAY(F2)=1,E10,IF(WEEKDAY(F2)=7,E10,'J:DailySeptember 2006[Cash Flows -Sep06.xls]1st'!$I$40*-1))

As you can see the month is shown in 3 ways...

September 2006
01Sep2006.xls and

I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............

View 4 Replies View Related

Link Formula To File Based On Cell Value

Jan 22, 2008

I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files.

My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.

View 2 Replies View Related

How To Link A Cell To Specific Image File To Other Folder

Jun 26, 2014

There is a problem in joining one cell to the other file

The below is screen shot when l click the on every cell the image must be open

How can do it?


View 1 Replies View Related

Insert Link To Open File Saved On Computer

Jan 23, 2009

I am trying to insert a link that once clicked will open a file (word, excel, powerpoint, etc. ) that has been saved on my computer.

View 3 Replies View Related

Create File List But Only Show Filename As Link

Nov 16, 2006

This link worked perfect for my needs: Create a List of Hyperlinked Files:
But is there any easy way to only show the filename insted of the full path for the name of the hyperlink? Now it shows it like :C:MyDocumentsTestings est1.xls
I only want it to show as: test1. I still want the hyperlink to work

View 2 Replies View Related

Prompt For File On External Formula Link Entry

Feb 1, 2008

I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell

However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.

View 2 Replies View Related

Automatic Link Update WITHOUT Prompt When File Opened Not Working (XL2003)

Jul 6, 2006

Despite setting the "Edit/Links/Startup Prompt/Don't display the alert and update links" option numerous times, my workbook still prompts me to update links every time I open it. The option seems to be set okay (it is preset whenever I go into the "Edit/Links/Startup Prompt" dialog), but it doesn't seem to affect the workbook's startup behaviour. The workbook contains a ComboBox control that is initialized with customer names from another workbook, which is included in the References for this main workbook.

I am using Excel 2003 (from Office Pro 2003) under Windows XP (SP1). I believe this used to work without the prompt when I was working on this app last fall (I'm not sure, as my memory of specific behaviours back that far is fuzzy). However, it has been persistently prompting ever since I started working on this app again this spring/summer. Was there perhaps a bad fix to Office 2003 (that I automatically applied) that broke this feature?

View 5 Replies View Related

Excel 2010 :: File Sharing - Link 4 Spreadsheets Where Information Will Be Updated By One Person

Apr 21, 2013

I have a Excel Spread Sheet which has been created, there will be 4 people in 4 different locations who will have access to this spreadsheet and will need to update/amend it through "Dropbox", my question/problem is, how can I link the 4 spreadsheets where information will be updated by one person, but will update all 4 sheets at the same time.

Is this possible and how do I go about it?

View 4 Replies View Related

Excel VBA - Right Click File Browser To Create Link To Locally Stored JPGs

Oct 9, 2013

I've been playing about with this for ages, I have row 'U' which I manually link to local JPG files. Basically it has the word "HERE" and linked to a JPG that opens when you click it.

I'm really looking to just RIGHT CLICK a cell in column U, it opens a file browser, when I select the file it places HERE in the cell and links it to the file I've selected.

View 5 Replies View Related

Place Excel File Path As Link In Lotus Notes Email Body

Oct 29, 2013

I have an Excel sheet that I use as a mailing automatism for reports. As it currently is it attaches an actual copy of the excel workbook to the email and send them out. The mailer contains several different people, and they get different report each day. Due to the size of some of the files, I am starting to run into an issue where I cannot sent the emails anymore because they are too big, so I am wanting to switch to sending links to the files instead, and I have hit a wall.

I use Lotus Notes 8.5. The VBA will cycle through a range, and each cell has a list of report delimited by a ",". It takes the list and passes it to the mailer as a string. The mailer takes the string, turns it into an array and splits it out, and then checks to make sure the reports are current. One email could have up to 10 different reports in it. I have tried creating an HTML MIME email to include the links.

Here is the code I currently have:

Sub Send_HTML_Email(ByRef Name As String, ByRef Address As String, ByRef Reports As String)
Const ENC_IDENTITY_8BIT = 1729
'Send Lotus Notes email containing links to files on local computer
Dim NSession As Object 'NotesSession
Dim NDatabase As Object 'NotesDatabase
Dim NStream As Object 'NotesStream
Dim NDoc As Object 'NotesDocument


View 1 Replies View Related

Variable For File Name Or Path Name?

Jun 25, 2014

How could i get the below code to work with a variable as the file name?

Or could I use something like "thisfile" to determine where to import to.

[Code] .....

View 5 Replies View Related

Pass A File Name As A Variable

Aug 8, 2008

I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.

This code works:

View 9 Replies View Related

Copyrights 2005-15, All rights reserved