Links

[gwhenning]

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]

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:=xlLinkTypeExcelLinks)

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

 

[David M]

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

 

[Dave Peterson]

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.