Excel Moves Files from Source to Destination VBA

Good Evening Experts

Looking for VBA solution in excel - moving files

Source column A Path - C:\temp

Filename column B - test.pdf

Destination Column C - C\temp\archive\

I want to loop through each row move the file on the source to the destination folder based on the example above.

If the file exists over write it
Nick CollinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Nick

To the paths in column A and C change?
0
Nick CollinsAuthor Commented:
No they stay the same
0
Martin LissOlder than dirtCommented:
The 2 in row 10 is the first row of data. If it isn't then change that line.
Sub MoveFiles()

Dim lngLastRow As Long
Dim lngRow As Long
Dim strFrom As String
Dim strTo As String

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = 2 To lngLastRow
    If Right$(Cells(lngRow, "A"), 1) = "\" Then
        strFrom = Cells(lngRow, "A")
    Else
        strFrom = Cells(lngRow, "A") & "\"
    End If
    If Right$(Cells(lngRow, "C"), 1) = "\" Then
        strTo = Cells(lngRow, "C")
    Else
        strTo = Cells(lngRow, "C") & "\"
    End If

    Name strFrom & Cells(lngRow, "B") As strTo & Cells(lngRow, "B")
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nick CollinsAuthor Commented:
In the example, there could be multiple entries and in column C the path could be different..

As the destination folders are different
0
Martin LissOlder than dirtCommented:
Please show an example of what you mean.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub MoveFiles()
Dim fso As Object
Dim srcFolder, destFolder
Dim file
Dim lr As Long, i As Long
Dim AllGood As Boolean

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set fso = CreateObject("Scripting.FileSystemObject")

For i = 2 To lr
    srcFolder = Cells(i, 1).Value
    destFolder = Cells(i, 3).Value
    file = Cells(i, 2).Value
    
    If Right(srcFolder, 1) <> "\" Then srcFolder = srcFolder & "\"
    If Right(destFolder, 1) <> "\" Then destFolder = srcFolder & "\"
    If Not fso.FolderExists(srcFolder) Then
        Cells(i, 4) = "Invalid Source Path"
    ElseIf Not fso.FolderExists(destFolder) Then
        Cells(i, 4) = "Invalid Destination Path"
    ElseIf Not fso.FileExists(srcFolder & file) Then
        Cells(i, 4) = "File Not Found"
    Else
        AllGood = True
    End If
    If AllGood Then
        If fso.FileExists(destFolder & file) Then fso.DeleteFile destFolder & file
        fso.MoveFile srcFolder & file, destFolder
        Cells(i, 4).Value = "File moved"
    End If
    AllGood = False
Next i
End Sub

Open in new window

1
Nick CollinsAuthor Commented:
Table.JPG
See the enclosed table for example - the idea is to move the files from the path in Column A
to the folder in column C. The process to loop through row and move..
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Did you test the code I proposed? If yes, did it not work as desired?
0
Nick CollinsAuthor Commented:
I got an error message - can't execute code in break mode
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's strange. Are you debugging the code?
BTW I just tested it and it worked as desired.
0
Nick CollinsAuthor Commented:
I can take a look at your example
0
Martin LissOlder than dirtCommented:
My code should also work with the example you show. And to correct the problem with "can't execute code in break mode", go to Visual Basic by pressing Alt+F11 and then click the little square button in the menu bar. (Your setup may not be exactly like mine)
2018-05-11_05-58-24.png
0
Nick CollinsAuthor Commented:
For some reason it can't find the file - i can see the file
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please be specific. Which code you are referring to?
0
Nick CollinsAuthor Commented:
My mistake - code is working correcty
0
Martin LissOlder than dirtCommented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am still not sure which code you were having issues with?

Since you accepted Martin's answer as a solution and it seems you didn't test my code, splitting points here doesn't make much sense.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.