Copying Files the Fiie Copy Statement

To copy files between folders, use the FileCopy statement shown below: FileCopy source, destination

The first parameter of this statement, source, specifies the name of the file that you want to copy. The name may include the drive in which the file resides. The second parameter, destination, is the name of the destination file that can include the drive and folder designation. Both parameters are required.

Suppose you want to copy a file specified by a user to a folder called C:\Abort. The procedure shown below demonstrates how to do this:

Sub CopyToAbort()

Dim folder As String Dim source As String Dim dest As String Dim msgl As String Dim msg2 As String Dim p As Integer Dim s As Integer Dim i As Long

On Error GoTo ErrorHandler folder = "C:\Abort"

msgl = "The selected file is already in this folder." msg2 = "was copied to" P = 1 i = 1

' get the name of the file from the user source = Application.GetOpenFilename ' don't do anything if cancelled If source = "False" Then Exit Sub

' get the total number of backslash characters "\" in the source ' variable's contents Do Until p = 0

p = InStr(i, source, "\", 1) If p = 0 Then Exit Do s = p i = p + 1


' create the destination file name dest = folder & Mid(source, s, Len(source)) ' create a new folder with this name MkDir folder

' check if the specified file already exists in the ' destination folder If Dir(dest) <> "" Then MsgBox msg1


' copy the selected file to the C:\Abort folder FileCopy source, dest

MsgBox source & " " & msg2 & " " & dest End If Exit Sub ErrorHandler: If Err = "75" Then

Resume Next End If

MsgBox "You can't copy an open file." Exit Sub End If End Sub

The procedure CopyToAbort uses the Excel application method called GetOpenFilename to get the name of the file from the user. This method causes the built-in Open dialog box to pop up. Using this dialog box, you can choose any file, in any directory, and on any disk drive. If the user cancels, Visual Basic returns the value "False" and the procedure ends. If the user selects a file and clicks Open, the selected file will be assigned to the variable source. Because for the purpose of copying you'll only need the filename (without the path), the Do...Until loop finds out the position of the last backslash ("\") in the file stored in the variable source.

Next, Visual Basic prepares a string of characters for the second argument of the FileCopy statement and assigns it to the variable dest. This variable holds the string obtained by concatenating the name of the destination folder (C:\Abort) with the user-specified filename preceded by a backslash ("\"). The MkDir function creates a new folder called C:\Abort if it doesn't yet exist on drive C. If such a folder already exists, Visual Basic will need to deal with error 75. This error will be caught by the error handler code included at the end of the procedure. Notice that the error handler is a fragment of code that begins with the label ErrorHandler followed by a colon.

When Visual Basic encounters the Resume Next statement, it will continue to execute the procedure from the instruction following the instruction that caused the error. This means that the statement MkDir folder won't be executed. After that, the procedure checks whether the selected file already exists in the destination folder. If the file already exists there, the user will get a message stored in the variable msg1. If the file does not exist in the destination folder and the file is not currently open, Visual Basic will copy the file to the specified folder and notify the user with the appropriate message. If the file is open, Visual Basic will encounter run-time error 70, and therefore it will run the corresponding instructions in the ErrorHandler section of the procedure.

1. Enter the procedure CopyToAbort in a new module called FileCopyStatement.

2. Run this procedure several times, selecting files from different folders.

3. Try to copy a file that was copied before by this procedure to the C:\Abort folder.

4. Open one file and try to copy it while it's open by using the CopyToAbort procedure.

5. Run the procedure MyFiles prepared earlier in this chapter to write to the Immediate window the contents of the folder C:\Abort.

Note: Do not delete the C:\Abort folder or the files that you have copied to it. You'll delete both the folder and the files in the next section with the VBA procedure called RemoveMe.

0 0

Post a comment