Thread: Using Excel userForm to upload a document into a library

Hi all,

I'm looking for a way for a userForm to allow a user to browse to a file and then have excel save the selected file to a particular path.

I've puzzled out the following so far:

Sub getAndSaveFile()

Dim userFilePath As String
Dim libraryFilePath As String
Dim typeSelect As Integer

userFilePath = Application.GetOpenFilename

If typeSelect = 1 Then
    libraryFilePath = "H:\TEMP\Lib1\"
ElseIf typeSelect = 2 Then
    libraryFilePath = "H:\TEMP\Lib2\"
ElseIf typeSelect = 3 Then
    libraryFilePath = "H:\TEMP\Lib3\"
Else
    libraryFilePath = "H:\TEMP\"
End If

'insert code to save the selected file to the selected file path
FileCopy userFilePath, libraryFilePath

end sub

My problem is thus:

The Application.GetOpenFilename method gets the full file path of the selected file, which is great for the FileCopy userFilePath variable.

But how do I truncate the filePath string to include only the file name so I can put it in the expression for the copy destination, i.e.:

FileCopy userFilePath, libraryFilePath & fileName

Must I directly modify the string to remove everything (and including) the right-most "/" character in the userFilePath string, or is there a way to modify the Application.GetOpenFilename method to return just the selected file name and not the full file path?

Thanks!
-o

Hi Ouka,

Read http://www.ozgrid.com/VBA/GetExcelFileNameFromPath.htm

Hello Ouka,

here is a much easier way using the Dir Statement.

Sub getAndSaveFile()

  Dim FileName As String
  Dim userFilePath As String
  Dim libraryFilePath As String
  Dim typeSelect As Integer

    userFilePath = Application.GetOpenFilename
    If userFilePath = "False" Then Exit Sub
    
    If typeSelect = 1 Then
       libraryFilePath = "H:\TEMP\Lib1\"
    ElseIf typeSelect = 2 Then
       libraryFilePath = "H:\TEMP\Lib2\"
    ElseIf typeSelect = 3 Then
       libraryFilePath = "H:\TEMP\Lib3\"
    Else
       libraryFilePath = "H:\TEMP\"
    End If

 'insert code to save the selected file to the selected file path
  FileName = Dir(userFilePath)
  FileCopy userFilePath, libraryFilePath & FileName

End Sub

Does

ThisWorkbook.Path

help any?