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

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.

Stefi wrote:

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

--

Dave Peterson

€ž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.

Stefi wrote:

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

--

Dave Peterson

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson

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:

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:

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:



€ž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.

Stefi wrote:

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

--

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson

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:

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson

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


Stefi wrote:

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:

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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:

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


Stefi wrote:

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:

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Not a problem.



Stefi wrote:

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:

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


Stefi wrote:

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:

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

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!

Dave Peterson wrote:

Not a problem.

Stefi wrote:

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:

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


Stefi wrote:

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:

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




Stefi wrote:

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:

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:



€ž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.

Stefi wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson