Save File Name not whole File Path

[SimoneRene]

Hi!

When you click a button on my form the code currently opens a file viewer and copies the file selected by the user to a new folder. The original file path is displayed in a text box which is bound to a cell in my table.

I want the text box (and corresponding cell in the table) to only display the file name(not the whole file path).
Do I need to use SafeFileName or FileName as String? Not sure how or where to put either of these in the code.

Also is there a way of only displaying the file name in the text box if there are no duplicates?
If the user tries to copy a file with a duplicate name an error message pops up and the file is not copied(which is great) but the file(and currently whole file path) is still displayed in the text box.

Private Sub Button_Click()
Dim CopyDialog As Office.FileDialog
Dim SourceFile As Variant
Dim targetFile As Variant
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim fNameFile As String

 FileViewerTxt = ""
 
'Add File Destiation
targetFile = "X:\Folder\"
ChDir Dir("*.*", vbDirectory)
Set CopyDialog = Application.FileDialog(msoFileDialogFilePicker)
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With CopyDialog
With fDialog
.AllowMultiSelect = False
.Title = "Select File"
.Filters.Add "pdf File", "*.pdf"

If .Show = Fasle Then
    MsgBox "File Upload Canceled"
End If
 

For Each SourceFile In .SelectedItems
If Len(Dir(targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\")))) <> 0 Then
    MsgBox "Same File Name Already Exists, Please Rename and Try Again!"
 
End If
   
For Each varFile In .SelectedItems
      FileViewerTxt = FileViewerTxt & varFile & vbCrLf

    Next

    FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\"))



Next
End With
End With

End Sub

Thanks in advance for any help! I'm new to access!

 

[sxschech]

Here are some file functions from https://sqlaccxl.wordpress.com/2013...name-withwithout-file-extension-or-path-only/

Function FileNameNoExt(strPath As String) As String
 'https://sqlaccxl.wordpress.com/2013/03/06/vba-function-to-extract-file-name-withwithout-file-extension-or-path-only/
 Dim strTemp As String
 strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1)
 FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1)
End Function
 
'The following function returns the filename with the extension from the file's full path:
Function FileNameWithExt(strPath As String) As String
 FileNameWithExt = Mid$(strPath, InStrRev(strPath, "\") + 1)
End Function
 
'the following function will get the path only (i.e. the folder) from the file's ful path:
Function filePath(strPath As String) As String
 filePath = Left$(strPath, InStrRev(strPath, "\"))
End Function

 

[SimoneRene]

Thanks sxschech,

This is helpful, however, I'm unsure of how this works with my existing code.

Do I remove the FileDialog and use this instead. How do I use the function and the existing sub together?

If I create a module and change my command button to 'RunCode' can I choose to run both the sub and the function?

Sorry I'm a newbie.

 

[sxschech]

I was working on putting together some code, would like a bit of clarification. User is selecting 1 file or multiple files to copy?
User is choosing the file to copy based on a textbox, combobox, listbox or by clicking a button to bring up the dialog box in order to choose the source files in a directory (1 or multiple) and then using another dialog box to choose the destination directory?

 

[SimoneRene]

Thank you very much sxschech.

I was working on putting together some code, would like a bit of clarification. User is selecting 1 file or multiple files to copy?
User is choosing the file to copy based on a textbox, combobox, listbox or by clicking a button to bring up the dialog box in order to choose the source files in a directory (1 or multiple) and then using another dialog box to choose the destination directory?

The User selects one file at a time hence ".AllowMultiSelect = False"
The User clicks one button that brings up the dialog box the user selects the source file and it automatically pings to the destination 'targetFile = "X:\Simone" ' .
So currently its just one button does all.

However I do think it might be better to split it into two buttons i.e. one to open FileDialog and select the file(shown in a txtbox) and another to copy/save the file to destination. I'm a novice so not sure how to do this.

I have the code that I used previously to open the Fdialog and show the file the user selects in a txtBox but then couldn't figure out how to copy that file to the destination hence why it kind of merged into one button.

 

[sxschech]

Was busier than expected today, will provide you the code on Monday. You shouldn't need two buttons. The code I'll provide will allow user to select the file and copy it to X:\Simone. If the file already exists, it will offer the user to replace the existing file, or save the file under a new name. If you prefer, code can prevent file from being replaced.

You mentioned a textbox on the form, is that containing the name of the file to be copied and all you are asking user to do is select the folder where the file is located or is the user selecting the file by locating it in the folder?

 

[moke123]

i had some time to fool around with this.
here's an example which may give you some ideas.

 

[SimoneRene]

Was busier than expected today, will provide you the code on Monday. You shouldn't need two buttons. The code I'll provide will allow user to select the file and copy it to X:\Simone. If the file already exists, it will offer the user to replace the existing file, or save the file under a new name. If you prefer, code can prevent file from being replaced.

You mentioned a textbox on the form, is that containing the name of the file to be copied and all you are asking user to do is select the folder where the file is located or is the user selecting the file by locating it in the folder?

Thanks sxschech!

Definitely don't want any files replaced. But having the option to rename would be great!

I mentioned two buttons so the user selects a file to "upload" and then confirms that its the correct file before "saving".
Maybe a message box could do the same job i.e. the user selects the file and before its copied to the destination a box pops up saying "Are you sure ...File is the correct file" Confirm or Cancel.

The File Dialog box appears when the button is clicked and the selected file and file path is shown in the text box. The point of the text box was so the user could see the file they have chosen to upload. The text box is also bound to a table to keep a record of the files that have been saved using the form.

Thank you.

 

[SimoneRene]

i had some time to fool around with this.
here's an example which may give you some ideas.

Thanks Moke123,

The code looks great I think it will do what I'm after.
I have copied it into my database and am getting a Compile error, "sub or function not defined" on If FileExists. Any tips on how to fix this? Why would this occur? References?

Thank you!

 

[moke123]

there are a few modules included in the sample that you will need to import also. modFileExistsFolderExists is necessary and is the reason for the error. I included a couple which are not used but I included them anyway just so you could see them.

if you look through the code...
tsGetFileFromUser is in modBrowseFiles
FileExists is in modFileExistsFolderExists
fHandleFile is in modShellExec

 

[SimoneRene]

there are a few modules included in the sample that you will need to import also. modFileExistsFolderExists is necessary and is the reason for the error. I included a couple which are not used but I included them anyway just so you could see them.

if you look through the code...
tsGetFileFromUser is in modBrowseFiles
FileExists is in modFileExistsFolderExists
fHandleFile is in modShellExec

Thank you Moke123, I have added modFileFolderExists but modBrowseFiles and modShellExec are for 32 bit systems. I'm running 64. I will look into the conversion and let you know if I get it working.

 

[SimoneRene]

Thank you Moke123, I have added modFileFolderExists but modBrowseFiles and modShellExec are for 32 bit systems. I'm running 64. I will look into the conversion and let you know if I get it working.

Converted to 64 bit by adding ptrSafe to all declare functions.
Doesn't appear to be any problems when I debug but nothing appears to happen when I click the button on my form.

 

[moke123]

cant help ya with 64bit access, dont have it.
good luck with your project.

 

[SimoneRene]

cant help ya with 64bit access, dont have it.
good luck with your project.

Thanks for your help moke123, you have given me some good ideas. I will keep working on it!

 

[SimoneRene]

Ok so I have managed to solve my original question!
The text box now displays the filename only not the whole file path. The textbox only displays the file if it copies successfully

Below is the code for anyone who is interested:

Private Sub Button_Click()
Dim CopyDialog As Office.FileDialog
Dim SourceFile As Variant
Dim targetFile As Variant
Dim varFile As Variant
Dim objDialog As Object

 FileViewerTxt = ""
'Add File Destiation
targetFile = "X:\Folder\"
ChDir Dir("*.*", vbDirectory)
Set CopyDialog = Application.FileDialog(msoFileDialogFilePicker)
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
With CopyDialog
With objDialog
.AllowMultiSelect = False
.Title = "Select File"
.Filters.Add "pdf File", "*.pdf"

If .Show = Fasle Then
    MsgBox "File Upload Canceled"
  End If

For Each SourceFile In .SelectedItems
If Len(Dir(targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\")))) <> 0 Then
    MsgBox "Same File Name Already Exists, Please Rename and Try Again!"
Else
    Me.FileViewerTxt = Dir(.SelectedItems(1))

 End If
For Each varFile In .SelectedItems
Next
    FileCopy SourceFile, targetFile & Mid$(SourceFile, InStrRev(SourceFile, "\"))
Next

End With

End With
End Sub

 

[sxschech]

Glad you solved your issue. With regard to the functions I provided earlier: You would place them in a standard code module (not a form module) then call them in your code.
Let's say sourcefile is X:\Folder\MyFileName.PDF

FileNameNoExt(SourceFile) would return MyFileName
FileNameWithExt(SourceFile) would return MyFileName.PDF
FilePath(SourceFile) would return X:\Folder\

 

[SimoneRene]

Glad you solved your issue. With regard to the functions I provided earlier: You would place them in a standard code module (not a form module) then call them in your code.
Let's say sourcefile is X:\Folder\MyFileName.PDF

FileNameNoExt(SourceFile) would return MyFileName
FileNameWithExt(SourceFile) would return MyFileName.PDF
FilePath(SourceFile) would return X:\Folder\

Thanks sxschech, that's good to know for the future!