ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Updating Links on Excel (https://www.excelbanter.com/excel-discussion-misc-queries/89867-updating-links-excel.html)

Update Link Question May 22nd 06 04:25 PM

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!

Barb Reinhardt May 22nd 06 05:22 PM

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!


Update Link Question May 22nd 06 06:00 PM

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!


Barb Reinhardt May 22nd 06 06:08 PM

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!


Ardus Petus May 22nd 06 08:21 PM

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!





All times are GMT +1. The time now is 07:47 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com