Move a file with the click of a button

[davedinger]

This is my Delemma:


I have users that download blank forms from Vendors to their desktops, which I understand is easy for the unsers and it fits their needs, but then they end up saving them on the server in all sorts of places. Now with multiple users doing this they are making a mess on the server with all of the locations and duplicating files.

So I have a form named TBLVendorDocuments and a form named "FRMVendorDocuments" and I am trying to create a button named "Locate" on the vendor Documents Form that will allow them to locate their file in something like the file picker and when found they can click on a button that will automatically make a copy of the document in a Folder Named "SharedDocs" I do not want the user to have an option where the file goes. I then need to have the location of the new document to be sent to as a hyperlink to the field named Location that is in the TBLVendorDocuments Table.
The name of the Document can be anything really.

I have code that one of you Super Brains helped me with quite along time ago that gets most of this done, it just probably needs an extra line put in it someplace but I don't know where.

By the way you probably don't get told this often but we appreciate your help allot
Thanks for sharing your knowledge and time.

Private Sub Command10_Click()   Dim objFD As Object
Dim strOut As String


'msoFileDialogFilePicker = 3
Set objFD = Application.FileDialog(3) 'This changes the selector type
With objFD
'set dialog properties here, like
   .InitialFileName = "\\SVRSFP11\data\City Share\DATA FOLDER\WARRANTY DATABASE Master\SHARED DOCUMENTS\VendorDocs\"


End With


If objFD.Show Then
   'do stuff such as
   strOut = objFD.SelectedItems(1) 'this returns path of selected file
   Me.Location = strOut 'This Sends the file selection to the Field named Location
   
Else
   Exit Sub 'no point in continuing if user chose Cancel
End If


' no point in testing if user chose a file because if you have already exited, they didn't


Set objFD = Nothing


End Sub


Private Sub Location_Click()
Application.FollowHyperlink Me.Location.Value
End Sub




Private Sub Command9_Click()
Application.FollowHyperlink Location
End Sub

[madpiet]

Doesn't the NAME function in VBA do that? (Effectively rename 'C:\Path\FileName' to a different one? so you'd just change the path inside the name to move it.)

[davedinger]

The code above only opens the file picker and gives it a starting location for the file picker to look at then when a file is selected it creates a hyper link to where the user has the file. The issue I have is that say a user has a document on their desktop, they click on the button they use the file picker to navigate to their desktop they click on their file. The code then saves a hyperlink to the document on their desktop. the problem is the user eventually deletes the document on their desktop and it screws up the hyper link.
my goal is to have the user navigate to their document, click on it, and automatically it will move it or copy it to a given folder "SharedDocs" and then the hyper link to the newly stored document will be saved in the "location" field of the database.

You solution may be on the right path but I'm not sure where it would go in the code. my guess would be a new line right after:

Private Sub Command10_Click() Dim objFD As Object
Dim strOut As String


'msoFileDialogFilePicker = 3
Set objFD = Application.FileDialog(3) 'This changes the selector type
With objFD
'set dialog properties here, like
.InitialFileName = "\\SVRSFP11\data\City Share\DATA FOLDER\WARRANTY DATABASE Master\SHARED DOCUMENTS\VendorDocs"

[madpiet]

Here's a good page for your problem: Move Files with VBA FileSystemObject (MoveFile) - Automate Excel

[Micron]

I'm wondering if you want to move the file from someone's desktop or personal folder, or copy it to the controlled folder (SharedDocs)?

[moke123]

There are several ways to accomplish this but also a lot of variables come into play.
What happens when 2 vendors both have a form called "OrderForm"? Does the name of the file matter? can it be renamed?

the location of the new document to be sent to as a hyperlink to the field named Location that is in the TBLVendorDocuments Table.

I would not use a hyperlink datatype. I find it to be a real P.I.T.A.
If all the files are in the same folder you do not need to save the whole path to a table, Only the filename. You can concatenate the folder path to the file name and open it with code.

[davedinger]

This is what the form the user sees,
There is a field named "DocumentName" where the user makes their own name for the document.
a button named open that opens the hyper ink to the file location
a button locate/add that opens the file picker
and The actual file name location is shown in grey and will eventually be hidden.

So yes, files can be renamed to avoid duplicates since the user attaches their own name to the file location.
and I'm game to use the file path, i just knew how to use the hyperlink so that's what I did.

Thanks for you help

[moke123]

So yes, files can be renamed to avoid duplicates since the user attaches their own name to the file location.

Until 2 users happen to pick the same name You still have to test that the file name (actually the new file path) doesn't exist.
The attached will prompt for a new file name if the given file name is already in use.

Personally I would opt for having a folder for each vendor. You then wouldn't need to save the file path to a table.
You just use fso to iterate over the folder and list all the files for that vendor.

The attached shows 2 ways to do it.
One saves the path to a table with all the files in one folder.
The second creates a folder for each vendor and doesn't require a table.

[davedinger]

Sorry this has taken so long to get back to you but you did a really great job on the code its more then I expected. I have been taking it apart and trying different things to learn from it and I have learned allot from it already.
Thanks.

I'm having one issue. I cant get the code to send the file I pick to a folder on the server. I can get it to send to most anyplace on my desktop or local system, but when it gets to a server location I must have some syntax issue. How do I get the file to go to

\\SVR0621P01\data\DE MOD Share\DATA FOLDER\WNTY DATABASE MASTER\Shared Documents\VendorDocs

I'm pretty sure it goes in the code between the lines i have marked with ***************

I my mind the line that currently reads:
strDestination = CurrentProject.Path & "\Shared_Folders" 'set the path to the destination folder

should read
strDestination = CurrentProject.Path & "\\SVR0621P01\data\DE MOD Share\DATA FOLDER\WNTY DATABASE MASTER\Shared Documents\VendorDocs"

Can you explain what I'm Doing wrong?

Thanks

Private Sub CopyToShared(strSource As String)
 
    Dim strDestination As String, FilExt As String
    Dim NewName As String, NewPath As String


   ' Set fso = New FileSystemObject
    
   ' If Not fso.FolderExists(CurrentProject.Path & "\Shared_Folders") Then   'make sure folder exists
   '    fso.CreateFolder CurrentProject.Path & "\Shared_Folders"
   ' End If
   
  '*************************************************************************************
   
    strDestination = CurrentProject.Path & "\Shared_Folders"  'set the path to the destination folder
    
    FilExt = fso.GetExtensionName(strSource) ' get file extension from source file
    
' **************************************************************************************


TryAgain:


    NewName = InputBox("Please enter a New File Name" & vbNewLine & "Do not include the file extension, (ie.'.Doc')")


    If NewName = "" Then Exit Sub


    NewPath = fso.BuildPath(strDestination, NewName & "." & FilExt)


    If fso.FileExists(NewPath) Then MsgBox "Name already exists. Try Again": GoTo TryAgain 'if file already exists re-try name


    fso.CopyFile strSource, NewPath, False  'copy the file to the destination folder
    
    InsertInTable Me.VendorLink, NewName & "." & FilExt, NewPath  'insert the file info into table
    
End Sub


Private Sub InsertInTable(VID As Long, FilName As String, FPath As String)


    Const QDef_Insert As String = _
        "Insert into tblDocuments" & _
        "(VendorLink,DocumentName,Location) " & _
        "Values(p0,p1,p2)"


    With CurrentDb.CreateQueryDef("", QDef_Insert)
        .Parameters(0) = VID
        .Parameters(1) = FilName
        .Parameters(2) = FPath
        .Execute dbFailOnError
        .Close
    End With
    
End Sub

[pbaldy]

I my mind the line that currently reads:
strDestination = CurrentProject.Path & "\Shared_Folders" 'set the path to the destination folder

should read
strDestination = CurrentProject.Path & "\\SVR0621P01\data\DE MOD Share\DATA FOLDER\WNTY DATABASE MASTER\Shared Documents\VendorDocs"

Can you explain what I'm Doing wrong?

You don't want the CurrentProject bit. Try

strDestination = "\\SVR0621P01\data\DE MOD Share\DATA FOLDER\WNTY DATABASE MASTER\Shared Documents\VendorDocs"

You may need a backslash at the end.

[davedinger]

We have a winner!

Thanks to all that helped