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