application.getopenfilename

[ccordner]

Good morning

I want to be able to add a hyperlink to a document in a spreadsheet, but by pressing a button so the user can browse for a file. I've been successfully using the code below for some time to retrieve the UNC path (so it works on any computer).

' 32-bit Function version.' Enter this declaration on a single line.
Private Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias _
    "WNetGetConnectionA" (ByVal lpszLocalName As String, ByVal _
    lpszRemoteName As String, lSize As Long) As Long
    
' 32-bit declarations:
Dim lpszRemoteName As String
Dim lSize As Long


' Use for the return value of WNetGetConnection() API.
Const NO_ERROR As Long = 0


' The size used for the string buffer. Adjust this if you
' need a larger buffer.
Const lBUFFER_SIZE As Long = 255


Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim NewFileName As String




Private Sub cmdBrowse_Click()


Filter = "View All Files (*.*),*.*,"
FilterIndex = 3
Title = "Select a File to Open"


With Application
    FileName = .GetOpenFilename(Filter, FilterIndex, Title)
End With


If FileName = False Then
    Exit Sub
    Else
    
    ' Trim the filename to find the mapped drive letter.
    DriveLetter = Left(FileName, 1) & ":"


    ' Specifies the size in characters of the buffer.
    cbRemoteName = lBUFFER_SIZE


    ' Prepare a string variable by padding spaces.
    lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)


    ' Return the UNC path (\\Server\Share).
    lstatus& = WNetGetConnection32(DriveLetter, lpszRemoteName, _
    cbRemoteName)


    ' Verify that the WNetGetConnection() succeeded. WNetGetConnection()
    ' returns 0 (NO_ERROR) if it successfully retrieves the UNC path.
    If lstatus& = NO_ERROR Then
    
        'Add UNC path to beginning of FileName
        NewFileName = Left(Trim(lpszRemoteName), (Len(Trim(lpszRemoteName)) - 1)) & "\" & Right(FileName, (Len(FileName) - 3))


        Else
        
        MsgBox ("An error has occurred with" & Chr(10) & lstatus& & Chr(10) & "This device will self-destruct in thirty seconds")
        Exit Sub


    End If


End If


End Sub

On one of the files however, I though it would save time if I could start them off in the right directory - again, I wanted to make it so it would work on any computer and managed to find the code below:

Private Declare Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long

Private Sub SetUNCPath(sPath As String)
    Dim lReturn As Long
    lReturn = SetCurrentDirectoryA(sPath)
    If lReturn = 0 Then _
    MsgBox "Error setting path"
End Sub


Call SetUNCPath("\\myserver\myfolder\myfile")

This works perfectly for setting the default path, but then when I try to actually add the code, it keeps failing with error code 1200. I tried deleting the new code, but it still fails.

Any idea why?

Thanks
Chris