Refreshing Lined Cells And Breaking Links

Oct 25, 2011

I would like to refresh all external links to other xl files (on a network drive) and break all links

I have a code like this:

For Each l In ActiveWorkbook.LinkSources
Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True
ActiveWorkbook.Close False
Next l

For Each l In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks
Next l

It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...

I cannot open all files because there are many of them and they are like 40mb each.

There are no defined names in the file, except print_area

View 2 Replies


Links Breaking From Two Workbooks

Dec 18, 2013

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).

View 9 Replies View Related

Breaking Links To Some Workbooks

Jan 14, 2010

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.

View 9 Replies View Related

Breaking Links And UsedRange

Jun 23, 2006

I am working on a project that involves copying data from one sheet to another, then breaking all links to the original sheet. This part of the program is working fine. The problem comes when I try to put borders around the used range of cells in the new sheet. I want the border to only go down to the last row that contains text in column A.

Currently, the program is putting a border around all the cells that have text plus those that originally contained links (all the links are not always used). So even though I have removed the links from the new sheet, usedrange still includes these extra rows. This is no good for printing, since the links spill over to page two and I dont want that second page to print if there is no data.

So I basically need to know how to fix the problem with usedrange, or instead need a way of specifying the last row that contains text in column A. I have done a search on this and tried a number of different things, however none have worked so far.

View 9 Replies View Related

Links To Other Workbooks Breaking When The Files Are Replaced?

Jun 12, 2013

I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.

This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.

Is there any VBA code or something that I could run that will update the links to pick up the new files?

View 2 Replies View Related

Excel 2010 :: Breaking Data Links To Another Workbook

Oct 18, 2012

I am running a macro-enabled Excel 2010 file, and there is a link to another workbook that pops up every time I open it asking if I want to update. I have searched formulas and pages, and can't find where it's linking from. I searched the VBA module I've been working in, and I can't find the link. When I click Data->Edit Links->Break Links and confirm, nothing happens. It still shows up as linked, and still prompts me to update next time I open it. I'd really like to get this data link severed.

View 3 Replies View Related

Split Multi-Lined Cells Into Columns

Dec 5, 2007

So I have a worksheet filled with a lot of text in individual cells in one column. What I would like to do is split up the text into different columns in one row so that I can use it easier. The data would be seperated by blank lines within the cell (basically when you hit alt+Enter in the cell). Sample data of a cell is below...

Cell A1:
Directions: Baking an Apple Pie

First you must .... etc

Total time required: 30 minutes

What I would love to be able to do is split up the above sample cell into three different columns, in the same row. So the above cell would become...

Cell: A1 Cell: B1 Cell: C1
Directions: Baking an Apple Pie | First you must .... etc | Total time required: 30 minutes

And then so on and so forth for the rest of the data in that column. I realize that VBA code will be needed for the above... I searched the forum for something that would address this particular issue but have had no luck.

View 2 Replies View Related

Breaking Data Into Multiple Cells?

Feb 21, 2014

Data layout in this excel sheet. I need to break it up properly in order to have a better way to import it into SQL. Is it a good idea to cram multiple data points into each cell. From the DB POC column, i need to separate that which is before the "/" from that which is after the "/" and put each item into it's own row. So there needs to be a new column for "DB" and a new column for "POC". The value before the "/" will go into the "DB" column and the data after the "/" goes in the POC column, with one new row for each item.

View 9 Replies View Related

Searching For A String In Multi-Lined Row

Jun 6, 2014

Best approach for searching for a piece of string from a multi-lined cell. I am trying to do it via VBA but can't seem to figure it out. I tried FIND and Instr. I was thinking of converting the multiple lines into a single line then do an instr comparison from the result but I don't know how to do it.


String to search = "MARY"
Output Location value

----------MARTHA YULET

-----------HENRY THE FIFTH
-----------JOHNNY BRAVO

Cells in column "Names" has multiple lines Cells in column "Location" are not merged....

View 3 Replies View Related

Stopping Cells From Automatically Refreshing After External Data Refreshes

Jan 30, 2010

I have 2 Excel documents.The first document is named "data" and it extracts data from off the Internet every 24 hours. The second document, named "database", is for storing this data. Every 24 hours, I run a macro which imports the data from "data" into a new row in "database", along with the date the macro was run.

My problem is this: Whenever I refresh the external data in "data", my "database" document also refreshes itself instantaneously. My question is this: Is there a way to "cut the connection" between the two documents so that, when "data" is refreshed, the data in "database" DOES NOT also refresh itself automatically?

View 3 Replies View Related

Formula That Links Cells In Tables Together With Cells Not In Table And Give Sum

Apr 18, 2014

What I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.

So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1

then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row

and Finally give me the sum of both corresponding numbers.

IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.

Then returns the sum of R13 and X16 into B5. (B5 should show 176)

View 5 Replies View Related

Edit Links - Break Links Not Working?

Jun 22, 2012

I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.

View 9 Replies View Related

Detect Cells With Links

Jul 27, 2006

How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.

I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links

View 10 Replies View Related

Creating Permanent Links Between Cells?

Nov 5, 2008

I am trying to use Excel to document fiber optic cables that run between different locations. Each cable has 2 ends, but whatever service is connected to one end, will also be a the other. I would like to use an Excel worksheet to represent a termination point (a "light shelf" comprised of several fiber optic cables each made up of several strands of fiber (kind of a table with the 1st 4 columns of 6 rows being cable A, and the next 8 cable B)). I would like to be able to type in the value for cable A Strand 1 and have it linked to another worksheet representing a different location (the other end) and vice versa. I have tried to paste a link, but that seems to get broken. Is there a better way to make permanent links (Excel is bawking about a circular reference, which it is as each end needs to be linked back to the other.

View 9 Replies View Related

Keeping Links Between Cells From Changing

Jan 31, 2013

In spreadsheet 1 I have a bunch of data, no formulas or anything, but data I need. I created spreadsheet 2 to take parts of S1 and have set links to cells in S1 so that when the data there is changed S2 gets updated automatically for me.

My problem is that when I add or delete rows or columns to S1 it's messing up my links in S2, not showing the right data. Issue with my linking that will tie the cells together when rows or columns are added or deleted.

View 1 Replies View Related

Update Of Links In Formula For All Cells

Oct 20, 2006

I have cells (range named Formulas) in a worksheet A (wsA) linking to another workbook. This workbook has several sheets, each a different forecast. From a Radio Button Group on wsA I would like to choose forecast, FC1-FC3, and then all the cells in the range Formulas would be updated with the choosen FC.

I would like a VBA script to handle this:When the workbook is activated the present Forecast in the Radio Button Group should be stored as a variable (oldFC)When I choose a new forecast, the value should be stored in a new variable (newFC)I then want to loop through the whole range (Formulas) and update each cell and it's part of string value to the newFC

Before update:
wsA cell N3='H:[5500-plan-06.xls]FC 3'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 3'!N$17

When new Forecast is choosen this should be update to:
wsA cell N3='H:[5500-plan-06.xls]FC 1'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 1'!N$17

View 2 Replies View Related

During Saving Worksheet Cells Loosing Links?

Jul 4, 2014

When i save the worksheet cells are loosing links to other cells. what is even more surprising it happens only for 6 cells in 6 sheets.

scheme of links
Basic data

On the sheets P1-P6, each cell A1 has a link to Basic data.

View 2 Replies View Related

Macro Automatically Clicking Links In Cells

Jul 19, 2009

I have a list of links in 1 excel worksheet. They are spaced every other row if it makes a difference. I need to create a macro that clicks on every link automatically. Each link is a direct file that I need downloaded every few months. I have no Macro experience beyond hitting record to automate basic tasks.

View 13 Replies View Related

Color Cells With External Links On Update

Feb 27, 2008

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:


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?

View 9 Replies View Related

Identify / Color Formula Cells With External Links

Feb 22, 2008

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 Related

Breaking A Tie

May 24, 2009

I have scores for golf teams that I have ranked. When there is a tie, I wish to break that tie based on another number.

For example, 2 teams tie for 1st place with a score of 312 from 4 golfers. I wish to rank those scores based on a 5th player score and determine the winner to be ranked 1st and the next 2nd without disrupting the ranking order of the other teams.

View 5 Replies View Related

'vlookup' Not Refreshing

Nov 1, 2008

I have been working with one sheet (call it tab A) that has 40-50 vlookup formulas that reference another sheet (call it prices) within the same workbook. Everything worked fine. I change my prices tab and everything auto changes in tab A.

Now I copied 'tab A' 22 times creating tabs B - W. I need to slightly modify each tab and based on my prices data, print out the appropriate tab.

Problem is now, when I change my pricing tab, tab A - W do not update. F9 does nothing. Rebooted didn't work. If I go into the formula and hit F2 to edit & return it recalculates to the correct data. But I can't get it to auto update.

View 6 Replies View Related

Breaking Up A Number

Jan 4, 2010

I have a number in one cell which is between 1 and 15 inclusive, and I want to break which ever number is chosen into the sum of 1,2,4 and 8, with each digit displayed in a separate cell. In the remainder cells if any are blank I would like 0 (zero) to be displayed


15 ----> Cell 1 = 1, Cell 2 = 2, Cell 3 = 4, Cell 4 = 8


9 -----> Cell 1 = 1, Cell 2 = 0, Cell 3 = 0, Cell 4 = 8

Using the numbers 1,2,4 and 8 there is only one way to sum each of the numbers 1-15 inclusive. The results of the 4 cells I'm using an if statement to display an "x" next to an option.

View 10 Replies View Related

Breaking A Cell Down

Apr 3, 2007

I was wondering if there was a way to break down a single cell with it's componets.

For example, if cell A1 has a sum formula containing the sum of other cells, is there a was to just copy the single cell (A1) and pasting all of its different values to multiple cells?

Like, A1 has 100 in it but it is sum up by 4 other cells each @ 25 each, so is there a way to copy A1 and pasting it to different cell to show each entry?

View 9 Replies View Related

Usedrange Not Refreshing

Oct 12, 2007

I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.

Is this correct?

If so, is there another way to "refresh" the usedrange property without saving the workbook.

If not, is there another way I can determine what the usedrange actually is at any point in time?

View 9 Replies View Related

Breaking Out Of Run Away Code

Jul 5, 2006

I am in the middle of designing a macro that has 5 or 10 times ran away with itself causing me to sit & wait for it to finish. I am hoping there is a graceful way out of this without going to office application recovery.

View 2 Replies View Related

Refreshing / Triggering A Cell?

Jun 13, 2014


I take this paste text only to get:


this wont work normally until i open the cell and press enter.

Any way I could do that for more than 1 cell at a time?

View 6 Replies View Related

Breaking Number In To Decimal?

Apr 2, 2014

I'm looking for a formula to break number in to decimal . Which means for 5 digits i.e 12555 to 12.5 and 4 digits i.e 1555 to 1.5

View 4 Replies View Related

Refreshing Protected Worksheet

Jan 5, 2013

I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.

In turn, the current information is displayed on a summary page so we can all see who's doing what.

I want to protect that summary page and its formulas from accidental amendment.

However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.

Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?

View 5 Replies View Related

Breaking Data Out Of A String

Apr 1, 2009

I have strings of data pumped out of a database like so "!OV !IPV ABL (850) !VL SM (150) !AD !PW !QT CC (-350)" If an exclamation point is listed, then no value follows however if no exclamation point is present, then each item will be followed by a value. I am trying to break this data out into a table. I am not sure if this is even possible. I am also attaching an example.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved