Vba copy file in vba in Excel
At many instances, the user may be required to copy one or more files from one location to another within the program.
This type of requirement is most seen in corporate applications where files are moved often.
The Excel VBA allows the user to move or copy file with ease without involving the manual work of opening drive folders copy, paste and so on.
The following code demonstrates the file transfer with VBA.
- Sub filecopy()
- Dim fDialog As FileDialog, result As Integer
- Dim sfil As String
- Dim fldr As FileDialog
- Dim sItem As String
- Dim FSO
- Set FSO = CreateObject("Scripting.FileSystemObject")
- Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
- fDialog.AllowMultiSelect = False
- fDialog.Title = "Select a file"
- fDialog.InitialFileName = "C:\"
- fDialog.Filters.Clear
- fDialog.Filters.Add "Excel files", "*.xlsx"
- fDialog.Filters.Add "All files", "*.*"
- If fDialog.Show = -1 Then
- MsgBox fDialog.SelectedItems(1)
- sfil = fDialog.SelectedItems(1)
- End If
- Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
- With fldr
- .Title = "Select a Folder"
- .AllowMultiSelect = False
- .InitialFileName = Application.DefaultFilePath
- If .Show <> -1 Then GoTo NextCode
- sItem = .SelectedItems(1)
- End With
- MsgBox "You have selected " & sItem
- NextCode:
- GetFolder = sItem
- Set fldr = Nothing
- FSO.CopyFile (sfil), sItem, True
- End Sub
The above code demands the user to select file to copy and destination folder and then copies the file from source to destination.
The screenshot of the editor is as shown below.