Consulting

Results 1 to 7 of 7

Thread: Move file to new folder based on cell value, create folder if not already exist

  1. #1

    Move file to new folder based on cell value, create folder if not already exist

    i'm very new to VBA, and would like things to stay ELI5 ( explain like i'm 5 ).

    any simple way to do this?

    If I3 = "Yes"
    then move file in "E3" to Folder from B7 + \Approved - Ex H:\VBA\Approved
    then afterward remove "Yes" from I3 ( so it doesent move all )

    and if the folder doesent exist, then create it.
    Attached Images Attached Images

  2. #2
    It depends on your definition of simple. If copy and paste is included in that definition then you need a macro .

    Sub MoveFile()
    'Graham Mayor - https://www.gmayor.com - Last updated - 30 Nov 2018
    Dim fso As Object
    Dim iPath As Long
    Dim vPath As Variant
    Dim strPath As String, strOldPath As String
    Dim strName As String
    Dim xlSheet As Worksheet
        Set xlSheet = ActiveSheet
        With xlSheet
            strPath = .Range("A7") & "\Approved"    'assign the target path to a string
            If UCase(.Range("I3")) = "YES" Then    'check if the move is approved
                Set fso = CreateObject("Scripting.FileSystemObject")
                strOldPath = .Range("E3")    'assign the original file path to a string
                If fso.FileExists(strOldPath) Then
                    'extract the filename from the full name
                    strName = Split(strOldPath, "\")(UBound(Split(strOldPath, "\")))
                    'ensure the target path exists and create it if it doesn't
                    vPath = Split(strPath, "\")
                    strPath = vPath(0) & "\"
                    For iPath = 1 To UBound(vPath)
                        strPath = strPath & vPath(iPath) & "\"
                        If Not fso.FolderExists(strPath) Then MkDir strPath
                    Next iPath
                    'move the file
                    Name strOldPath As strPath & strName
                    Beep
                    MsgBox "File moved to " & strPath
                Else
                    Beep
                    MsgBox strOldPath & " not found"
                End If
            End If
        End With
        Set xlSheet = Nothing
        Set fso = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    does not seem to work ( i'v fixed the A7 to B7 in the StrPath ).
    besides, i need this to work for all lines below aswell.

  4. #4
    it works, just need to make it apply to all cells in "I" row now.
    anyone kind enough to edjucate educate a "beginner" ?

  5. #5
    You will have to explain what you mean by
    apply to all cells in "I" row
    , There is only one row in the I Column? The macro was programmed to work with the information you provided.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    i mean the macro should apply to all of the cells between example: "I3:I100"
    sorry for not expressing clearly.

    still new to VBA, and learning all the correct terminology is a first step
    Attached Images Attached Images

  7. #7
    anyone willing to assist ?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •