ChangeLink problem

[Diddy]

Hi everyone,

I don't know what I'm doing wrong but this code stops at the changelink line
with a runtime error 1004.

I've checked in the locals window and the two strings are both as expected
and I put the msgbox in to check that the Activeworkbook was the one I wanted.

I've tried different types for the excel link type but now at a loss!

Here's the code

Option Explicit

Sub LinksChangeSource()

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
'ActiveWorkbook.ChangeLink Name:=stroldlink, newname:=strnewlink,
Type:=xlLinkTypeOLELinks
ActiveWorkbook.ChangeLink Name:=stroldlink, newname:=strnewlink,
Type:=xlLinkTypeExcelLinks
'ActiveWorkbook.ChangeLink Name:=stroldlink, newname:=strnewlink,
Type:=xlExcelLinks


MsgBox "Done"
End Sub

If anyone can help I would be extremely grateful

BTW should I put underlines _ where the code breaks in these messages - just
in case anyone wants to try it out?

Thank you

 

[Diddy]

Hi

Working this morning! Arrrggghhhh!

Up 'til after 1am this mornining trying to get this to work

Thanks

 

[OssieMac]

Hi Diddy,

Try putting line in like this

ActiveWorkbook.ChangeLink stroldlink, strnewlink, xlExcelLinks

 

[Diddy]

Hi Ossie,

What I meant to say was that the code was working this morning - How
frustrating is that?

Sorry it came out wrong and thanks for your help

any clue as to why it didn't want to play yesterday?

cheers

 

[OssieMac]

Hi again Deirdre,

We were obviously posting simultaneously before. As to your question I have
no idea other than a glitch in Excel. They occur sometimes and I have
experienced similar things in the past and have learnt that if I am sure that
the code is right then shut down and re-boot and try again. (I should add
that on most occasions it has not helped and it was me that was wrong but I
have had a win on a couple of occasions by re-booting.)

 

[Diddy]

Hi OssieMac,

I have to hold my hand up and say that it was me and not Excel.

The names on the sheet tabs didn't match - code was fine

Could you help me further and explain how I could add an error catcher and
possibly put in a message box to advise the user to check that tab names
match and formulas match and to re-try please

Thank you v much

 

[OssieMac]

Hi Deirdre,

Use an on error routine.
Place an "On Error Goto" immediately before the line of code that can be a
problem.

Place the actual error routine immediately before the the End Sub.

On Error Goto errorLinks 'No colon after label name here
ActiveWorkbook.ChangeLink Name:=stroldlink, newname:=strnewlink,
Type:=xlLinkTypeExcelLinks
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

You possibly know this but just in case. The space and then underscore at
the end of a line is a line break in an otherwise single line of code.
The above code is untested. Feel free to get back to me again if still
having a problem.

 

[Diddy]

Hi OssieMac,

Thanks for that

I've tried it out and here's what I'm gettinng

1. If there are no links to update (tried this one out by mistake!)
runtime error 1004 Method 'ChangeLink' of object '_workbook' failed
at this line in the error section

ActiveWorkbook.ChangeLink Name:=stroldlink, newname:=strnewlink,
Type:=xlLinkTypeExcelLinks

2. Messge box comes up after links have been changed successfully

3. error message but no message box when tab names don't match

What do I need to change?

Thanks for all your help

 

[Diddy]

Hi OssieMac,

Please ignore last message - I've put your code in the wrong places

I'll try harder this time

Thank you again

 

[Diddy]

Hi OssieMac,

Sorry for all the hassle, brain definitely not in gear this morning

I must have still got it in the wrong places. I get the message box if there
are errors or not.

Here's the code

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

Hoping you can help

 

[OssieMac]

Hi again Deirdre,

I copied your code into a workbook and created 2 more workbooks; one with
links to the workbook with the code and another without links. I ran the code
successfully and changed to links from one workbook to another and then
changed them back again. Did this several times.

When I tried to introduce an error by changing a worksheet name, I did not
get an error. Instead I got a dialog box listing the Sheet names in the new
workbook and I had the option to select a worksheet for the links.

I cannot see how it goes into the errorLinks area unless there is an error
because the Exit Sub would prevent it. The only way it can go past the Exit
Sub is if the On Error routine directs it there. You could have a problem
with the actual line of code On Error Goto 0 and it is in fact creating an
error on that line without cancelling the previous On Error Goto errorLinks.
(I have had errors that I cannot see and assume they are non printable
characters and I have had to delete the line and reinsert.)

Try commenting out the On Error Goto errorLinks and see where the code stops
with the error. (Do this before change the line On Error Goto 0.)

Anyway below is the code I tested. I did change the line breaks in some
places so that it will go into this post without breaking code lines and that
makes it easier for you to copy. I also like to tabulate msgbox's with the
Chr(13) the way you will see I have done it so that it is easy to see where
the line feeds are.

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

 

[OssieMac]

Hi again Deirdre,

Something strange happened when I first posted this so here it is again.

I copied your code into a workbook and created 2 more workbooks; one with
links to the workbook with the code and another without links. I ran the code
successfully and changed to links from one workbook to another and then
changed them back again. Did this several times.

When I tried to introduce an error by changing a worksheet name, I did not
get an error. Instead I got a dialog box listing the Sheet names in the new
workbook and I had the option to select a worksheet for the links.

I cannot see how it goes into the errorLinks area unless there is an error
because the Exit Sub would prevent it. The only way it can go past the Exit
Sub is if the On Error routine directs it there. You could have a problem
with the actual line of code On Error Goto 0 and it is in fact creating an
error on that line without cancelling the previous On Error Goto errorLinks.
(I have had errors that I cannot see and assume they are non printable
characters and I have had to delete the line and reinsert.)

Try commenting out the On Error Goto errorLinks and see where the code stops
with the error. (Do this before change the line On Error Goto 0.)

Anyway below is the code I tested. I did change the line breaks in some
places so that it will go into this post without breaking code lines and that
makes it easier for you to copy. I also like to tabulate msgbox's with the
Chr(13) the way you will see I have done it so that it is easy to see where
the line feeds are.

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

 

[Diddy]

Hi OssieMac,

I copied your code and it worked beautifully!

Thank you so much for your patience

Diddy

 

[Peter]

Hi Diddy,

I came across the same problem when executing a macro which uses the
ChangeLink command.
Executing through the Excel menu ("Extra"| Macro | Macro's |Execute")
gave no error but calling the macro from the _click event of a command
button gave the error you mentioned.

After a while I noticed that using the menu also gave an error when the
command button had the focus but not when a worksheet cell had the
focus.

For me, using Range("A1").Select before calling the macro solved the
problem.



So:

Sub CommandButton1_Click()
Call Macro_with_ChangeLink
End Sub

Results in an error


And:

Sub CommandButton1_Click()
Range("A1").Select
Call Macro_with_ChangeLink
End Sub

No problem at all.


Regards, Peter

*** Sent via Developersdex http://www.developersdex.com ***

 

[Diddy]

Thanks for that Peter,

I've saved this with my snippets!

Cheers