Consulting

Results 1 to 7 of 7

Thread: Add cell value to folder name

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location

    Add cell value to folder name

    Hello, I am using this macro to find files and move them to a folder. The folder created uses the contents in column A as the folder name. I'd like the folder to now use column A and column B as the folder name. I've tried a few things and it hasn't work. Please can someone help. Thank you

     Sub FindFilesAndMove()    Dim MyFolder As String, MyFile As String, srchStr As String, DestFoldFull As String, FSO As Object, rCell As Range
        Dim DestFold As String
        
        Set FSO = CreateObject("Scripting.Filesystemobject")
        MyFolder = "Y:\Accounts Conveyancing Shared\Populated completion documents\" '<<< change to suit
        DestFold = "Y:\Accounts Conveyancing Shared\Completions\Auto completion packs\" '<<< change to suit
        
        
        For Each rCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
            MyFile = Dir(MyFolder)
            srchStr = rCell.Value & " - "
            DestFoldFull = DestFold & rCell & "\"
            MkDir DestFoldFull
            Do While MyFile <> ""
                If InStr(MyFile, srchStr) Then
                    FSO.MoveFile Source:=MyFolder & MyFile, Destination:=DestFoldFull & MyFile
                End If
            MyFile = Dir
            Loop
        Next rCell
        
        MsgBox ("Pack Creation Successful")
        
    End Sub
    
    
    End Sub

  2. #2
    change this to:

    ..
    MyFile = Dir$(MyFolder & "*.*")

  3. #3
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    Thank you for taking the time to reply. I have tried this and the folder name has not changed, apologies if I'm missing something, should I be changing anything else?

    Quote Originally Posted by arnelgp View Post
    change this to:

    ..
    MyFile = Dir$(MyFolder & "*.*")

  4. #4
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    774
    Location
    It depends whether both cells make one folder name:
    DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "\"
    Or if different levels of folder then maybe:
    DestFoldFull = DestFold & rCell & "\" & rCell.Offset(, 1) & "\"
    Hope this helps
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  5. #5
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    Thanks Georgiboy, so I replaced DestFoldFull = DestFold & rCell & "" with DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "" this gives me a runtime error '76' path not found. Debug refers to MkDir DestFoldFull

    Thank you

    Quote Originally Posted by leemcder View Post
    Thank you for taking the time to reply. I have tried this and the folder name has not changed, apologies if I'm missing something, should I be changing anything else?

  6. #6
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location
    I've worked it out, column B is a date and it doesn't like date format with a / as can't save a file with this. Works perfectly with a .

    Many thanks for your help! Much appreciated

    Quote Originally Posted by leemcder View Post
    Thanks Georgiboy, so I replaced DestFoldFull = DestFold & rCell & "" with DestFoldFull = DestFold & rCell & rCell.Offset(, 1) & "" this gives me a runtime error '76' path not found. Debug refers to MkDir DestFoldFull

    Thank you

  7. #7
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    774
    Location
    Glad you worked it out, thanks for posting the fix.

    George
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

Posting Permissions

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