[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Hello Gurus

Hello All,

i have a VBA question  -

A)      These are 3 folders in a drive with folder names following the naming convention like below…

C:\WW\2014.09.09 DA 10 XLS
C:\WW\2014.10.09 DA 11 XLS
C:\WW\2014.11.12 DA 12 XLS

----------------------------------------------------------------------------------------------------

B)      Now there is an excel file (“MAIN”) that has info like below – this basically tells which account belongs in which cycle respectively….

Account      | Cycle
Ac1      |         10
Ac2      |         10
Ac3      |         11
      

-------------------------------------------------------------------------------------------------
C)      Now in a certain separate folder  (C:\RAW\) - there are multiple xls files (specific to accounts) with naming convention such that it has the [account] names included in their filename like this:

Fl_Ac1.xls
fl_Ac2.xls
fl_Ac3.xls

----------------------------------------------------------------------------------------------------
I need VBA to do the following – what macro can I give in the file of step B so that on pressing the button, it loops through the multiple files in folder C:\RAW\ and then looks up the account name in list of sheet A (step B) and then locates the cycle ID, to finally put that file in the respective destination folder in C:\WW\ ---

For example – if the code loops through the file   fl_Ac1.xls – it looks up the cycle ID for Ac1 in “MAIN” file and finds that cycle is 10 – then it copies the file – fl_Ac1.xls into the folder  C:\WW\2014.09.09 DA 10 XLS folder….


Thank you
Rayne
Book1.xlsx
0
Rayne
Asked:
Rayne
1 Solution
 
luconstaCommented:
Hi,

You could try the following approach - because the file naming convention you've mentioned, you could go parsing the ACCOUNTS (not files) and then copy the files for each parsed account:

Sub SortAndCopy()

    Dim oCell, oRange As Range
    Dim cAcc, cCycle, cDstFolder As String
    
    'Set the range for accounts (ONLY A COLUMN)
    Set oRange = Worksheets("Sheet1").Range("A2:A5")
    
    For Each oCell In oRange
        cAcc = oCell.Value
        cCycle = Range("B" & oCell.Row).Value
        Debug.Print cAcc & " / " & cCycle
        
        Select Case cCycle
            Case 10
                cDstFolder = "C:\WW\2014.09.09 DA 10 XLS"
            
            Case 11
                cDstFolder = "C:\WW\2014.10.09 DA 11 XLS"
                
            Case 12
                cDstFolder = "C:\WW\2014.11.12 DA 12 XLS"
        End Select
        
        On Error Resume Next
        FileCopy "C:\RAW\FL_" & cAcc & ".XLS", cDstFolder & "\FL_" & cAcc & ".XLS"
    Next oCell
End Sub

Open in new window

0
 
RayneAuthor Commented:
Thank you :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now