Replace Multiple File Link Locations
Nov 16, 2006
I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))
and
=IF(WEEKDAY(F2)=1,E10,IF(WEEKDAY(F2)=7,E10,'J:DailySeptember 2006[Cash Flows -Sep06.xls]1st'!$I$40*-1))
As you can see the month is shown in 3 ways...
September 2006
01Sep2006.xls and
Sep06.xls
I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............
View 4 Replies
ADVERTISEMENT
Mar 5, 2010
I have to select about 200 odd files from a folder, and move them to a different folder. The problem is, I have to manually select the 200 files from a list of approx 10,000 (not in order).
I have a list of the filenames (with extension) in an Excel Spreadsheet, and I'm wondering if there's a way to automatically move the files using an Excel macro or something?
example:
File location = C:EBDumped
and I need to move the 200 files to C:EBSent
So I would need to move ONLY the files where the file name is in the Excel list.
The file type (extension) is always the same, but the name (and length of the name) is different.
Is this possible using Windows XP with Excel XP?
View 9 Replies
View Related
Oct 27, 2008
I use the below code to specify where a database is:
View 2 Replies
View Related
Jul 5, 2013
Trying to copy records from one spreadsheet to multiple spreadsheets located in different directorates. Although I can create multiple Macros to address the need, I want to use ONE MACRO that utilizes two variables to represent:
1) The targeted file directory
2) The targeted file name
Items 1) and 2) are stated in two cells within the main spreadsheet and as you can already imagine, these values change IAW the name of the target file.
This is what I am trying to do:
File cell A1 = "C:ORLOAcquisition"
File cell A2 = "target.xlsx"
Basically replacing the code below
ChDir "C:ORLOAcquisition"
Workbooks.Open Filename:= "C:ORLOAcquisition arget.xlsx" , UpdateLinks:=3
with, ChDir ""A1"" Workbooks.Open Filename:= ""A2"" , UpdateLinks:=3
I was able to do it with target-spreadsheets located under the same workbook but my code does not work when trying to activate workbooks located in other directorates in the network.
View 2 Replies
View Related
Mar 30, 2014
HTML Code:
=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))
I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?
In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.
I have probably over complicated?
View 3 Replies
View Related
Mar 16, 2014
Distance Calculations Example 1.xlsx
I need quickly identifying multiple locations based on their proximity to an initial location and each subsequent location selected. I have attached an example spreadsheet that is structured in the following manner:
Cell H2 is the 'initial' location that will be used to identify all subsequent locations
Colums A through C are location specific details including latitude and longitude of all 450 possible locations
Column D is a distance calculation based on the difference betwen the location in column A to the 'initial' location in cell H2
Cell F2 is the necessary minimum distance between locations(in miles)
Cell G2 is the number of locations needed(this will vary)
What I am trying to accomplish is to fill column H with the number of locations specified in cell G2 that are atleast the minimum distance in F2 apart from each other, like drawing virtual circles around the locations on a map.
I am pretty sure this can be done with a Macro, but am not sure if that is the best solution or not. I am a novice Excel user, so even though I don't know the solution, I can already see two issues:
1. Whatever the solution is, the reference in column D will need to change to calculate the distance for each subsequent location instead of staying absolute with cell H2(H3 for second location, H4 for 3rd, etc...)
2. With each selection that is made, any location less than the specified distance in cell G2 should be removed from any future choices for subsequent locations.
View 4 Replies
View Related
Jun 27, 2014
Do we get the option in excel to copy from different locations (just copy a part from say Sheet A, Sheet B and some other excel file) and then pasting all at once in some other file?
View 2 Replies
View Related
Feb 13, 2006
In a worksheet, I have 15,000 links to data files as follows:
'C:DataA1[DataA1]
'C:DataA2[DataA2] and so on.....
I would like to replace them as follows:
'C:DataB1[DataB1]
'C:DataB2[DataB2] and so on ....
As you can see, I only want A to be changed to B, however, when I use Find &
Replace, it will prompt me the location of the new link file for every single
replacement.. 15,000 !.
View 11 Replies
View Related
Oct 22, 2008
I am currently trying to automate some excells workbooks that my company uses. The one i'm working on is a listing of all the change orders we have. Unfortunatly there are roughly a 1000 rows, each containing the information for the change- change #, Date opened, part numbers, change description, status, date closed.
Order 0001 - 10/22/08 - 0156, 7251, 9901 - delete bag - closed - [blank] -
Order 0002 - 10/22/08 - 0018, 0612, 0875 - add notes - open - [blank] -
What I want to do is type a part number in, it checks to see if there are any rows that already have that number and have an open status (as apposed to closed) and then tells me where the conflict is. so in the above example if I typed in 0612 it would tell me that number is already in use. I am using excel 2003 if that makes any difference.
View 2 Replies
View Related
Sep 14, 2009
I've been using the following macro ....
View 8 Replies
View Related
Apr 24, 2009
when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:
=Safety.xls!$A$5
=Safety.xls!$A$6
How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.
View 3 Replies
View Related
Jun 12, 2007
Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...
View 9 Replies
View Related
Jul 7, 2008
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies
View Related
Jun 4, 2013
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
View 1 Replies
View Related
May 16, 2013
My raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?
View 1 Replies
View Related
Jul 25, 2008
linking a CommandButton to another File. I made a CommandButton but would now like to know how to write the Macro to make that Button, when clicked, bring me to a file saved in Excel.
View 9 Replies
View Related
Jan 29, 2009
I have some code that looks at the part # in column D and then goes out to the D drive to drawings (.pdf files) and creates a hyperlink.
The problem is that it is only creating a link to the .pdf file and not to folders. I need a hyperlink to the folder so when it is clicked the open folder pops up and shows the multiple files associated with that part.
Link should be the same regardless of file or folder, no? As long as the name matches link should be made, correct?
E.g.
123-100.pdf (column D part #) links to 123-100.pdf file on CD
456-200 (folder name in column D) no link created to folder named 456-200 on CD.
Sub Hyper()
MyPath = "D:"
StartRow = 2
EndRow = 20
x = 0
For i = StartRow To EndRow
Cells(i, 4).Select
MyFileName = ""
MyFileName = Dir(MyPath & Cells(i, 4).Text, vbNormal)
If MyFileName "" Then
x = x + 1
Cells(i, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="D:" & MyFileName
End If
Next i
End Sub
View 9 Replies
View Related
Feb 26, 2007
What is the script in order for me to link a MS Word file to excel. What I intend to do is to customise the commandbutton, so that once a user click on it, the linked MS Word file (stored in the local drive) will pop-up and he/she can do the necessary amendments to the file?
View 3 Replies
View Related
May 18, 2007
I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:
File#, Date, Status, Customer, Etc.
This is accomplished by copying a row of data in the active workbook and pasting as a link on the master. When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again. The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.
What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.
Thus, where the pasted link formula reads:
='C:Active Files[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:Active Files["&$A25&".xls]Home'!$O$1"
View 3 Replies
View Related
Dec 21, 2012
Basically, at month end, my company will create a new folder with the name as YYYY_MM (e.g. 2012_11). In this folder, a new file will be created as: earnings_YYYY_MM (e.g. earnings_2012_11).
What I want is to automatically update the link to the most recent file: earnings_YYYY_MM, whenever it is available. For example, next month there will be a new file earnings_2012_12 created in the new folder 2012_12, then the new file will be linked with.
View 1 Replies
View Related
Feb 22, 2013
I have a "cost.csv" file that contains the cost of the toys. The cost is show in $ with two decimal places i.e. $0.75
I created a new MS Excel 2010 workbook file named as "customers.xlsx" that has four columns i.e. Item, Cost, Order, [Total Amount] columns. I used the paste link command and the Cost column data is linked with the cost column in "cost.csv" file.
When I write order quantity in the order column and then try to use the following expression in [Total Amount] column i.e. "Cost * Order" then I received error due to following reason i.e. "Cost is a text value and Order is a numeric value and excel is unable to evaluate the expression." I have done it by first remove the $ sign from cost using RIGHT and LEN FUNCTIONS.
Now I want to change the format of "Cost" column i.e. I want to show the cost in Euro but not in $. Here I am stuck as I am trying to change the format of the Cost linked cell but unable to change it either I applied the new format, the text remains shown $0.75?
Is this the problem as data is coming from .csv file? If it is then how I can accomplish the task?
View 2 Replies
View Related
Jul 14, 2007
I'm sure this is an easy one but it's unfortunately beyond me. I have a update links formula in which I would like for it to update with the name of the file being in a cell. Example:
The file name is Week20.xls
Cell A1 has the value of Week20.xls using a formula (="Week" & A2 & ".xls")
Currently I have it by looking at the actual file location like this: ='C:Documents and SettingsMy DocumentsWeekly Sales6 Sales Comparisons[Week20.xls]Daily Comparisons'!$I$4
But rather then pointing directly to the file path I want it to update by the name of the file in A1. So I'm sure its something as simple as replacing [Week20.xls] with some formula but I don't know what that one is.
View 3 Replies
View Related
Jun 27, 2013
I have attached 2 files to explain what I really need:
In file A.xlsx I have 2 sheets. In the B1 cell from Sheet 1 I have created a data validation criteria based on the sheet 2.
I would like to get that same data validation criteria based on the A.xlsx onto the B.xlsx.
Is it possible? If not do you have any trick, solution, workaround to link a list from one excel file to another?
View 4 Replies
View Related
Oct 26, 2009
We're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
View 7 Replies
View Related
Jan 9, 2012
I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)
I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:
=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))
Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.
What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.
View 3 Replies
View Related
May 19, 2014
I have a template excel file that has links to data in another excel file in the same directory as the template. However, I save the template with a new name in a new folder and the link no longer works. The link changes to the location with the new file and of course the file I am trying to like to is not in that location.
The link shows the full path in the template file so I would think it would keep the full path when saving to a new location but the file doesn't.
How do I make the link address static?
View 6 Replies
View Related
May 31, 2006
I'm using Excel 2002.
I have one workbook with data linked to another CSV file (It's about 40000rows). When I open the workbook, "THis workbook contains one or more links that cannot be updated." message appears and asks me to open csv file if I wanna to update (although I set full path for links in cells). I wonder if there's any way to update link without opening csv file? Or Excel can not update link without openning the resource file?
View 3 Replies
View Related
Jan 22, 2008
I have a problem that may be a little bit diff. from must of the Readonly post that I have found here. Most people would like to have all of there files opened as readonly files, but I don't in this case. I am copying a number of files that has VBA code in just about all of them. I am using a CD writer to copy the files to a CD using Windows Explorer and that is working just great, No problem copying the files.
My problems comes in when I copy the files from the CD to another computer, all of the files are Readonly files on the new PC. I have not saved them as readonly and don't want them to be readonly files. Does anyone have any ideas why this is happening what I need to check or change to get the files to another computer without being a readonly file.
View 2 Replies
View Related
Jun 26, 2014
There is a problem in joining one cell to the other file
The below is screen shot when l click the on every cell the image must be open
How can do it?
Capture.PNG
View 1 Replies
View Related
Jan 23, 2009
I am trying to insert a link that once clicked will open a file (word, excel, powerpoint, etc. ) that has been saved on my computer.
View 3 Replies
View Related
