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?
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!
I have a problem that I have several workbooks that are getting more widely used by a number of users and I'm starting to encounter problems. The workbooks open files from particlaur locations to retrieve information but I am finding that not everyone has the same drive letter for the same server or shared drive.
For example Server607 could be drive J: for me but drive N: for people in another department. Is there a way to refer to the server location in VBA when opening files rather than the drive path?
I am using pivotable with access queery as the source.
When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same.
When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
View 2 Replies View RelatedI'm looking for enabling an excel file with a password that prevent it to be copied on an another drive.
View 1 Replies View RelatedI 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 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 Replies View RelatedI 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.
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 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 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'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.
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 been trying for months to get this to work by altering code from similar requests I've hunted down on google to no avail.
I am looking to have a "Master" sheet that is populated by data from 30+ individual excel workbooks. Each workbook is contained on a server in a directory "O:JobsJobs In Progress." In that directory are subfolders (named in sequence "C12000 Job1", "C12001 Job2", etc) which contain the actual workbook (Named according to number ie. "C12000 Jobinfo").
I would like this "Master" to automatically pull specific values from each C1200* workbook and paste them in a designated cell so that I can quickly look at specific information contained in each workbook. Each C1200* workbook is constructed the same way.
Short Example of C1200* workbook in which data will be pulled from:
Job Number
C12000
PM
Abe Lincoln
Foreman
Thomas Edison
Contract Date
1/1/10
I'm trying to set-up a costing system as follows:
Each job has it's own xls file, this is a list of manually input costs and a total cost at the bottom (total is the same Cell reference: G24) for all xls files. The xls file is named the same as the job - F0001, F0002 etc.
At the end of every month we then want to create a master list xls file for all jobs being invoiced that month. This is a list of all the jobs - F0001, F0002 etc. In Column A, and the corresponding cost totals in column B.
In the master file we want to be able to type in the job reference to column A (i.e. the file names of the single job files -F0001, F0002 etc) and have the corresponding total for that reference display automatically in Column B (i.e. Cell G24 from file F0001 will display in the master file cell B1, when 'F0001' is typed in cell A1).
Ideally the master file would display all the individual entries automatically, so no need to input the job references either - simply scanning the directory for the other files present and creating a list automatically in the master file, alternatively it can get the job reference from Cell A1 in the individual job files, instead of from the file name.
For three years I've created a workbook (A) that is dependent on another workbook (B) I upkeep. When I open (A), I Edit Links and update the location to my newest weekly file to map from (B). For my 2014 (A) book, I did the same process, but when I update the values, all the links break. Only when I open up (B) with (A) will the values appear.
As a short term solution, I saved (A) with the values in there and disabled the prompt to update values, so others can see the data. This isn't a feasible long term solution since updates will be made weekly, and not everyone will want to open up (B) just to update (A).
I have a consolidation workbook with many tabs. Each tab has many links to 8 data workbooks.
I would like to eliminate the links in all of the tabs of the consolidation workbook to half of the data workbooks.
I am using Excel 2007.
I tried Data/Edit Links, and selected one workbook, but when I hit Break Link, it broke the links to ALL the data workbooks.
I have a Vlookup in a shared workbook looking in another shared workbook for the data I want. I open the file and click "Update Links" but I then get the following message box:
"This workbook contains one or more links that cannot be updated.
-To change the source of links, or attempt to update values again, click Edit Links.
-To open the workbook as is, click Continue.
I know this message is trying to tell me it can't find my links, but the path/workbook/ sheet are correct and the numbers are getting updated. I'm worried the message will deter the other users of this workbook from allowing the use of the vlookup.
