Copy File To (Use Dialog Box to Find Location)

[Learn2010]

Option #1
I have a folder with Access files, subfolders, and some .vbs files in it. It requires one person to navigate through a setup process. Once the setup is completed, they need to copy the folder to a location on a shared drive. I would like to do this with vba in a file located outside the folder. In a nutshell, here is how the setup takes place.

Files are downloaded to C:\Program Files.
A vbscript is run and extracts a folder directly to the C:\Drive and opens it.
The user manually moves the BE file to a shared drive.
The user opens the FE file and is walked through using the Linked Table Manager.
The user then follows a series of forms to input data.
After this, a Shell command closes that DB and opens another.
The second DB walks them through the Linked Table Manager and closes.
At this point I would like to open the file outside the folder.

Here is what I need:
Upon opening the third file, I would like to have it copy the folder C:\Test and have a dialog box open telling them to paste it in a location where all users have access to, allowing them to navigate to that location and paste the file.

Option #2
Another option is to open a dialog box that would allow for selection of a folder and the option to Save As. This way the user can select the file and save it to another location.

Can anyone do that?

Thank you.

 

[vbaInet]

Upon opening the third file, I would like to have it copy the folder C:\Test and have a dialog box open telling them to paste it in a location where all users have access to, allowing them to navigate to that location and paste the file.

See the following link for examples:

http://www.rondebruin.nl/folder.htm

 

[Learn2010]

The location of the file to copy is C:\MyFolder. The location to move it to is unknown to me. There could be several sites using this program and each will be different. The location to move the file will be at the discretion of the person that sets up the program on their system.

Is there a way to script that?

Thank you.

 

[vbaInet]

Search the site for a folder dialog or Google it. You should find some code. If not, come back.

 

[Learn2010]

I got a response on another post that helped me solve the problem, which actually was to copy a folder and allow the user to select the location to paste it.

What I need now is the code to select a designated file, which I put in the code, and then have a dialog box that allows the user to paste that file on a shared drive that all the other users at that location have access to.

The reason I need the dialog box is that this setup process will take place at several different sites, with different networks, and I have no control over where the file is going to be placed. I would like to control the process of copying and pasting.

Thank you.

 

[vbaInet]

What code do you have? All you do is use the Folder Picker Dialog (not the File Dialog) and get the path returned in the SelectedItems collection. Then use that path in your copy command.

 

[Learn2010]

I tried using the code the other guy gave me and change some areas but I couldn't get it to work. In the below code, the folder to copy is actually inside C:\MyFolder. This code copies that folder and opens the dialog box for the user to select the location to paste it.

Private Sub btnCopyAFolder_Click()
Dim f As Object, h As Object 'declare f as an object
Dim x, y As String
On Error Resume Next
'Set h = Application.FileDialog(msoFileDialogFolderPicker)   'h equal to the folder picker
Set h = Application.FileDialog(msoFileDialogFolderPicker)
With h  'with the select folder dialog box
    .title = "Select Location To Copy To"    'set the title to the following
    .ButtonName = "Select Location To Copy To and Click This Button"  'set the button caption to the following
    .Show  'show the pic folder
End With    'end with h
'copy folder to selected folder
'change C:\Documents and Settings\rvasquez\My Documents\Example Spreadsheets\Hold Folder
'to the folder you want them to copy
x = CopyFolder("C:\MyFolder", h.SelectedItems(1), True)
End Sub
Function CopyFolder(sFolderSource As String, sFolderDestination As String, _
                    bOverWriteFiles As Boolean) As Boolean
On Error GoTo Error_Handler
   Dim fs As Object
    CopyFolder = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles
    CopyFolder = True
 
Error_Handler_Exit:
    On Error Resume Next
    Set fs = Nothing
    Exit Function
 
Error_Handler:
    If Err.Number = 76 Then
        MsgBox "The 'Source Folder' could not be found to make a copy of.", _
                vbCritical, "Unable to Find the Specified Folder"
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: CopyFolder" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function
Function CopyFile(strSource As String, strDest As String) As Boolean
On Error GoTo CopyFile_Error
 
    FileCopy strSource, strDest
    CopyFile = True
    Exit Function
 
CopyFile_Error:
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & strSource & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: ModExtFiles / CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    End If
    Exit Function
End Function