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" <[email protected]> wrote in message
news:[email protected]...
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >
>
>
>

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

--
Regards,
Tom Ogilvy


"whylite" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >