I want to move the workbook I have created to a different folder on the same drive. The workbook is pretty big and has loads of links to other workbooks. If I move it will all of these links fail and if so will I have to manually reset them all? I guess I am asking if there is specific method of moving workbooks which will automatically change the links to the cope with the new location?
View 3 RepliesI am new in VBA, but I have a short procedure to break all external links in the currently active workbook.Is there any option to firstly update all external links and only then break them?
Sub BreakLinks()
Dim Links As Variant
Dim i As Integer
With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
[Code]....
I'im using an index - match - match formula with links to an external file.
Everything works fine until I close the source workbook. At that moment the liks break. The error I'm getting is #REF!.
I suspect the reason is that the source data are in form of an official table. I tried some formulas with data in the same workbook but outside the table and they keep working after the source file is closed.
I really want to keep the source data in a form of an official excel table.
I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:
D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls
Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.
Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....
I have been trying to save myself alot of time by using the name manager and an external link to another workbook without success.
The real directory name is very long. There are 200+ different markets like forex1
and 25 different "Locations" that refer to the same things
Name Manager name Value
-----------------------------------------------------------------------
Directory ="C:DemoVP["
Forex1 ="Australian Dollar Japanese Yen Cash.xls"
Location1 ="]Sheet1'!A2"
Now I am trying to add these 3 thing together to get an external link without success with the following =Directory&Forex1&Location1
I get this below, but no external link 'C:DemoVP[Australian Dollar Japanese Yen Cash.xls]Sheet1'!A2
I have recently come across an issue I have never seen before.
In the attached xlsx file, I have formulas which are trying to reference an external file (C:UsersDELLDownloads[test.XLSX])
How do I delete the external link without losing the formulas.
test.xlsx
My system is Vista Ultimate with Office 2007. I use Norton Internet Security 2007 with Microsoft Office Automatic Scan disabled.
For many years I have used a spreadsheet which links to an external data source - eSignal. eSignal provides real time updates to the spreadsheet.
The problem occurs after I click the link to enable automatic update of links.
About 2 out of 3 times when I load this spreadsheet Excel has a problem with indigestion and closes.
Here is the problem report:
Problem signature
Problem Event Name:APPCRASH
Application Name:EXCEL.EXE
Application Version:12.0.6331.5000
Application Timestamp:48fa2869
Fault Module Name:EXCEL.EXE
Fault Module Version:12.0.6331.5000
Fault Module Timestamp:48fa2869
Exception Code:c0000005
Exception Offset:000453a2
OS Version:6.0.6001.2.1.0.256.1
Locale ID:1033
Extra information about the problem
LCID:1033
Brand:Office12Crash
skulcid:1033
Bucket ID:1048410467
Does anyone have any idea how this error can be avoided? (Even with a clumsy workaround?)
I have some sumifs() functions in cells in a workbook that contain external links in them:
Code:
=SUMIFS('C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$E$54:$E$88,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$C$54:$C$88,$G50,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$A$54:$A$88,H$49)
The formulas work fine if the external file is open. However if the linked file is not loaded and I load the file with the external links, as soon as the file with links is calculated, some of externally linked cells turn to "#VALUE" errors. I have a need to have only certain people see the employee file that is referenced above but the others need to update the file with the external links and not have errors in cells.
The really weird thing is that some cells yield the #VALUE error and some don't, even when both have SUMIFS() functions in them. Even cells with the Exact same formula in them will sometimes yield #VALUE and other times yield a real value.
I have several links to external workbooks that cannot be found...
I have deleted all Name references that posses a ref#
Also I have tried to change the source to the workbook that I am using, but I keep getting a message that says invalid reference to external workbook! In other words it won't let me change my source
I have several work books that I want to change there source, or even get rid of because I can't update them anyways and I don't think it matters if they are updated!
Is it possible to update external links on a worksheet via running a macro?
I have a worksheet where some cells have external links to other files, and every month I have to scroll through and do the typing in order to have everything up and running.
A typical cell has formulas like =[E:ReportsAug-2007.xls]Data!F25
Another cell =[E:ReportsAug-2007.xls]Data!F43
and so on.
Every new month I actually have to replace all [E:ReportsAug-2007.xls] occurrences with for example [E:ReportsSep-2007.xls]. So, is it possible to have a macro which opens a textbox of which i can type the new month 'Sep', so that when the macro runs I save all the typing?
how do I edit external links automatically in name manager?
I got external links like
='C:folder1[file1.xls]SHEET1'!$CM$15
It is easy to replase manually few links, but what about few hundreds...
How do I replace from
='C:folder1[file1.xls]SHEET1'!
to
='SHEET1'!
automatically ?
I am designing a spreadsheet for work.
each of the people i work with have their own workbook with their name as the title, John Smith.xls.
There are about 20 of us on the team with their own workbook with the same design.
We record information on these weekly and so there are 20 files in the folder for Week 1, 20 in Week 2 etc.
I want to make a master spreadsheet with will collect all of the information on these sheets and list them on a master spreadsheet every week.
So the external data i am linking to is found in a location like
='C:SpreadsheetsWeek26[John Smith.xls]Sheet1'!$A$1
Here is my problem,
I need the 'Week 26' and the 'John Smith' to be changeable and preferabilly linked to a cell using the INDIRECT formula so I can change all references to Week 26, 27, 28 etc and also change the workbook name it is referencing.
I have attempted to do this in the following way
I broke down the pathname to seperate cells
Cell 1: 'C:Spreadsheets
Cell 2: A reference linking to a cell containing value: Week 26
Cell 3: A reference linking to a cell containing value: [John Smith.xls]
Cell 4: Sheet1'!$A$1
I then used the CONCATENATE formula to link these into a single cell as a text string.
I then tried to use INDIRECT to link to the filemane in the CONCATENATE cell.
This did not work.
I need a way to make parts of the filename variable and linked to one cell.
I also need to do this while the other workbooks are closed.
I have found some information on INDIRECT.EXT which as far as I can make out involves installing mods to Excel to make these work, I cannot do this as the spreadsheet will be used across our network and will eventually be used by thousands of users.
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
I have VBA code that creates three reports based on fields a user chooses. When the user clicks the button to create the first report, links are built to the files which contain the fields they chose. There are approximately 15 files that need to be linked in every report.
When the links are built, referencing the external files, the system is extremely slow.
I have Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual.
Despite these settings, the links pull in updated values.
Also, to create the last two files, I do a FIND/REPLACE to change the cells referenced in the links, causing it to choke.
would get the links in place without updating each one as it is created?
I have the following code in Sheet1 for file Book7.xls
Private Sub Worksheet_Calculate()
On Error Resume Next
For Each rcell In Range("A1:D6")
Select Case rcell.Value
Case Is >= 5
rcell.Interior.Color = vbBlue
Case Is < 5
rcell.Interior.Color = vbRed
End Select
Next rcell
End Sub
Range A1:D6 is linked to an external source file which I am changing every now and then.
A1 has the formula:
=[Book8.xls]Sheet1!A1*1
Copied all the way to D6.
[Book8.xls]Sheet1!A1:D6 presently has all values 1 hence the formatting in Book7 is all red.
Now I an changing source file to Book9.xls (thru EDIT menu->LINKS). [Book9.xls]Sheet1!A1:D6 has all values 10. But when I change external source to Book9.xls, the right formatting did not apply. All are still red (instead of blue). I still need to go in one of the cell and press F2 and F9 to trigger calculation.
How do I change the code in order to recognise the calculation in order to trigger the codes in the event?
I have a workbook that is being used by many individuals. Within it contains links to an external workbook on a server. Currently the links in the workbook map the server to drive "T".
If a different user maps the same server to another drive letter, I assume these links need to be updated with the drive letter he/she is using for that server?
The goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.
I am trying to set up an external cell refence within my workbook for example:
='[File.xls]Tab'!$C$9
When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"
It only shows the new file path name and not the value in the new sheet--even after updating links.
I am also attempting to do this using the my network places file path instead of the letter drive in my computer.
I have an Excel 2003 workbook (named TargetWorkbook.xls) with some links to a CSV file called DataSource.csv. I use Excel 2007 to open the workbook. I checked the "Update links to other documents" option under the "When Calculating this workbook..." section on the Advanced pane of the Excel Options window. I unchecked the "Ask to update automatic links" option under the "General" section on the Advanced pane of the Excel Options window. I selected the "Don't display the alert and update links" option on the Startup Prompt dialog (accessed from the Edit Links dialog) I created a simple Auto_Open macro with the following statements:
Sub Auto_Open()
Workbooks.Open Filename:="C:ProjectsExcelTestDataSource.csv", ReadOnly:=True
Worksheets("DataSource").Activate
Workbooks("TargetWorkbook.xls").Activate
Windows("DataSource.csv").Visible = xlVeryHiidden
End Sub
The DataSource.csv file is updated daily. I want the cells of TargetWorkbook.xls to automatically update from the csv file when I open TargetWorkbooks.xls without displaying a prompt. I plan to deploy this workbook to a server and open it programatically via a Windows Service (I know, I know, Microsoft doesn't encourage this)and can't have it throwing up a user dialog.
Here is the problem: When I open TargetWorkbook.xls manually using Excel 2007, it updates the links but throws up the dialog "This workbook contains one or more links that cannot be updated...". When I click on the "Edit Links..." button, it displays the message "Warning: Open source to update values". When I click the "Check status" button for this link, it says "Source is open". How do I prevent this dialog box from poping up? I've been wrestling with this for a few
days now and can't find a solution.
I have a sheet called "Sheet1" in workbook "File1". In this sheet, I have a lot of formulas and some of them involves linking to external files located in E: Finance folder. Is there a way to automatically highlight those cells containing a formula that references to external files?
View 3 Replies View RelatedIs there a way to log the userid of the person who last made an update in the external file links?
View 4 Replies View RelatedUpon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
I have a list of objects in column A
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
I have a workbook linked to another one. When I try to change the links to another book, I get a message ' the cell orchart your are trying to change is protected and therefore read only". I have checked and doubled checked and cannot find any worksheet that is protected. How can I find the problem cell or change the link to another book? I have the password but cannot find the worksheet?
View 2 Replies View RelatedI have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.
Is there anyway to check if a range is being used as a link in another sheet.
Eg.
In sheet1 , A1 i have a value Apple.
In sheet2, A1 I have linked it to sheet1 A1.
is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.
I have a workbook with a ton of links to other tabs, but all links and tabs are within the workbook (none to other documents). I've found that a lot of the links are no longer working. This document is only a month old, so it's not outdated or anything. i find myself having to go back and re-link everything. This is a big file...i understand if i have to re-link everything once, but i'm worried that the links will "break" again.
does anyone know why these links within a workbook are not working? is it something i have to fix in the Options?
Somehwere in a review of your book, it referred to an add-in that attempts to break all links to a workbook. I looked on the site (microsoft) with the provided URL but could not find it. Is it still there? Does it work for EXCEL 2002?
View 4 Replies View RelatedI have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
external links.png
I'm having trouble working this around, i use the following code in one of my Workbook sheets.
View 10 Replies View Related