I am sure this is easy, I have bits and pieces, but I need the middle part.
I have a file that arrives in morning to location 1 on the network named the following
1) I want to copy from location FeedPath >> DONE, See Code
2) rename it (removing the date), >> NEED HELP WITH THIS STEP <<
3) Copy to location CopyToPath (automatically overrides file from day before). >> DONE, See Code
4) Run a macro in my db >>DONE
5)delete files from location FeedPath. >>DONE, ALREADY HAVE CODE, SEE SECOND FUNCTION DeleteFeedFiles()
Here is my code My macro is already set up to run the functions in order, I just need to know how to rename the file not sure of the order:
Copy Files from and to code:
Dim FSO As Object
Dim FeedPath As String
Dim CopyToPath As String
Dim FileExt As String
FeedPath = "\\usporamfs01\share\RAD\Master_Data\Latest_Feed"
CopyToPath = "\\usporamfs01\share\RAD\Master_Data"
FileExt = "*.xlsx"
If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(FeedPath) = False Then
MsgBox FromPath & " doesn't exist"
If FSO.FolderExists(CopyToPath) = False Then
MsgBox CopyToPath & " doesn't exist"
FSO.CopyFile Source:=FeedPath & FileExt, Destination:=CopyToPath
'MsgBox "You can find the files from " & FromPath & " in " & ToPath
Delete Files Code
On Error GoTo errorHandler
Dim FSO As Object
Dim FeedPathName As String, ToPath As String, FileExt As String
FeedPathName = "\\usporamfs01\share\RAD\Master_Data\Latest_Feed"
Dim fileName As String
If Len(FeedPathName) > 0 Then
If Right(FeedPathName, 1) <> "\" Then FeedPathName = FeedPathName & "\"
fileName = Dir(FeedPathName & "*.xlsx")
While Len(fileName) > 0
Kill FeedPathName & fileName
fileName = Dir()
If Err.Number = 70 Then
Select Case MsgBox("Could not delete " & fileName & ". Permission denied. File may be open by another user or otherwise locked.", vbAbortRetryIgnore, "Unable to Delete File")
MsgBox "Error deleting file " & fileName & ".", vbOKOnly Or vbCritical, "Error Deleting File"