• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 53
  • Last Modified:

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
0
Nick Collins
Asked:
Nick Collins
  • 7
  • 5
  • 4
  • +1
2 Solutions
 
NorieVBA ExpertCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now