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" <whylite@discussions.microsoft.com> wrote in message
> news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> >
>
>
>

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

--
Regards,
Tom Ogilvy


"whylite" <whylite@discussions.microsoft.com> wrote in message
news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> 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" <whylite@discussions.microsoft.com> wrote in message
> > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > >
> >
> >
> >

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" <whylite@discussions.microsoft.com> wrote in message
> news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> > 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" <whylite@discussions.microsoft.com> wrote in message
> > > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > > >
> > >
> > >
> > >
>
>
>

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" <whylite@discussions.microsoft.com> wrote in message
news:6E7A8368-E884-4E6C-97BB-AB7874C6046A@microsoft.com...
> 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" <whylite@discussions.microsoft.com> wrote in message
> > news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <whylite@discussions.microsoft.com> wrote in message
> news:6E7A8368-E884-4E6C-97BB-AB7874C6046A@microsoft.com...
> > 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" <whylite@discussions.microsoft.com> wrote in message
> > > news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> > > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <whylite@discussions.microsoft.com> wrote in message
> > news:6E7A8368-E884-4E6C-97BB-AB7874C6046A@microsoft.com...
> > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> > > > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > > > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <whylite@discussions.microsoft.com> wrote in message
news:A4B87FD4-1D6E-450E-8FC4-1B209101272F@microsoft.com...
> 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" <whylite@discussions.microsoft.com> wrote in message
> > > news:6E7A8368-E884-4E6C-97BB-AB7874C6046A@microsoft.com...
> > > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > > news:2CDFD245-BDAA-43A6-BE2B-F4804A4572BC@microsoft.com...
> > > > > > 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" <whylite@discussions.microsoft.com> wrote in message
> > > > > > > news:71B4A8DD-BBCD-465E-A5F2-6CBDC6BBC4E4@microsoft.com...
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >