I have a series of weekly financial reports that are always named like this
"Weekly report Week "&<WeekNo.&".xls" I've written a macro on a summary
document that prompts the user to select the week they want to summarize and
change the link to reference the new week. I change the link using the
ActiveWorkbook.ChangeLink Name:="X:\Weekly report Week 01.xls",
NewName:=NewLinkName, Type:=xlExcelLinks method. The problem is that when the
user opens the .xlt file and selects a week everything works normally but if
they change the week and then change it again the code breaks because there
is now no link named "X:\Weekly report Week 01.xls" How can you get a list of
the links in an excel file without using an add-on?

It looks like you could cycle through all the links looking for "weekly report"
(or even more??).

I stole the code from VBA's help and modified it to go through all the links:

Option Explicit
Sub testme02()

Dim astrLinks As Variant
Dim iCtr As Long

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLi nks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
If InStr(1, astrLinks(iCtr), "weekly report", _
vbTextCompare) 0 Then
'do the change
End If
Next iCtr
End If

End Sub



gwhenning wrote:

I have a series of weekly financial reports that are always named like this
"Weekly report Week "&<WeekNo.&".xls" I've written a macro on a summary
document that prompts the user to select the week they want to summarize and
change the link to reference the new week. I change the link using the
ActiveWorkbook.ChangeLink Name:="X:\Weekly report Week 01.xls",
NewName:=NewLinkName, Type:=xlExcelLinks method. The problem is that when the
user opens the .xlt file and selects a week everything works normally but if
they change the week and then change it again the code breaks because there
is now no link named "X:\Weekly report Week 01.xls" How can you get a list of
the links in an excel file without using an add-on?

--

Dave Peterson

Hello, i have a simlar querie,

i have two spreadsheets (spreadsheet1 + spreadsheet2) holdin
identical information but the two spreadsheets sort the informatio
into different workbooks depending on two different dates. i would lik
a vb that means i only have to put the information into spreadsheet1 an
it to put itself inthe corect workbook in spreadsheet2. is thi
possible?

in spread sheet 1 the information is put in order of the date w
recieve the file

in spread sheet 2 the information is put in order of the date we finis
with the file.

we have a workbook for each month. i need the info in spread sheet 1 t
put itself in the right month (workbook) in spreadsheet 2,

hope you can understand what i mean.

thank you

David

--
David
-----------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...fo&userid=2655
View this thread: http://www.excelforum.com/showthread.php?threadid=40055

Putting the data in two spots is usually a problem--always a problem for me.

I'd put the data in one location and use Data|Sort or Data|Filter|autofilter to
see different "views" of the data.



David M wrote:

Hello, i have a simlar querie,

i have two spreadsheets (spreadsheet1 + spreadsheet2) holding
identical information but the two spreadsheets sort the information
into different workbooks depending on two different dates. i would like
a vb that means i only have to put the information into spreadsheet1 and
it to put itself inthe corect workbook in spreadsheet2. is this
possible?

in spread sheet 1 the information is put in order of the date we
recieve the file

in spread sheet 2 the information is put in order of the date we finish
with the file.

we have a workbook for each month. i need the info in spread sheet 1 to
put itself in the right month (workbook) in spreadsheet 2,

hope you can understand what i mean.

thank you

David,

--
David M
------------------------------------------------------------------------
David M's Profile: http://www.excelforum.com/member.php...o&userid=26556
View this thread: http://www.excelforum.com/showthread...hreadid=400557

--

Dave Peterson