Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Following Hyperlink by VBA and Reset Status Bar

Posted on 2016-09-01
11
Medium Priority
?
80 Views
Last Modified: 2016-09-02
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

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

Open in new window

0
Comment
Question by:Rob Henson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 18

Accepted Solution

by:
xtermie earned 2000 total points
ID: 41779690
Hey Rob, for question 2 try
...
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar   'restores the default text
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41779723
Rob, If I understand question 1 correctly, you can follow the hyperlink by using the Hyperlinks(I).Follow

    If cell.Value <> "" Then
        cell.Select
        cell.Hyperlinks(1).Follow
But then
1) You might get a warning message before opening the link
2) You would have to have a second application object in order to do your save in the other app

https://msdn.microsoft.com/en-us/library/office/aa195735(v=office.11).aspx
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41779813
Hi Extermie,

Doesn't seem to work for StatusBar, it disappears altogether.

Hyperlinks(1).Follow gives "Run-time error 9: Subscript out of range".

Looking at it, I think I will stick with creating the file path and name for the Workbooks.Open statement, it works perfectly well. I also then have the option to dictate that the source also updates its links, whereas opening by hyperlink gives the Update Links dialogue. I will put the hardcoded directory path part in a reference cell and refer to it in the hyperlink formulas and the vba script.

Thanks
Rob
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Author Comment

by:Rob Henson
ID: 41779842
Sorted the StatusBar issue with the following:

Application.DisplayStatusBar = True
oldStatusBar = Application.StatusBar

....code lines....

Application.StatusBar = oldStatusBar

Open in new window

Thanks for the help.
1
 
LVL 18

Expert Comment

by:xtermie
ID: 41779943
Hi Rob, maybe it is not Hyperlinks(1) in your case...hmmm...weird, it run for me.
There may be a way to get the address after all
Could you humor me and try:
....
Dim cell As Range
Dim Filename2 As String
For Each cell In Range("Links")
    If cell.Value <> "" Then
        cell.Select
        x = cell.Hyperlinks.Count
        'cell.Hyperlinks(x).Follow
        Filename2 = cell.Hyperlinks(x).Address
       ' Filename = "\\[i]hard coded path[/i]" & cell.Value & ".xlsm"
        Workbooks.Open Filename:=Filename2, 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
...

Open in new window


Given that each cell has a single hyperlink this should work just fine
Also please note that concatenating the Cell.Value to the file path and extension may be dangerous if someone has set a different text to be displayed in the text.

Hope this works!!
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41779999
For experimenting I shortened the code to just:
Sub FollowLinks2()
    For Each cell In Selection
    If cell.Value <> "" Then
        cell.Select
        x = cell.Hyperlinks.Count
        Filename2 = cell.Hyperlinks(x).Address
        Workbooks.Open Filename:=Filename2, UpdateLinks:=3
        ActiveWindow.Close SaveChanges:=True
    Else
    End If
    Next cell
End Sub

Open in new window


That gives the same error. I put a Stop command after line 5 so that the script would stop rather than fail and it shows the value of x as zero.

When I right click on the cells it does not show the Edit Hyperlink options. I wonder if being a formula it doesn't recognise it as being a true hyperlink eventhough it works as one when clicked; bizarre.

I did wonder if it was because the cells also have a Comment "Click to Open file" rather than the usual tool tip that comes up with a hyperlink showing "the address, click once to follow, click and hold to select cell". I removed the comment from one of the cells and it still gave x as zero.

Noted, the comment regarding changing the hyperlink text to something other than Project name will make it fail. This file will only be used by 2/3 people and they are all reasonably Excel savvy and will only change things that they know they can.
1
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41780263
Try this to clear the status bar

Application.StatusBar = ""

Open in new window


FollowHyperlink

ActiveWorkbook.FollowHyperlink Address:=Range("A1").Value

Open in new window

0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41780300
Hi Roy

Got it sorted now.

Don't think your hyperlink suggestion would work because the cell value is just the project name, not the full link address.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41781032
I thought you were creating the hyperlink with a formula.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41781115
Hi Rob, since it is not an actual Hyperlink my code wont work, you are right (I thought it was).
If the cell contains a web address but not as a hyperlink, you can try
ActiveWorkbook.FollowHyperlink    ActiveCell.Value

Open in new window


So if the above does not work, you are probably better off creating the full path as you do.

Unless we try to compose a macro that actually clicks your cell like what is suggested here
http://excelhelphq.com/how-to-move-and-click-the-mouse-in-vba/
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 41781152
The cell contains a formula to create a hyperlink but the cell displays the Project name rather than the actual file path so "ActiveCell.value" does not give the address.

The link is to another excel file on the network.

I will be staying with creating the file path as originally done, just thought there might be a better way but I guess not.

Thanks
Rob
1

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question