How to use batch file code in a vba module

Question

I have a batch file that copies an excel file to different hard drives as a security measure. I want to take the batch file code and drop it into an excel module, thus eliminating the need for the batch file.

Answer 1

Use VBA’s ability to run external commands instead of the batch file. The batch logic (copying files) can be implemented directly in VBA with either Shell (to call xcopy/robocopy) or native FileCopy.

Example 1 – Call the existing batch logic via xcopy (no .bat file needed):

Sub BackupWorkbook()
    Dim src As String
    Dim dest1 As String
    Dim dest2 As String
    Dim cmd As String

    src = "C:\Path\To\Source\MyFile.xlsx"
    dest1 = "D:\Backups\MyFile.xlsx"
    dest2 = "E:\Backups\MyFile.xlsx"

    ' Copy to first drive
    cmd = "cmd /c xcopy """ & src & """ """ & dest1 & """ /Y /I"
    Shell cmd, vbHide

    ' Copy to second drive
    cmd = "cmd /c xcopy """ & src & """ """ & dest2 & """ /Y /I"
    Shell cmd, vbHide
End Sub

Adjust the paths and add more destinations as needed. This reproduces what a batch file would do, but the commands live in the VBA module.

Example 2 – Use pure VBA (no command line):

Sub BackupWorkbookVBA()
    Dim src As String
    Dim dest1 As String
    Dim dest2 As String

    src = "C:\Path\To\Source\MyFile.xlsx"
    dest1 = "D:\Backups\MyFile.xlsx"
    dest2 = "E:\Backups\MyFile.xlsx"

    FileCopy src, dest1
    FileCopy src, dest2
End Sub

To run this macro:

1. Press Alt+F11 to open the Visual Basic Editor.
2. Insert a new Module and paste one of the procedures above.
3. Close the editor, then in Excel use Alt+F8 → select the macro → Run.

For reuse on any workbook, place the macro in Personal.xlsb so it is available globally, or copy the module between workbooks using the Visual Basic Editor.

---

References:

• Automate tasks with the Macro Recorder
• Create and save all your macros in a single workbook