excel button not working

excel?

Board Regular
Joined
Sep 14, 2004
Messages
129
Office Version
  1. 365
Platform
  1. Windows
My spreadsheet has a button to allow the user to open a seperate spreadsheet in a different directory by just clicking on it.

The button works fine when opened from its original location, but when the file is emailed to other users it doesn't work. It looks like it is trying to open the 2nd file from C:\Documents and Settings folder of the user that sent the file out instead of its original location.

How can I get the button to work regardless of whether it is in its original location or has been emailed out.

Thanks for the assist.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
I'm really confused about what you're trying to do.

Without truly understanding that... I'd say you need to use "relative paths". From the sounds of it, you are using fully qualified paths.

In other words, if you are using "My Documents" and your username is "Jake", then the file path is acutally "C:\Documents and Settings\Jake\My Documents".

Does that explain what you are seeing?

-Tim
 

excel?

Board Regular
Joined
Sep 14, 2004
Messages
129
Office Version
  1. 365
Platform
  1. Windows
Yes, this is what is happening. I don't want it to change to "C:\Documents and Settings\Jake\My Documents", I want it to remain as "W:\File Location\File Name".

Basically, the button is a hyperlink that opens a different file. I believe your right about it being a "Relative Path". So what I need is for it to be an "Absolute Path" so when my spreadsheet is emailed to others, it will still open the different file.

I cannot figure out how to make this one an "absolute path" without causing another problem. I have a similar button (hyperlink?) on this sheet that takes me back to cell A1.

I tried to enter the path for the different file in the "Hyperlink Base" field located under the Menu Option: "File" / "Properties" - "Summary", but that messed up the button that takes me to cell A1.

Any idea what I can do so both items work?

Thanks
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Actually, I'm not sure Excel understands relative paths ( I just tried a quick test with "../t1.xls" and it wouldn't work ).

What's the directory structure you are working with?

Is the other file always in the user's "My Documents" folder?

How are you currently trying to open the file?
 

excel?

Board Regular
Joined
Sep 14, 2004
Messages
129
Office Version
  1. 365
Platform
  1. Windows
The main file (File A) is located in W:\Operations\Manufaturing\OPS\Weekly. While the file which is Hyperlinked (File B) is in W:\Operations\Manufaturing\STATS\2006\P10. Both are located on Network drives. I want the Hyperlink to File B to always point to this location, but when File A is emailed to others the directory changes to C:\Documents and Settings\Jake\Local Settings\STATS\2006\P10.

File A changes locations because it is emailed as an attachment and once attached then resides in the senders/receivers directory.
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Ok, you need to be able to change the hyperlink based on whether or not the file has been emailed. I'll leave that up to you.

However, take a look at this sample code. In particular, the function "FileLocation" ( yes, there are better ways to code this, *shrugs* ). You would call this function to create a string with the location of the file based on whether or not you want to use "Email Location" or original location.

You could put something like "call changehyperlink(sheet1.Range("A1"),filelocation(true,"test.xls"))" to change the link.

In the code below, everything above "GetLocalSettingsDirectory" was copied from the previous MSDN links.

Here's the code:
Code:
Public Declare Function SHGetSpecialFolderLocation _
    Lib "shell32" (ByVal hWnd As Long, _
    ByVal nFolder As Long, ppidl As Long) As Long

Public Declare Function SHGetPathFromIDList _
    Lib "shell32" Alias "SHGetPathFromIDListA" _
    (ByVal Pidl As Long, ByVal pszPath As String) As Long

Public Declare Sub CoTaskMemFree Lib "ole32" (ByVal pvoid As Long)

Public Const CSIDL_LOCAL_APPDATA = &H1C

Public Const MAX_PATH = 260
Public Const NOERROR = 0

Public Function SpecFolder(ByVal lngFolder As Long) As String
Dim lngPidlFound As Long
Dim lngFolderFound As Long
Dim lngPidl As Long
Dim strPath As String

strPath = Space(MAX_PATH)
lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl)
If lngPidlFound = NOERROR Then
    lngFolderFound = SHGetPathFromIDList(lngPidl, strPath)
    If lngFolderFound Then
        SpecFolder = Left$(strPath, _
            InStr(1, strPath, vbNullChar) - 1)
    End If
End If
CoTaskMemFree lngPidl
End Function

Function GetLocalSettingsDirectory() As String
'Closest thing is to get APPDATA and then strip off "Application Data"

Dim strLocalSettings As String
strLocalSettings = SpecFolder(CSIDL_LOCAL_APPDATA)
strLocalSettings = StrReverse(strLocalSettings)
strLocalSettings = Mid(strLocalSettings, InStr(strLocalSettings, "\"))
strLocalSettings = StrReverse(strLocalSettings)

GetLocalSettingsDirectory = strLocalSettings
End Function

Function FileLocation(ByVal IsEmailed As Boolean, ByVal strFileName As String) As String
Dim strConstantDirectory, strNonEmailedDirectory

strConstantDirectory = "STATS\2006\P10\"  'No leading "\", but insert the trailing "\"
strNonEmailedDirectory = "W:\Operations\Manufacturing\" & strConstantDirectory

If (IsEmailed) Then
    FileLocation = GetLocalSettingsDirectory & strConstantDirectory & strFileName
Else
    FileLocation = strNonEmailedDirectory & strFileName
End If

End Function

Sub ChangeHyperlink(ByVal rngRange As Range, ByVal strLink As String)
rngRange.Hyperlinks.Delete
rngRange.Hyperlinks.Add rngRange, strLink
End Sub

-Tim
 

Forum statistics

Threads
1,136,649
Messages
5,676,989
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top