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.
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
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.
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.
Code:
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
Code:
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.
VBA Code:
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