Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
forcing refresh links
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
How can I 'Enable Automatic Refresh' for Query Refresh by default | Setting up and Configuration of Excel | |||
forcing UDF to run | Excel Worksheet Functions | |||
Query Refresh-Enable Automatic Refresh Dialogue Box | Excel Discussion (Misc queries) | |||
Manual Refresh of Links | Excel Discussion (Misc queries) |