Exclude Transfer Path in Output

[trsisko]

I have an Array witch Outputs a Transfer Path for a File. Problem Is I don't actually need the Full Path

<TABLE style="WIDTH: 320pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=426 border=0 x:str><COLGROUP><COL style="WIDTH: 320pt; mso-width-source: userset; mso-width-alt: 15579" width=426><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 320pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=426 height=17>D:\Import.xls

I just need the Import.Xls Exported to the worksheet. Anyway I can get Excel to Ignore the Path/Directory and just keep the Filename?


</TD></TR></TBODY></TABLE>

Option Explicit
Public Tran_Path As String
 
Private Sub cmdTransfer_Click()
Worksheets("Scheme").Range("C11").Value = Tran_Path
  
Dim fname As String
  
  fname = FileNameOnly(Me.txtImageFolder)
  fname = Me.txtTransferFolder & "\" & fname
  On Error GoTo FileCopyError
  FileCopy Me.txtImageFolder, fname
  MsgBox ("Logo Transferred")
  SetDefaultTransfer
  Unload Me
  Exit Sub

 

[Andrew Poulsom]

In your snippet of code you are using the function FileNameOnly. Can't you use that on Tran_Path?

 

[jimrward]

if the file exists use the DIR function on the full filename including path to strip it to just the filename

 

[trsisko]

Resolved. Highlighted the Code which I used Below.

Option Explicit
Public Import_Notice As String
 

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub CmdCancel_Click()
Unload Me
End Sub
Private Sub cmdLocate_Click()
  Me.txtTransferFolder = ""
  Me.txtImageFolder = Application.GetOpenFileName
  If Me.txtImageFolder = False Then
  Me.cmdTransferFolder.Enabled = True
  SetDefaultTransfer
  Else
  Me.txtTransferFolder = ""
  Me.cmdTransferFolder.Enabled = True
  Me.txtTransferFolder.Enabled = True
  Me.cmdStartOver.Enabled = True
  
  End If
End Sub

Private Sub cmdStartOver_Click()
  SetDefaultTransfer
End Sub
Private Sub cmdTransfer_Click()
Worksheets("Scheme").Range("C12").Value = Import_Notice
  Dim fname As String
  
  fname = FileNameOnly(Me.txtImageFolder)
  fname = Me.txtTransferFolder & "\" & fname
  On Error GoTo FileCopyError
  FileCopy Me.txtImageFolder, fname
  MsgBox ("Covering Letter Transferred")
  SetDefaultTransfer
  Unload Me
  Exit Sub
  
FileCopyError:
  MsgBox ("This logo did not transfer. Please retry")
  
End Sub
Private Function FileNameOnly(pname) As String
'   Returns the filename from a path/filename string
    Dim I As Integer, length As Integer, temp As String
    length = Len(pname)
    temp = ""
    For I = length To 1 Step -1
        If Mid(pname, I, 1) = Application.PathSeparator Then
            FileNameOnly = temp
            Exit Function
        End If
        temp = Mid(pname, I, 1) & temp
    Next I
    FileNameOnly = pname
End Function
Private Sub cmdTransferFolder_Click()
  Me.txtTransferFolder = BrowseForDirectory
  If Me.txtTransferFolder = "" Then
    MsgBox ("This is not a folder.  Be sure to choose a folder.")
    Me.txtTransferFolder = ""
    Me.cmdTransferFolder.SetFocus
  End If
  
  Me.cmdTransfer.Enabled = True
  Me.cmdTransfer.SetFocus
End Sub
 
Private Sub txtImageFolder_Change()
[B]Import_Notice = FileNameOnly(Me.txtImageFolder)[/B]
End Sub
Private Sub UserForm_Initialize()
  SetDefaultTransfer
End Sub
Private Sub SetDefaultTransfer()
  'set the buttons and text boxes in default format
  Me.txtImageFolder = ""
  Me.cmdStartOver.Enabled = False
  Me.cmdLocate.SetFocus
End Sub