Links To CSV Files, Find And Replace Not Working
Apr 9, 2009
I have a large spreadsheet that has links to CSV files. Every month I need to create a new folder and put the CSV files into that folder, all the CSV files from month to month have the same name for each worksheet.
I'm trying to do Find and replace on the Folder name within the links to change say, February to March so I can just add the CSV files for each month and the spreadsheet will link to the new CSV file in the folder.
I have even tried adding all the February CSV's to the March folder so that when I overwrite the CSV's it will update the master spreadsheet, but it keeps asking me where to look for the folder.
Here is an example of a link:
='\FileservermsbilBRANCH REPORTSBM ReportsFebruary[TItemToDispense@40186 Export Dispensed Item Data-00.CSV]TItemToDispense@40186 Export Di'!BM2
As you can see the folder is named February within the link and I need to replace it with March.
View 9 Replies
ADVERTISEMENT
Feb 5, 2014
[Code] .....
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
View 6 Replies
View Related
May 15, 2014
In the attached file I want to do a Find/Replace for 599 --> 599.0 and 428 --> 428.0. When I do a F/R for each it tells me it has made 3 and 5 replacements respectively but the data is not changed?
View 7 Replies
View Related
Sep 11, 2008
I'm using Excel 2007 and trying to replace about 2000 commas with full stops. I want to use find and replace but keep getting a message saying that Excel cannot find the data I'm searching for.
Excel help suggests I haven't clicked on "find" before clicking "replace" but I did.
I've tried highlighting the column in which I want to make the replacements, but same message.
Also tried highlighting nothing, same message.
Tried copying and pasting the whole thing into a new file, but same message.
View 14 Replies
View Related
Oct 9, 2013
For certain cells in this spreadsheet Excel's find and replace is not working:
For example, if I type the species "carex_filamentosa" it will say "...cannot find the data you're searching for"
View 2 Replies
View Related
Dec 6, 2012
I'm copy and pasting many formulas from different worksheets. After pasting, I'm trying to go into each of the cell references and update it to the new worksheet. For example, let's say the cell has a reference to another cell of: ='Worksheet 1'!xx:xx I want change about 50 cells like that at once to ='Worksheet 2'!xx:xx However, every time I pick the Replace feature, either one at a time, or all at once, it opens the Open file dialog box. I've confirmed that it definitely "Finds" everything correctly....it just happens every time on the "Replace".
View 3 Replies
View Related
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
Nov 18, 2008
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?
View 9 Replies
View Related
Jul 12, 2013
I am using Excel 2010 on Windows 7.
I copied (with 'Paste Link') rows from a master Excel file to a file that performs calculations. This file (and many others like it) need to be modifed by other people. My tester has informed me that the data is not updating between the files for her, although it does work for me.
When I check 'Data->Edit Links' it shows the status of the file as 'unknown'. If I click 'Check status' it is then OK, but the next time I look at it, it has reverted to 'unknown'.
1. How do I get the status to remain at OK?
2. Why do the links work for me, but not for my end users?
View 2 Replies
View Related
Nov 19, 2012
I have been having problems lately with excel files on Network drives. I have one file that I created on 11/12/2012. However, on Friday that file showed it had been modified by myself on 11/13/2012. I did not do this. As a note, this file contains macros based on a button that is clicked from a worksheet.
I then copied data from this worksheet into another file, creating a link that I did not know was there with the worksheet and the macro. From the first workbook, I also copied in data collection from two other worksheets, also I believe with a link to the first workbook.
I did a test on the first file this morning and found out you can open a file, make no changes, and close the file. Excel will ask you to save the file. If you say Yes, the file will show being modified with today's date and your userid, even though nothing really changed.
What I am actually trying to find out is that can the original file be altered somehow that will automatically update the linked file? Or, when I open the linked file, do I have to say "YES", I want the updates before the linked file is updated?
There were some questions as to how a value showed up in the link file (or actually ended up being removed) and when I looked at the original file it was also removed. We checked on stability across the network drive but found it to be okay, now we are looking at the link issue.
View 2 Replies
View Related
Mar 3, 2007
This workbook contains links to other data sources.
*If you update the links, Excel will attempt to retrieve the latest data.
*If you don't update the links, Excel will use the previous information.
Note that data links can be used to access and share confidential information without permission and possibly perform other harmful actions. Do not update the links if you do not trust the source of this workbook.
I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.
Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.
Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.
I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.
So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?
View 9 Replies
View Related
Dec 6, 2006
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: ....
View 7 Replies
View Related
Aug 5, 2014
I am working on a file that has multiple worksheets with many links, lookups and formulas; some between the worksheets and some external to another Excel file. The plan is to use this file as a template and copy it over and over with new names. Once I save the file as a new name in a new folder on my network, will I lose all these links, lookups and formulas? Or is there a way to maintain the links or do a global change of the formulas (i.e., the original file name is "TEMPLATE" and the new file name is "PROJECT!1")?
View 2 Replies
View Related
Mar 14, 2008
We use Excel 2003 and 2007 to track labor data for a field service company. The field tech's complete daily forms and the office staff copy/paste this data into weekly billing worksheets. They use naming conventions to show the jobs and dates in the file name (Daily Labor - Job1 - 031308.xls and Weekly Labor - week ending 3-16.xls. What I would like to do is link the daily data to the weekly forms as they come in. Is there some way to do this when the job names and the dates are always different? Can I tell Excel to expect data files based on the naming conventions as in the Weekly is named "Weekly Labor - Week Ending 3-16.xls so data files named LaborJob1310.csv, laborjob1311.csv would automatically update the weekly sheet? I can put these in a folder and link them, but I don't know how to provide an easy way to do this for the upcoming week.
View 4 Replies
View Related
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
May 12, 2014
I'm trying to set up a macro so that: in column C I will see the current (old) links being used by the workbookin column D I will input the new links I want to use in the workbookthe macro will open all those files in column Dthe macro will then replace the current (old) links with those listed in column D
I have the following so far, but something doesn't seem to be working - it gets as far as opening the first new file but doesn't open any others and doesn't change any links (although I've tested the part to change links separately and this seemed to work ok).
Sub linked_sheets()
Dim LinkedBooks As Variant
Dim i As Long
LinkedBooks = ThisWorkbook.LinkSources()
For i = LBound(LinkedBooks) To UBound(LinkedBooks)
Cells(i + 3, 3) = LinkedBooks(i)
[Code] ..........
View 1 Replies
View Related
Mar 17, 2006
I've found countless, very useful macros that do 1/3 of what i need. My needs:
1) A macro to look in a set network folder, and generate a list of Excel files, and display them (1 per row, just the file name if possible)
2) Each file name is a hyperlink to open that sheet
3) A macro to look at the file listed above, then list in the column B a value from a set cell. So it would ideally output:
1| RH0018.xls A1CellValue
2| RH0019.xls A1CellValue
3| RH0020.xls A1CellValue
View 2 Replies
View Related
Feb 5, 2009
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
View 6 Replies
View Related
Jan 14, 2009
I have a workbook that archives by date and is saved for 1 month at a time. At the end of the month, all the archived workbooks are moved to another location and are no longer needed.
The problem I am having is I don't know how to break the link between the master and all the saved workbooks. So when I open the master it is looking for the old workbooks in the folder location and they do not exist. How do I break this link?
View 5 Replies
View Related
Sep 12, 2010
I and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
View 5 Replies
View Related
Jul 2, 2013
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
View 4 Replies
View Related
Apr 20, 2006
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View 3 Replies
View Related
May 30, 2013
I have links between Excel files and Word files. I use these files for multiple projects. When I copy them and paste them to a new folder the new files would be linked together. For some reason this has stopped. Now when I copy, the Word file has links to the original document, not to the copied document.
View 7 Replies
View Related
Mar 18, 2009
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View 2 Replies
View Related
Aug 29, 2006
I am trying to open set of excel files to fetch data starting from a master excel file where the links are given using a loop. Some of files given as links are either absent or the link is wrong.
* What is the syntax to find if the link is correct/present
* What is the syntax to find if the file is present in specified location
* What is the syntax to find if the file is password protected to open
* I use error handler to resume the next statement if OPEN statement fails.It works fine inside the loop only for first two files.
For example if the third link is errorneous the error handler fails and RUN TIME ERROR occurs.
View 5 Replies
View Related
Oct 26, 2009
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or
View 9 Replies
View Related
Jul 14, 2006
This one has really got me stumped:
Sub NCR()
Application.DisplayAlerts = False
Workbooks.Open ("J:AcctMgt3NWFMScriptingScriptsDailyReportsNCRNCRvdn.xls")
Sheets("NCRvdn").Columns("B:B").EntireColumn.AutoFit
dRow = Sheets("NCRvdn"). Range("B1").Text
dMonthDay = Format(dRow, "mmdd")
dDay = Format(dRow, "dd")
dPrevDay = dDay - 1
dMonthName = Format(dRow, "mmmm")
dMonthNum = Format(dRow, "mm")
dYear = Format(dRow, "yy")
dPrevDay2 = dMonthNum & dPrevDay.............
This opens the first file and dRow="7/13/2006". The next file that it opens contains links to information from the previous days. Without VBA you just drag the previous day down, select the row of data and do a replace all, say from 0712 to 0713. As you can see I even tried to make it use the specific data I wanted versus the variables; still doesn't work. What really gets me is that if I go back to the sheet after this code runs, I go to EditReplace, replace all "0712" to "0713" and it does it. It has to be the code then right??
View 3 Replies
View Related
Feb 24, 2014
I want to change country name 'California' and 'Belziuma' with new country name as 'USA' in column B2.
Similarly change country name 'Moscow' with new name 'Russia'.
If cond not working.
View 1 Replies
View Related
Jul 19, 2007
I would like to be able to find out what other spreadsheets rely on the spreadsheet I'm currently in.
For example spreadsheet A cell D1 links into spreadsheet C cell F1
So if I was in spreadsheet C I'd like to be able to run or do something that told me that cell F1 was used in spreadsheet A cell D1.
View 9 Replies
View Related
May 27, 2014
I've been given the assignment to replace an old company name with the new one in an excessive amount of excel files. All the excel files seem to be some kinds of forms with fancy buttons and such. I've been trying to research this quite a bit but only found some shady programs which I'm not installing on company computers so I decided to write a batch script to try and accomplish this, if you have any other ways to do this do let me know. So far I've only got a basic skeleton of the batch script, like looping through all the excel files in a directory and then loop through all the worksheets within the file. Here's how it looks
[Code] .....
I just want some confirmation that this would actually work and how to replace all occurrences of a string in a workspace.
View 14 Replies
View Related
