excel button not working

[excel?]

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.

 

[tmcfadden]

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?]

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]

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?

 

[tmcfadden]

If you just need to figure out the different user/system folders, check out these two entries at MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0187.asp

http://msdn.microsoft.com/library/d...llcc/platform/shell/reference/enums/csidl.asp

-Tim

 

[excel?]

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]

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:

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