Call .cmd from VBA to copy files from local to external drive

[Adar123]

Hi all,

I am attempting to optimize the speed of my VBA code when copying files to external drive. An observation was made that the wkBk.SaveAs command to save file in external drive takes quite a while to complete, whereas if the command to copy is used in CMD command, it takes a split second.

wkBk.SaveAs "x:\\Test\file_" & dTod & "." & "xls"

Since I have already created a workbook in a local drive, I tried replacing the second wkBk.SaveAs command with FileCopy command which did speed up the code but am curious if can speed it up even further by calling CMD command

Copy "x:\\Test" "y:\\Temp"

Question 1: How do I send such instruction into CMD with VBA?
Question 2: "x:\\Test" folders contains thousands of historical files, so I am wondering how to copy only the files published today.

Thank you.

P.S. the code I used to test the speed of running the VBA. Result was 83 seconds for just 1 of the 6 files. With FileCopy command the same code ran in 40 seconds.

Sub CalculateRunTime_Seconds()
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
  StartTime = Timer

'*****************************
'Insert Your Code Here...
'*****************************

'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

 

[yky]

Question 1: How do I send such instruction into CMD with VBA?

You can create a batch file, put commands in it, and do something like "shell ("C:\test.bat")" with VBA. It may be more complicated if you need to pass parameters to the batch file.

Question 2: "x:\\Test" folders contains thousands of historical files, so I am wondering how to copy only the files published today.

You have said what's in X. You haven't said what's in Y:. I think ROBOCOPY can do it but I need more info of what you want to copy.

 

[Adar123]

Not familiar with ROBOCOPY, Y also has multiple files that are copied over from X.

 

[Tetra201]

...

Copy "x:\\Test" "y:\\Temp"

Question 1: How do I send such instruction into CMD with VBA?..

Try using

Shell "cmd /C ""copy X:\Test Y:\Temp"""

Will it really speed up your code?

 

[GWteB]

If you are copying files within a loop, yielding to the Windows OS using the DoEvents function might narrow down the amount of time in some circumstances.

 

[yky]

I created the following file using notepad and saved it as testtest.bat. I don't have network drives. So, I experimented with copying from C: to D:\test. You can modify the file and use x:\ and y:\.

robocopy c:\ d:\test file %1*.bat /xo

This batch file takes an argument (%1) from the command line. The /xo switch excludes old files, meaning only new or newer files are copied.

I called this batch file using the following VBA command:

Call Shell("C:\testtest.bat" & " re")

The argument passed is "re". The command copied any new or newer file whose name matches "re*.bat" from C:\ to D:\test\. In my case, it copied two files: read-2-line.bat and rename-all.bat.

 

[Adar123]

My first time calling .bat file out of VBA! Thanks for your input, the Robocopy worked well.

Hope the following analysis is helpful to other users on this forum:

By calling wkBk.SaveAs you spend loads of time on saving a file in a remote drive which is located on another continent. I suspect a new instance of Excel is called on the remote drive and there is some interaction going on between the local and the remote drives resulting in lengthy running time: in my case it takes 60-80 seconds to run a whole code. Considering repetition of this code 6 times, this does get annoying. If you save a version on a local drive and use FileCopy VBA command instead, this significantly speeds up the code: 30-35 seconds. This may be good enough and it is a neat solution because no external files are called.

However, if you want to run your code even faster, calling .bat with Robocopy command saves even more time. I ran a test three times with the following results:

Seconds it takes to run the code	test1	test2	test3
FileCopy	34.1​	30.63​	29.47​
Call Shell	23.07​	18.92​	19.84​

 

[Adar123]

x

 

[Adar123]

I think it is also relevant to share the code (albeit it is a shortened version) to give some sense of why it would take such time to run.

'copy data from closed source workbook
Sub copydata()

'PURPOSE: Determine how many seconds it took for code to completely run

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
StartTime = Timer

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Dim dTod As String, dYes As String

'Adjust for Monday
If Format(Now(), "DDD") = "Mon" Then
dTod = Format(Date - 2, "yyyy-mm-dd")
dYes = Format(Date - 3, "yyyy-mm-dd")
Else

dTod = Format(Date, "yyyy-mm-dd")
dYes = Format(Date - 1, "yyyy-mm-dd")
End If

Dim localZipFile As Variant, destFolder As Variant 'Both must be Variant with late binding of Shell object
Dim Sh As Object

'Check the file name ending
strFileName = "X:\\data\File_" & dYes & "_074005.zip"
strFileExists = Dir(strFileName)

strFileName2 = "X:\\data\File_" & dYes & "_074001.zip"
strFileExists2 = Dir(strFileName2)

strFileName3 = "X:\\data\File_" & dYes & "_074006.zip"
strFileExists3 = Dir(strFileName3)

If strFileExists = "" Then '.zip file to be unzipped
If strFileExists2 = "" Then '.zip file to be unzipped
If strFileExists3 = "" Then '.zip file to be unzipped
localZipFile = "X:\\data\File_" & dYes & "_064123.zip"
Else
localZipFile = "X:\\data\File_" & dYes & "_074006.zip"
End If
Else
localZipFile = "X:\\data\File_" & dYes & "_074001.zip"
End If
Else
localZipFile = "X:\\data\File_" & dYes & "_074005.zip"
End If

destFolder = "X:\\data\" 'destination folder of .zip file's unzipped contents

'Unzip all files in the .zip file

Set Sh = CreateObject("Shell.Application")
With Sh

'Unzip all files inside the .zip file

.Namespace(destFolder).CopyHere .Namespace(localZipFile).Items
End With

Set myApp = CreateObject("Excel.Application")

'Check the file name ending
If strFileExists = "" Then
If strFileExists2 = "" Then '.zip file to be unzipped
If strFileExists3 = "" Then '.zip file to be unzipped
Set wkBk = myApp.Workbooks.Open("X:\\data\File_" & dYes & "_064123.csv")
Else
Set wkBk = myApp.Workbooks.Open("X:\\data\File_" & dYes & "_074006.csv")
End If
Else
Set wkBk = myApp.Workbooks.Open("X:\\data\File_" & dYes & "_074001.csv")
End If
Else
Set wkBk = myApp.Workbooks.Open("X:\\data\File_" & dYes & "_074005.csv")
End If

lastRow = wkBk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'solve data recognition format issue
Dim dateRange As Range
Application.ScreenUpdating = False
For Each dateRange In wkBk.Sheets(1).Range("E3:E" & lastRow)
With dateRange.Offset(, 0)
.Value = dateRange
.NumberFormat = "dd-mmm-yyyy"
End With
Next

'copy data
wkBk.Sheets(1).Range("C2:H" & lastRow).Copy

myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate
Range("A1").Select
wkSht.Paste

lastRow = Range("A" & Rows.Count).End(xlUp).Row

'change date format
Range("C2:C" & lastRow).NumberFormat = "MMMYY"

'transform data according to local standard

Range("B:B,D:D").Delete

Cells(1, 1).Value = "Product"
Cells(1, 5).Value = "Publication_Date"

Dim oRange As Range
For Each oRange In ActiveSheet.Range("A1:A" & lastRow)
oRange.Replace What:="A", Replacement:="B", MatchCase:=False
oRange.Replace What:="C", Replacement:="D", MatchCase:=False

Next

'add today's date
Dim dRange As Range

dRateDate = Date

For Each dRange In ActiveSheet.Range("D2:D" & lastRow)
With dRange.Offset(, 1)
.Value = dRateDate
End With
Next

'correct dates format
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True

'Save Transformed Data
dTod = Format(Date, "yyyymmdd")

Set wkBk = ActiveWorkbook

wkBk.Sheets("NewData").Copy

ActiveWorkbook.SaveAs "X:\\data\File_" & dTod & "." & "xls"
'ActiveWorkbook.SaveAs "Y:\\Destination_data\File_" & dTod & "." & "xls"
ActiveWorkbook.Close

'Clean-up
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("NewData")
Range("A1:K" & lastRow).ClearContents
Range("A1:K" & lastRow).ClearFormats

' Copy file (calling Shell is faster)
'FileCopy "X:\\data\File_" & dTod & "." & "xls", "Y:\\Destination_data\File_" & dTod & "." & "xls"

Call Shell("X:\\data\copyfileextdrive.bat" & " re")

'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub