-1

I have write a VBA code to copy file from one directory to another; but I can't figure out why it is not working. Any ideas? I know how to do it using FileSystemObject but I will like to learn - doing it with SHELL. 

Sub copy_file()

    Dim dirPath As String, srcFile As String

    dirPath = "E:\Download\"
    srcFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    Shell ("cmd /c copy /y """ & srcFile & " " & dirPath & """")

End Sub
Pang
  • 9,564
  • 146
  • 81
  • 122
Mukibul Hasan
  • 592
  • 5
  • 10

3 Answers3

1

You're over-complicating.

You don't need to use Shell and go through the command line for this.

There's a built-in command: FileCopy

Example

This example uses the FileCopy statement to copy one file to another. For purposes of this example, assume that is a file containing some data.

Dim SourceFile, DestinationFile 
SourceFile = "SRCFILE" ' Define source file name. 
DestinationFile = "DESTFILE" ' Define target file name. 
FileCopy SourceFile, DestinationFile ' Copy source to target.

Read the documentation for FileCopy at the source.

I'd also suggest taking a few minutes to read through all the standard VBA objects and their available methods/functions/properties/etc to get an idea of what kind of tasks have built-in functionality with VBA and/or Office.

Here is the main page of the official documentation for Office VBA.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thanks for your reply, but i want to do it with shell command, i already know how to use FileCopy i wanted to know some thing new, your code will help me too. – Mukibul Hasan Aug 16 '18 at 01:30
0

srcFile is the same file that you are using to hold your vba code, and therefore that file is already open. In that case Windows shell copy command will not work, by design. You can duplicate your opened source file with the ActiveWorkbook.SaveAs method. Further reading: How to do a "Save As" in vba code, saving my current Excel workbook with datestamp? Further problem: your active workbook will immediately renamed, therefore you have to close it. In order to avoid having save as dialog before exit, read that tutorial: https://support.microsoft.com/en-us/help/213428/how-to-suppress-save-changes-prompt-when-you-close-a-workbook-in-excel

Zoltán Gergely Kis
  • 9
  • 1
  • 1
0

Shell ("cmd /c copy /y """ & srcFile & " " & dirPath & """")

Quotes issue. You should be check your path and try this code below:

Sub copy_file()

Dim dirPath As String, srcFile As String

dirPath = "E:\Download\"
srcFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name
MsgBox "cmd /c copy /y " & srcFile & " " & dirPath
Shell ("cmd /c copy /y " & srcFile & " " & dirPath)

End Sub

Simon Li
  • 303
  • 2
  • 4