forcing refresh links

S

Stefi

Hi All,

I have a link in WB A to WB B. When I change the source cell in WB B (it
contains a Boolean value) from FALSE to TRUE, link is refreshed in WB A. But
when I close WB B without saving (keeping the unchanged source value FALSE),
link in WB A is refreshed only when I reopen WB A. My question is that can I
somehow force refreshing link immediately when I close WB B without saving?

Thanks,
Stefi
 
D

Dave Peterson

You could always use:
Edit|links|Update values

But I bet you're looking for something more automatic.

Maybe you could have some application event that looks for a workbook that's
closing and then cycles through all the other workbooks and does that same
function.

I don't see anything that's built into excel that would do this automatically.
 
S

Stefi

„Dave Peterson†ezt írta:
You could always use:
Edit|links|Update values

But I bet you're looking for something more automatic.

Maybe you could have some application event that looks for a workbook that's
closing and then cycles through all the other workbooks and does that same
function.

I don't see anything that's built into excel that would do this automatically.
Click to expand...
 
S

Stefi

Hi Dave,

You won! Creating an event that keeps looking for closing workbooks seems a
bit complicated. But anyway, many thanks, because you put me in the right
direction:

Private Sub Workbook_Activate()
ActiveWorkbook.UpdateLink Name:= _
ActiveWorkbook.LinkSources, _
Type:=xlExcelLinks
End Sub

of WB A does the trick, because WB B must be the active workbook when I
close it, and having it closed WB A becomes the active workbook, either
automatically or clicking on it. It's satisfactory to have WB A refreshed
when it becomes active next time.

Regards,
Stefi


„Stefi†ezt írta:
 
S

Stefi

My solution is not complete! It works only if linked workbooks are ALL
closed! Any idea how to handle the case when some of them are just open?

Stefi


„Stefi†ezt írta:
 
S

Stefi

The final solution:

Private Sub Workbook_Activate()
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
' MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
backslpoz = InStrRev(aLinks(i), "\")
linkwbnev = Mid(aLinks(i), backslpoz + 1)
If Not IsOpenWB(linkwbnev) Then
ThisWorkbook.UpdateLink Name:=linkwbnev, Type:=xlExcelLinks
End If
Next i
End If
End Sub


Public Function IsOpenWB(ByVal munkafuzet As String) As Boolean
'returns true if workbook is open
Dim objWorkbook As Object
On Error Resume Next
IsOpenWB = False
Set objWorkbook = Workbooks(munkafuzet)
If Err = 0 Then IsOpenWB = True
End Function

Stefi


„Stefi†ezt írta:
 
D

Dave Peterson

How about something like this:

Option Explicit
Private Sub Workbook_Activate()
Dim myLinks As Variant
Dim iCtr As Long

myLinks = Me.LinkSources(xlExcelLinks)
If IsEmpty(myLinks) Then
'do nothing
Else
For iCtr = 1 To UBound(myLinks)
If IsWorkbookOpen(CStr(myLinks(iCtr))) = True Then
'skip it
Else
Me.UpdateLink Name:=myLinks(iCtr)
End If
Next iCtr
End If

End Sub
Function IsWorkbookOpen(myFileName As String) As Boolean

Dim JustFileName As String
Dim BackSlashPos As Long
Dim TestWkbk As Workbook

BackSlashPos = InStrRev(myFileName, "\")
JustFileName = Mid(myFileName, BackSlashPos + 1)

On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(JustFileName).Name) > 0)
On Error GoTo 0

End Function
 
S

Stefi

Thanks Dave for dealing with my problem that much! At first look this is
almost the same as I found myself in my previous post!

Regards,
Stefi


„Dave Peterson†ezt írta:
 
D

Dave Peterson

If I had waited a few minutes, your post would have shown up and I wouldn't have
essentially duplicated your solution.

Thanks Dave for dealing with my problem that much! At first look this is
almost the same as I found myself in my previous post!

Regards,
Stefi

„Dave Peterson†ezt írta:
Click to expand...
 
S

Stefi

Sorry for wasting your time but I couldn't be quicker. I wanted to thoroughly
test my solution before posting it. Thanks anyway!

Stefi


„Dave Peterson†ezt írta:
 
D

Dave Peterson

Not a problem.


Sorry for wasting your time but I couldn't be quicker. I wanted to thoroughly
test my solution before posting it. Thanks anyway!

Stefi

„Dave Peterson†ezt írta:
Click to expand...
 
D

Dave Peterson

In fact, if you look at a lot of posts, you'll see many of the answers
duplicated.

I'm just glad we matched!
 
Top