Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Links on Excel
I have a spreadsheet that summarizes data from 300+ different workbooks.
Every month a new file is created and we need to update the summary with the new month data. Is there a better way to update the links on the summarizing spreadsheet? Currently, we need to edit links and update links for each (300+) file name. The only thing that changes in the file names is the month. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Links on Excel
Are you speaking about hyperlinks or a formula that directs the cell to get
data out of another file? "Update Link Question" wrote: I have a spreadsheet that summarizes data from 300+ different workbooks. Every month a new file is created and we need to update the summary with the new month data. Is there a better way to update the links on the summarizing spreadsheet? Currently, we need to edit links and update links for each (300+) file name. The only thing that changes in the file names is the month. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Links on Excel
I'm speaking of a formula that directs the cell to get data out of another
file. Thanks. "Barb Reinhardt" wrote: Are you speaking about hyperlinks or a formula that directs the cell to get data out of another file? "Update Link Question" wrote: I have a spreadsheet that summarizes data from 300+ different workbooks. Every month a new file is created and we need to update the summary with the new month data. Is there a better way to update the links on the summarizing spreadsheet? Currently, we need to edit links and update links for each (300+) file name. The only thing that changes in the file names is the month. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Links on Excel
You could do a global change for the information you want to change or you
could put the month into a cell and give it a name. Select the cell and then INSERT - NAME - DEFINE and give it an appropriate name. If in your cells, you have something that looks like this: Y:\folder1\folder2\MONTH.xls You could change it to include "Y:\folder1\folder2\" & MONTH & ".xls" I hope this helps you. "Update Link Question" wrote: I'm speaking of a formula that directs the cell to get data out of another file. Thanks. "Barb Reinhardt" wrote: Are you speaking about hyperlinks or a formula that directs the cell to get data out of another file? "Update Link Question" wrote: I have a spreadsheet that summarizes data from 300+ different workbooks. Every month a new file is created and we need to update the summary with the new month data. Is there a better way to update the links on the summarizing spreadsheet? Currently, we need to edit links and update links for each (300+) file name. The only thing that changes in the file names is the month. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Links on Excel
Assuming your monthly data file names end with mm.xls (eg: 06.xls for june),
apply following macro: HTH -- AP '---------------------------------------------- Sub NewMonth() Dim sMonthId As String Dim rDept As Range Dim aLinks As Variant Dim iLink As Integer Dim sOldLink As String Dim snewlink As String sMonthId = Format(Range("A1").Value, "mm") aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(aLinks) Then For iLink = LBound(aLinks) To UBound(aLinks) sOldLink = aLinks(iLink) snewlink = Application.Replace( _ sOldLink, _ InStrRev(sOldLink, ".") - 2, _ Len(sMonthId), _ sMonthId) ActiveWorkbook.ChangeLink _ Name:=sOldLink, _ newname:=snewlink Next iLink End If End Sub '-------------------------------------------------- "Update Link Question" <Update Link a écrit dans le message de news: ... I have a spreadsheet that summarizes data from 300+ different workbooks. Every month a new file is created and we need to update the summary with the new month data. Is there a better way to update the links on the summarizing spreadsheet? Currently, we need to edit links and update links for each (300+) file name. The only thing that changes in the file names is the month. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
strange problem with links updates in excel | Excel Discussion (Misc queries) | |||
Excel links in Word | Excel Discussion (Misc queries) | |||
Dynamic Links Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2000 to 2003 update links doesn't work properly any suggest | Excel Worksheet Functions | |||
?? Links Not Updating ?? | Excel Discussion (Misc queries) |