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
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  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
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Glad you worked it out, thanks for posting the fix.

    George
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

Posting Permissions

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