Keep GetOpenFilename in memory while adding a link and performing a copy/paste to SharePoint with appended filename

[arisrune]

Hello folks,

This should be an interesting topic. I did research and managed to create a series of Subs that Upon double-click in a column cell

• Prompted Application.GetOpenFilename (opening a file explorer window)
• Applied full file-path in the cell as a hyperlink
• Calls a second sub that references 3 cells in the same row that parse the filename & location into something new
• Performs a file-copy using those references to paste the renamed version into a SharePoint site via the modified link native to file explorer.

What I would like to do is clean up my act and keep those references in memory instead of having to create them in excel cells for referencing. I have code for what works and also further below a test-bed that works out the types of formulas I would use.

Below is the code that works currently with referenced cells:

[SIZE=1]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)[/SIZE]
[SIZE=1] If Not Application.Intersect(Target, Range("Q4:Q1003")) Is Nothing Then[/SIZE]
[SIZE=1]Dim x As Long[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]     myfilepath = Application.GetOpenFilename()[/SIZE]
[SIZE=1]        [/SIZE]
[SIZE=1]    'if user cancels . . .[/SIZE]
[SIZE=1]    If myfilepath = False Then[/SIZE]
[SIZE=1]        Target = ""[/SIZE]
[SIZE=1]        [/SIZE]
[SIZE=1]    Else[/SIZE]
[SIZE=1]      ActiveSheet.Unprotect Password:=myPassword[/SIZE]
[SIZE=1]        Target = myfilepath[/SIZE]
[SIZE=1]    'Takes target and makes a usable Hyperlink to file[/SIZE]
[SIZE=1]        Target.Hyperlinks.Add Anchor:=Target, Address:=Target[/SIZE]
[SIZE=1]    [/SIZE]
[SIZE=1]      Call CopyRenameFile[/SIZE]
[SIZE=1]       ActiveSheet.Protect Password:=myPassword[/SIZE]
[SIZE=1]        [/SIZE]
[SIZE=1]        End If[/SIZE]
[SIZE=1]cancel = True[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]End If
End Sub


[/SIZE][SIZE=1]Private Sub CopyRenameFile()[/SIZE]
[SIZE=1]Dim src As String, dst As String, fl As String[/SIZE]
[SIZE=1]Dim rfl As String[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]'Source directory[/SIZE]
[SIZE=1]src = ActiveCell.Offset(0, 9)[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]'Destination directory[/SIZE]
[SIZE=1]dst = [/SIZE]"\\Random.net@SSL\DavWWWRoot\sites\"
[SIZE=1]
[/SIZE]
[SIZE=1]'File name[/SIZE]
[SIZE=1]fl = ActiveCell.Offset(0, 7)[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]'Renamed file[/SIZE]
[SIZE=1]rfl = ActiveCell.Offset(0, 8)[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]On Error Resume Next[/SIZE]
[SIZE=1]    FileCopy src & "\" & fl, dst & "\" & rfl[/SIZE]
[SIZE=1]    If Err.Number <> 0 Then[/SIZE]
[SIZE=1]        MsgBox "Copy: " & src & "\" & rfl[/SIZE]
[SIZE=1]    End If[/SIZE]
[SIZE=1]On Error GoTo 0[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]End Sub
[/SIZE]

Here is my test-bed to make sure it outputs what I expect in memory by using MsgBox reporting... but for some reason when i try to implement the elements from it in the version above, it fails to retain and apply the modified filename and errors out. FRUSTRATING>>>

[SIZE=1]Sub testMyfilepath()[/SIZE]
[SIZE=1]Dim myfilepath As String[/SIZE]
[SIZE=1]Dim myfilenm As String[/SIZE]
[SIZE=1]Dim ext As String[/SIZE]
[SIZE=1]Dim justfile As String[/SIZE]
[SIZE=1]Dim newpath As String[/SIZE]
[SIZE=1]
[/SIZE]
[SIZE=1]     myfilepth = Application.GetOpenFilename()[/SIZE]
[SIZE=1]     myfilenm = Mid(myfilepath, InStrRev(myfilepath, "\") + 1)[/SIZE]
[SIZE=1]     JustExt = Mid(myfilepath, InStrRev(myfilepath, ".") + 1)[/SIZE]
[SIZE=1]     justfile = Left(myfilenm, InStrRev(myfilenm, "."))[/SIZE]
[SIZE=1]     newpath = Range("A" & ActiveCell.Row).Value & "_" & "eP_" & justfile & JustExt

MsgBox myfilepath & vbNewLine & myfilenm & vbNewLine & JustExt & vbNewLine & justfile & vbNewLine & newpath
End Sub[/SIZE]
[SIZE=1][/SIZE]