How do I retrieve the current workbook link in vba?
 

How do I retrieve the current workbook link in vba?
 

If you could be bothered to elaborate maybe someone will bother to answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

I have a two workbooks. The one is a source workbook. These workbooks are
shared with many coworkers. Often the source workbook is renamed and then
saved leaving the current link severed. What I want to do is write a program
so that at the touch of a command button the link can be restored. Rather
than train everyone how to restore links I would like to have it executed in
vba. Is there a way to make the current workbook link a string in vba? Just
like you can a workbook name (string = activeworkbook.name). If so I can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

See Excel VBA help on the ChangeLink command. It does accept a string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These workbooks
are
shared with many coworkers. Often the source workbook is renamed and then
saved leaving the current link severed. What I want to do is write a
program
so that at the touch of a command button the link can be restored. Rather
than train everyone how to restore links I would like to have it executed
in
vba. Is there a way to make the current workbook link a string in vba?
Just
like you can a workbook name (string = activeworkbook.name). If so I can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

This is what I have for a code. The aLink is still returning empty yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, , iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These workbooks
are
shared with many coworkers. Often the source workbook is renamed and then
saved leaving the current link severed. What I want to do is write a
program
so that at the touch of a command button the link can be restored. Rather
than train everyone how to restore links I would like to have it executed
in
vba. Is there a way to make the current workbook link a string in vba?
Just
like you can a workbook name (string = activeworkbook.name). If so I can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

This part of the code worked for me even after I had renamed one of the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is renamed and
then
saved leaving the current link severed. What I want to do is write a
program
so that at the touch of a command button the link can be restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

See I have only one link for it to look for and it keeps returning blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is renamed and
then
saved leaving the current link severed. What I want to do is write a
program
so that at the touch of a command button the link can be restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

As you can see from the code I have writen below the changelink fails because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is renamed and
then
saved leaving the current link severed. What I want to do is write a
program
so that at the touch of a command button the link can be restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

With only one link and to a file no longer existent (had been renamed), it
worked fine for me. alink was an array with a single element.

--
Regards,
Tom Ogilvy

"whylite" wrote in message
...
As you can see from the code I have writen below the changelink fails
because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning
blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of
the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to
work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty
yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a
string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is
renamed and
then
saved leaving the current link severed. What I want to do is
write a
program
so that at the touch of a command button the link can be
restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string
in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If
so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will
bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

I just opened a new book. I linked one cell to another book. I then added
this macro and ran it to see what msgbox aLink would return and I got a
runtime error. I have no idea how it works for you and I can't get it to
work for me. I need to have the path for the current link so I can change it
in vba. All I am getting is errors. Thank you for your help. I do
appreciate your input. I know I am close to a solution.



"Tom Ogilvy" wrote:

With only one link and to a file no longer existent (had been renamed), it
worked fine for me. alink was an array with a single element.

--
Regards,
Tom Ogilvy

"whylite" wrote in message
...
As you can see from the code I have writen below the changelink fails
because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning
blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of
the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to
work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty
yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a
string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is
renamed and
then
saved leaving the current link severed. What I want to do is
write a
program
so that at the touch of a command button the link can be
restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string
in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If
so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will
bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

I just opened a book and added a formula to one cell linked to another book.
I then added this macro and msgbox alink to see what it would return and I
got a runtime error. What I really need is to be able to see the path for
the current link so I can change it to the new on. Thank you for your help.
I know I am close to a solution.

"Tom Ogilvy" wrote:

With only one link and to a file no longer existent (had been renamed), it
worked fine for me. alink was an array with a single element.

--
Regards,
Tom Ogilvy

"whylite" wrote in message
...
As you can see from the code I have writen below the changelink fails
because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning
blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of
the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to
work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty
yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a
string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is
renamed and
then
saved leaving the current link severed. What I want to do is
write a
program
so that at the touch of a command button the link can be
restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string
in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If
so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will
bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

Ok I got it. Now that I have the link string I am getting an error on the
change link. Thanks again.

Sub Restorelinks()
Dim savefilename As String
Dim PATH, link As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, , iTitle)

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select
Worksheets("data").Range("d1").Select

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
link = Chr(13) & aLinks(i)
Next i
End If


ActiveWorkbook.ChangeLink link, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub

"whylite" wrote:

I just opened a book and added a formula to one cell linked to another book.
I then added this macro and msgbox alink to see what it would return and I
got a runtime error. What I really need is to be able to see the path for
the current link so I can change it to the new on. Thank you for your help.
I know I am close to a solution.

"Tom Ogilvy" wrote:

With only one link and to a file no longer existent (had been renamed), it
worked fine for me. alink was an array with a single element.

--
Regards,
Tom Ogilvy

"whylite" wrote in message
...
As you can see from the code I have writen below the changelink fails
because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning
blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of
the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to
work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty
yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a
string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is
renamed and
then
saved leaving the current link severed. What I want to do is
write a
program
so that at the touch of a command button the link can be
restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string
in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If
so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will
bother to
answer...

Tim

"whylite" wrote in message
...

How do I retrieve the current workbook link in vba?
 

if you remove the Chr(13) from [link = Chr(13) & aLinks(i)] the macro works
perfectly.

--
Thanks!
Shane W


"whylite" wrote:

Ok I got it. Now that I have the link string I am getting an error on the
change link. Thanks again.

Sub Restorelinks()
Dim savefilename As String
Dim PATH, link As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, , iTitle)

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select
Worksheets("data").Range("d1").Select

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
link = Chr(13) & aLinks(i)
Next i
End If


ActiveWorkbook.ChangeLink link, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub

"whylite" wrote:

I just opened a book and added a formula to one cell linked to another book.
I then added this macro and msgbox alink to see what it would return and I
got a runtime error. What I really need is to be able to see the path for
the current link so I can change it to the new on. Thank you for your help.
I know I am close to a solution.

"Tom Ogilvy" wrote:

With only one link and to a file no longer existent (had been renamed), it
worked fine for me. alink was an array with a single element.

--
Regards,
Tom Ogilvy

"whylite" wrote in message
...
As you can see from the code I have writen below the changelink fails
because
aLink is blank. Maybe I need to reboot and try again. I am lost.


"whylite" wrote:

See I have only one link for it to look for and it keeps returning
blank. I
am running 2002.

"Tom Ogilvy" wrote:

This part of the code worked for me even after I had renamed one of
the
source (linked to) workbooks.

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If


If you go to Edit=Links and it shows links, I would expect it to
work.

--
Regards,
Tom Ogilvy



"whylite" wrote in message
...
This is what I have for a code. The aLink is still returning empty
yet my
data sheet is linked to paperwork414444.xls.


Sub Restorelinks()
Dim savefilename As String
Dim PATH As String
Set oldactive = ActiveWorkbook

Application.ScreenUpdating = False

'On Error GoTo ErrorHandler
Worksheets("data").Visible = True
Worksheets("data").Select

aLink = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

Const iTitle = "Save Data File" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, ,
iTitle)

ActiveWorkbook.ChangeLink aLinks, PATH, xlExcelLinks

Worksheets("data").Visible = False
oldactive.Select
Application.ScreenUpdating = True

ErrorHandler:
If Err.Number < 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub


"Tom Ogilvy" wrote:

See Excel VBA help on the ChangeLink command. It does accept a
string.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I have a two workbooks. The one is a source workbook. These
workbooks
are
shared with many coworkers. Often the source workbook is
renamed and
then
saved leaving the current link severed. What I want to do is
write a
program
so that at the touch of a command button the link can be
restored.
Rather
than train everyone how to restore links I would like to have it
executed
in
vba. Is there a way to make the current workbook link a string
in
vba?
Just
like you can a workbook name (string = activeworkbook.name). If
so I
can
then finish writing my program.

"Tim Williams" wrote:

If you could be bothered to elaborate maybe someone will
bother to
answer...

Tim

"whylite" wrote in message
...