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.
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?
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:
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 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")?
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.
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?
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)
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:
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?
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.
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.
How do you convert all the picture files in a worksheet so they can be processed the same? Half my pictures have eight white adjustment circles framing them when they are selected, the other half have four blue circles and four blue squares.
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.
I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.
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.
Trying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?
Need a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder) North South East(sub folder) West(sub folder) Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1 While DoIt = 1 Open "C:TempList.txt" for output as #1 Print #1, Files In Folder Close #1
Open "C:TempList.txt" for input as #2 while not eof(2) Line input #2, MyFile Display MyFile on screen Wait for 30 seconds Close MyFile and MyApplication that opened it wend Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer() Dim I As Long Dim strFilename As String Dim strPath As String strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/" With Application.FileSearch .LookIn = "C:Documents and SettingscDesktopNew Folder" .FileType = msoFileTypeAllFiles .Execute For I = 1 To .FoundFiles.Count strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1) With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & strPath & strFilename _ .......................
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example: 12/20/2007 Your Name 123 Somewhere St. Here, OH 45111 Home Visit
I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B BA = W RG = R SO = P JA = Y BE = L VE = GY GR = G VI = V MA = BR BJ = TA OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
I have 50 files each with 1000 numbers in column A.
I need to compare every list with every other list and calculate the Pearson function.
I am ok with the vb code to compare every file with everyother.
I can do this by opening each file then closing but it takes too long.
Each of the 50 csv file names is in my destination workbook
I would like to define an array using the file name, then extract 2 lists without opening the files then perform the pearson function and place the value in the destination workbook. (The pearson function just measures the strength of correlation between 2 sets of numbers)
I have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
I have 22 CSV files. I want to search each of those files for a specific text string (ie: "profile" or "description"). When the macro finds the word in any one of the CSV files I want it to copy that complete csv worksheet and paste it into an excel xls file.
how I can go about doing this. Not also that while vlookup is possible, its very time consuming if i have to do this 50 times over and each time the csv files have different file names. that is why i want to search within a file (each file has its own unique identifying text string)
I've been having a small problem with my UDFs. I'm sorry if it's already been asked but english isn't my first language and I don't know the proper search key works to find my problem.
Basically, I've been programming functions destined for a single workbook so I don't use addins. If I write a function called J13F and I try to write that in one of my excel sheets, it generally doesn't work. Excel makes me write it like this:
='myfilename.xls!'J13F.J13F(Sheet!F21)
However, sometimes when I close then open my file, this won't work and excel will make me use my function normally: J13F(Sheet!F21)
I'd like to be able to use the function normally in every sheet in my workbook, how can I do that?
I am using several spreadsheets which are currently linked to specific cells in other workbooks. These are updated through dating the links whenever needed. However I would rather not use formulas in the cells at all and only have value updated by using the link function within VBA. I cannot find how to do this though.
Just to reiterate. I currently have cells that have"='\server01main_foldersubfolderDocument.xls" for example. The formula syntax is not exactly right but in the spreadsheets it is so you get the idea. These links are updated (when the workbooks are not open) when the links are update via the edit menu or a macro which does the same thing.
What I want to do is have no formula in the cells (only values) and use the feature that takes the value from the other workbooks but in VBA exclusively. This is what I have not been able to find out how to do.
