Help needed - Strange hyperlink problem

[Dennis]

I have a strange issue with creating hyperlinks on desktops but not on
lap tops.

We received some new laptops at work that run on Windows 7 OS and
Microsoft Office 2007. We also had some desktops that were UPGRADED from
Windoxs XP to Windows 7 and Microsoft Office 2007.

The issue is that when I create a hyperlink on the desktop and I use the
browse to drill down to the network folder where the file is kept, it
takes (no jokes) 2 to 3 minutes to open the folder directory. Since the
folder containing the files I need to hyperlink to is a few folders deep
it takes over 10 minutes to create a hyperlink. The problem is with
hyperlinks only. If I browse on File/Open... there is no delay drilling
down to the folder I need to get to.

If I create the hyperlink on the laptop, there is no delay in opening the
folders directories (2 - 3 seconds per folders).

My IT group is working on resolving the issue but in the meantime I am
trying to create a macro that would open the folder where the files are
kept without browsing and be able to select the file I want to link to.

I used the macro recorder to create a hyperlink but I am forced to select
a specific file (macro would always put a hyperlink to the same file), I
can't figure out how to be able to select a different file every time.

I'd appreciate any anyone could give me.

Dennis

 

[Clif McIrvin]

I have a strange issue with creating hyperlinks on desktops but not on
lap tops.

We received some new laptops at work that run on Windows 7 OS and
Microsoft Office 2007. We also had some desktops that were UPGRADED
from
Windoxs XP to Windows 7 and Microsoft Office 2007.

The issue is that when I create a hyperlink on the desktop and I use
the
browse to drill down to the network folder where the file is kept, it
takes (no jokes) 2 to 3 minutes to open the folder directory. Since
the
folder containing the files I need to hyperlink to is a few folders
deep
it takes over 10 minutes to create a hyperlink. The problem is with
hyperlinks only. If I browse on File/Open... there is no delay
drilling
down to the folder I need to get to.

If I create the hyperlink on the laptop, there is no delay in opening
the
folders directories (2 - 3 seconds per folders).

My IT group is working on resolving the issue but in the meantime I am
trying to create a macro that would open the folder where the files
are
kept without browsing and be able to select the file I want to link
to.

I used the macro recorder to create a hyperlink but I am forced to
select
a specific file (macro would always put a hyperlink to the same file),
I
can't figure out how to be able to select a different file every time.

I'd appreciate any anyone could give me.

Dennis

So you have some macro code that includes a line that looks something
like this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv", _
TextToDisplay:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv"

You have several options - if you want the macro to pop a dialog box so
the user can type in a filename you could try:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
InputBox("Enter filename to link to"), _
TextToDisplay:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv"

If you want the filename to be in a cell in the active worksheet you
could use the currently selected cell like this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
ActiveCell, _
TextToDisplay:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv"

Or you could point to a specific cell like this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
ActiveSheet.Range("A1"), _
TextToDisplay:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv"


or using a named range like this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
ActiveSheet.Range("NameOfNamedRange"), _
TextToDisplay:= _
"C:\Documents and Settings\Cliff\My Documents\Daily
Activity.csv"

HTH!

 

[Dennis]

Cliff, thanks for the help. It seems to work fine - not quite what I
was looking for but it works. I will have to test it on the network but
I do not see why it would not work.

I was hoping for the folder directory to open up so the user could pick
the file name from the list of file. Since the macro is pointing at
that folder and by-pass the browse it would have sped up the process.

Again, thanks for your help.

Dennis

 

[Clif McIrvin]

Cliff, thanks for the help. It seems to work fine - not quite what I
was looking for but it works. I will have to test it on the network
but
I do not see why it would not work.

I was hoping for the folder directory to open up so the user could
pick
the file name from the list of file. Since the macro is pointing at
that folder and by-pass the browse it would have sped up the process.

Again, thanks for your help.

Dennis

You're welcome! I was doing a lot of guessing as to what you were
looking for.

I have code that I use almost every day that includes a file picker
dialog -- for some time I've thought I should generalize that code ...
so I took the time to do that now.

I did minimal testing, the file picker dialog comes up as expected at
any rate.

There are two functions and some module level declarations -- you should
be able to add these to the same module that has your current code.

Enjoy:

----------- begin code --------
Option Explicit

' Sample Code by Clif McIrvin 2011
' Error handling is not included
' these constants are used by GetFileDialog to set up the
' File Picker Dialog object filter.
' Modify or extend as needed.
Const conFileTypeDescr As String = "All Files"
Const conFileTypeExt As String = "*.*"

' These variables are used by both GetFileDialog and fOpenFile
Private fileToOpen As Variant ' Workbook.Name
Private fileToOpenLong As Variant ' Workbook.FullName


Function GetFileDialog(Optional varFullName As Variant, _
Optional varShortName As Variant) As Boolean

' Sample Code by Clif McIrvin 2011
' Error handling is not included
' Show File Picker Dialog so user can select a filename
' Not fully tested - "generalized" from working code
' GetFileDialog returns True if user clicks "OK",
' returns False if user clicks "Cancel"
' fileToOpen is set to GetFileDialog return value
' fileToOpenLong is set to the file selected by the user

' varShortName is used as the filename match pattern, and may
' include the standard MS wildcard characters
' varFullName is the initial path for the file picker dialog,
' and may include the filename. varShortName (if supplied)
' is appended to varFullName
' if varShortName is supplied but varFullName is not the
' file picker dialog will apply the varShortName filename
' match pattern to the current directory
' if neither parameter is supplied the file picker dialog
' is called with the current value of fileToOpenLong. The
' initial default is all files in the current directory


' assure valid parameters
If fileToOpen = False Then
fileToOpen = Empty
End If
If VarType(varShortName) <> vbString Then
varShortName = ""
End If
If VarType(varFullName) = vbString Then
fileToOpen = True
fileToOpenLong = varFullName & "/" & varShortName
End If

' prompt for the destination workbook
' Create a FileDialog object as a File Picker dialog box.
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd

'Change the contents of the Files of Type list.
'Empty the list by clearing the FileDialogFilters collection.
.Filters.Clear
.Filters.Add conFileTypeDescr, conFileTypeExt
If IsEmpty(fileToOpen) Then
.InitialFileName = varShortName & "*"
Else
.InitialFileName = fileToOpenLong
End If

'Use the Show method to display the File Picker dialog box and
return the user's action.
If .Show = -1 Then
'If the user presses the action button...
fileToOpenLong = .SelectedItems(1)
fileToOpen = True
Else
'If the user presses Cancel...
fileToOpen = False
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

GetFileDialog = fileToOpen
End Function

Function fOpenFile(Optional varFullName As Variant) As Boolean

' Sample Code by Clif McIrvin 2011
' Error handling is not included
' Not fully tested - "generalized" from working code
' fOpenFile returns True if workbook is successfully
' activated, False if not
' fileToOpenLong (or varFullName) is assumed to be a valid
' workbook.FullName
' if varFullName is present it is used instead of fileToOpenLong

Dim varOpen As Variant
Dim w As Workbook

' varFullName takes precedence over fileToOpenLong
If VarType(varFullName) = vbString Then
fileToOpenLong = varFullName
End If

'open or activate requested workbook
varOpen = False
If VarType(fileToOpenLong) <> vbString Then
Exit Function
End If
' activate workbook if it is already open
For Each w In Workbooks
If w.FullName = fileToOpenLong Then
varOpen = True
fileToOpen = w.Name
Exit For
End If
Next w

If Not varOpen Then
' NO ERROR HANDLING INCLUDED HERE!!!!!!
Workbooks.Open FileName:=fileToOpenLong
fileToOpen = ActiveWorkbook.Name
Else
Workbooks(fileToOpen).Activate
End If

fOpenFile = varOpen

End Function

 

[Dennis]

Cliff, thanks again. I'll to play with this for a while to understand
how it links together with the default folder to open/browse.

Thanks,

Dennis

 

[Clif McIrvin]

Cliff, thanks again. I'll to play with this for a while to understand
how it links together with the default folder to open/browse.

You are most welcome. Feel free to come back with questions!