asked on
Following Hyperlink by VBA and Reset Status Bar
Hi All,
I have been working on a Consolidation model for reporting progress on a number (70+) of projects. Each Project file works fine and when I update the Links in the Consolidation file all of the numerical links update fine.
In each project file there is an option for the Project Manager to make comments on variances and these comments are concatenated into a single cell using a User Defined Function. In each of the files this works fine but when I update links in the consolidation file the text values from the concatenated cell don't always feed through and I end with an error value in the relevant row for that particular project. I have got round this by opening and closing the source file.
Rather than doing each one manually I am writing a routine to go through ALL of the file links and open and close the source file.
As a feature on the file I have a cell on each project row with the following formula to create a hyperlink to the source file:
=HYPERLINK("Hard coded directory path"&B17&".xlsm",B17) where B17 is the Project Name. The cell displays only the file name and clicking on each individual hyperlink cell works fine.
The column containing these hyperlinks has a named range (Links)
I recorded a macro clicking on a hyperlink hoping that it would give me the syntax in VBA for following a hyperlink. Unfortunately it just recorded the WorkbooksOpen statement with the file name from the hyperlink.
I have got round this by effectively replacing the file name with the same hard coded file path from the hyperlink formula above and inserting the variable file name. The whole code is at the end of the question.
So...
Question 1: Can I do this by referencing the hyperlink within the cell? When this consolidation file rolls over next fiscal year, the path of the source files will change and it would be better if I (or my successor) just has to change the hyperlink formula path.
Question 2: At the start of the routine I am switching off ScreenUpdating and providing a progress in the StatusBar (line 29 of the code). This works fine; I don't see the files open, I occasionally see the Save progress bar appear but only briefly. However, the issue is with the StatusBar. How do I reset the StatusBar at the end of the routine so that it appears like normal, lines 33 and 34 don't do the trick; they leave the Statusbar just showing TRUE.
Many thanks for your assistance.
Rob H
I have been working on a Consolidation model for reporting progress on a number (70+) of projects. Each Project file works fine and when I update the Links in the Consolidation file all of the numerical links update fine.
In each project file there is an option for the Project Manager to make comments on variances and these comments are concatenated into a single cell using a User Defined Function. In each of the files this works fine but when I update links in the consolidation file the text values from the concatenated cell don't always feed through and I end with an error value in the relevant row for that particular project. I have got round this by opening and closing the source file.
Rather than doing each one manually I am writing a routine to go through ALL of the file links and open and close the source file.
As a feature on the file I have a cell on each project row with the following formula to create a hyperlink to the source file:
=HYPERLINK("Hard coded directory path"&B17&".xlsm",B17) where B17 is the Project Name. The cell displays only the file name and clicking on each individual hyperlink cell works fine.
The column containing these hyperlinks has a named range (Links)
I recorded a macro clicking on a hyperlink hoping that it would give me the syntax in VBA for following a hyperlink. Unfortunately it just recorded the WorkbooksOpen statement with the file name from the hyperlink.
I have got round this by effectively replacing the file name with the same hard coded file path from the hyperlink formula above and inserting the variable file name. The whole code is at the end of the question.
So...
Question 1: Can I do this by referencing the hyperlink within the cell? When this consolidation file rolls over next fiscal year, the path of the source files will change and it would be better if I (or my successor) just has to change the hyperlink formula path.
Question 2: At the start of the routine I am switching off ScreenUpdating and providing a progress in the StatusBar (line 29 of the code). This works fine; I don't see the files open, I occasionally see the Save progress bar appear but only briefly. However, the issue is with the StatusBar. How do I reset the StatusBar at the end of the routine so that it appears like normal, lines 33 and 34 don't do the trick; they leave the Statusbar just showing TRUE.
Many thanks for your assistance.
Rob H
Sub FollowLinks()
Application.ScreenUpdating = False
Sheets("Current").Select
Count = 0
For Each cell In Range("Links")
If cell.Value <> "" Then Count = Count + 1
P = ""
Warn = ""
If Count > 1 Then P = "s"
If Count > 10 Then Warn = "This will take a few minutes."
Next cell
Confirmation = MsgBox(Count & " file link" & P & " will be updated." & Chr(10) _
& "The screen will not change while updating progresses." & Chr(10) _
& "Progress can be seen on the Status bar." & Chr(10) _
& Warn, vbOKCancel, "Confirm")
If Confirmation = vbCancel Then Exit Sub
Count = 0
For Each cell In Range("Links")
If cell.Value <> "" Then
Filename = "\\[i]hard coded path[/i]" & cell.Value & ".xlsm"
Workbooks.Open Filename:=Filename, UpdateLinks:=3
ActiveWindow.Close SaveChanges:=True
Count = Count + 1
P = ""
If Count > 1 Then P = "s"
Application.StatusBar = Count & " file link" & P & " updated."
Else
End If
Next cell
Application.StatusBar = False
Application.StatusBar = True
Application.ScreenUpdating = True
Confirmation = MsgBox(Count & " file link" & P & " updated.", vbOKOnly, "Confirmation")
End Sub
VBAMicrosoft Excel
Last Comment
Rob Henson