Change links for batch of workbooks
 

Hi everyone,

I'm hoping someone can help me out.

I've got about 40 workbooks each with between 7 and 11 sheets.

The workbooks originally just held data and new (analysis) sheets were added
from a template.

The analysis sheets have lookup formulas relating back to the original
template.

If it is possible I would be grateful for someone to help me with code that
can go through each of the workbooks (all in the same folder) and change the
link from the template link to the data in the same workbook.

I've found this bit of code in this forum and it does what I want but it's
just as easy to go into the menus and do it that way.

Thanks
Trish

Sub LinksChangeSourcewitherrortrap()

Dim stroldlink As String
Dim strnewlink As String
MsgBox "Select Original link Sourcexxx"

stroldlink = Application.GetOpenFilename _
("Excel files,*.xls")
MsgBox "Select workbook to change"

strnewlink = Application.GetOpenFilename _
("Excel files,*.xls")

MsgBox ActiveWorkbook.Name

On Error GoTo errorLinks 'No colon after label name here
ActiveWorkbook.ChangeLink Name:=stroldlink, _
newname:=strnewlink, _
Type:=xlLinkTypeExcelLinks ' This one works at work

On Error GoTo 0 'Cancels the error call.

Exit Sub 'Place this immediately before errorLinks
'label so does not process unless there is an error.

errorLinks: 'Note the colon after the label name

MsgBox "An error has occurred. " & _
"This is possibly due to one of the following:-" _
& Chr(13) & "Incorrect Workbook name selected and/or" _
& Chr(13) & "Workbooks do not have same Worksheet names." _
& Chr(13) & "Check both the above and then re-run the macro."

End Sub

--
Trish

Change links for batch of workbooks
 

Hi Trish

See
http://www.rondebruin.nl/copy4.htm

You can change the red code block to code that make your changes

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Trish Smith" wrote in message ...
Hi everyone,

I'm hoping someone can help me out.

I've got about 40 workbooks each with between 7 and 11 sheets.

The workbooks originally just held data and new (analysis) sheets were added
from a template.

The analysis sheets have lookup formulas relating back to the original
template.

If it is possible I would be grateful for someone to help me with code that
can go through each of the workbooks (all in the same folder) and change the
link from the template link to the data in the same workbook.

I've found this bit of code in this forum and it does what I want but it's
just as easy to go into the menus and do it that way.

Thanks
Trish

Sub LinksChangeSourcewitherrortrap()

Dim stroldlink As String
Dim strnewlink As String
MsgBox "Select Original link Sourcexxx"

stroldlink = Application.GetOpenFilename _
("Excel files,*.xls")
MsgBox "Select workbook to change"

strnewlink = Application.GetOpenFilename _
("Excel files,*.xls")

MsgBox ActiveWorkbook.Name

On Error GoTo errorLinks 'No colon after label name here
ActiveWorkbook.ChangeLink Name:=stroldlink, _
newname:=strnewlink, _
Type:=xlLinkTypeExcelLinks ' This one works at work

On Error GoTo 0 'Cancels the error call.

Exit Sub 'Place this immediately before errorLinks
'label so does not process unless there is an error.

errorLinks: 'Note the colon after the label name

MsgBox "An error has occurred. " & _
"This is possibly due to one of the following:-" _
& Chr(13) & "Incorrect Workbook name selected and/or" _
& Chr(13) & "Workbooks do not have same Worksheet names." _
& Chr(13) & "Check both the above and then re-run the macro."

End Sub

--
Trish

Change links for batch of workbooks
 

Thanks Ron,

Looks just the job!

Thanks again

--
Trish


"Ron de Bruin" wrote:

Hi Trish

See
http://www.rondebruin.nl/copy4.htm

You can change the red code block to code that make your changes

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Trish Smith" wrote in message ...
Hi everyone,

I'm hoping someone can help me out.

I've got about 40 workbooks each with between 7 and 11 sheets.

The workbooks originally just held data and new (analysis) sheets were added
from a template.

The analysis sheets have lookup formulas relating back to the original
template.

If it is possible I would be grateful for someone to help me with code that
can go through each of the workbooks (all in the same folder) and change the
link from the template link to the data in the same workbook.

I've found this bit of code in this forum and it does what I want but it's
just as easy to go into the menus and do it that way.

Thanks
Trish

Sub LinksChangeSourcewitherrortrap()

Dim stroldlink As String
Dim strnewlink As String
MsgBox "Select Original link Sourcexxx"

stroldlink = Application.GetOpenFilename _
("Excel files,*.xls")
MsgBox "Select workbook to change"

strnewlink = Application.GetOpenFilename _
("Excel files,*.xls")

MsgBox ActiveWorkbook.Name

On Error GoTo errorLinks 'No colon after label name here
ActiveWorkbook.ChangeLink Name:=stroldlink, _
newname:=strnewlink, _
Type:=xlLinkTypeExcelLinks ' This one works at work

On Error GoTo 0 'Cancels the error call.

Exit Sub 'Place this immediately before errorLinks
'label so does not process unless there is an error.

errorLinks: 'Note the colon after the label name

MsgBox "An error has occurred. " & _
"This is possibly due to one of the following:-" _
& Chr(13) & "Incorrect Workbook name selected and/or" _
& Chr(13) & "Workbooks do not have same Worksheet names." _
& Chr(13) & "Check both the above and then re-run the macro."

End Sub

--
Trish