Loop through a folder using a mutiple lists

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
(Linked to previous posted VBA Loops not pasting in correct location)

Hi

I have created a piece of code which loops througha folder and opens all the files and copies and pastes data fom a range into a certain location. However i need to change it so when it loops the file it checks the names of the file and if has certain text in paste it into different sheets.

I need it to do the following:

If it is "Graphing_MTH_Actual_Curr_Year" & "*.csv" paste data on the MTH Sheet
If it is "Graphing_MTH_Actual_Prev_Year" & "*.csv" paste data on the MTHPrevious Sheet

If it is "Graphing_YTD_Actual_Curr_Year" & "*.csv" paste data on the YTD Sheet
If it is "Graphing_YTD_Actual_Prev_Year" & "*.csv" paste data on the YTDPrevious Sheet

If it is "Graphing_R12_Actual_Curr_Year" & "*.csv" paste data on the R12 Sheet
If it is "Graphing_R12_Actual_Prev_Year" & "*.csv" paste data on the R12Previous Sheet

I have tried doing it with multiple if statements but it just crashes. I would try variables but i am unsure of how to use the.

Does anyone know how to change this loop so it checks the file name on the list above and pastes onto the correct sheet?

Code:
 strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks\"
strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.csv")
Application.Calculation = xlCalculationManual
lNextrow = 2
If Len(strFile) > 0 Then
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("Book1Template.xlsx").Sheets("MTH")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("Book1Template.xlsx").Sheets("MTH").Cells(lNextrow, 2).PasteSpecial
                
        End With
            
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile = Dir
        Application.StatusBar = strFile
    Loop Until Len(strFile) = 0
End If

Thanks to anyone who can help.

Jessicaseymour
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
Hi

Thank you for replying......Looks like a option but i have never used select case statements so i am not sure how they work....could you explain how i would interrograte a Select Case into my code ....do i need to replace the loop with it? or place inside the loop?

Thanks so much for helping me
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Having a bit of a busy day so don't have time to provide a coded solution, but you'd need to put it inside your loop so it can evaluate each file it loops through to see if it fits one of the case statements.

Pysuedo code would be something like:
Code:
For Each File in my Folder
   Case File name
     begins with A do x
     begins with B do y
     begins with C do z
   End Case
Next File
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268

ADVERTISEMENT

Hi

Thank you for helping i really appricate it

But one question do i still use the do loop or just the if select case statement inside the if statement? and if so what do i replace the Loop Until Len(strFile) = 0 bit with?
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Sorry, I meant inside the DO LOOP (I used the FOR NEXT LOOP instead), i.e:
Code:
Do
   Case File name
     begins with A do x
     begins with B do y
     begins with C do z
   End Case
Loop Each File in my Folder
The difference between the two loop types is in a FOR NEXT loop, you have a defined number of iterations the loop cyles through (e.g. For 1 to 10). For a DO LOOP, you have an unknown number of iterations to loop through (e.g. DO something (like check if a cell is not empty) until you meet a condition that doesn't do that)
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268

ADVERTISEMENT

Code:
strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks\"
strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.csv")
strFile1 = Dir(strFldr & "Graphing_MTH_Actual_Prev_Year" & "*.csv")
strFile2 = Dir(strFldr & "Graphing_YTD_Actual_Curr_Year" & "*.csv")
strFile3 = Dir(strFldr & "Graphing_YTD_Actual_Prev_Year" & "*.csv")
strFile4 = Dir(strFldr & "Graphing_R12_Actual_Curr_Year" & "*.csv")
strFile5 = Dir(strFldr & "Graphing_R12_Actual_Prev_Year" & "*.csv")
Application.Calculation = xlCalculationManual
lNextrow = 2
If Len(strFile) > 0 Then
 Do
     Select Case 1 - 6
     Case 1
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH").Cells(lNextrow, 2).PasteSpecial
                
        End With
       
      'close it
        wbCsv.Close
        
     Case 2
          Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
            
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious").Cells(lNextrow, 2).PasteSpecial
                
        End With
        
      'close it
        wbCsv.Close
     Case 3
          Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
            
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD").Cells(lNextrow, 2).PasteSpecial
                
        End With
      'close it
        wbCsv.Close
     
     Case 4
          Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
            
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious").Cells(lNextrow, 2).PasteSpecial
                
        End With
      'close it
        wbCsv.Close
        
     Case 5
          Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
            
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12").Cells(lNextrow, 2).PasteSpecial
                
        End With
      'close it
        wbCsv.Close
     Case 6
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
            
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous").Cells(lNextrow, 2).PasteSpecial
                
        End With
      'close it
        wbCsv.Close
        
        lNextrow = lNextrow + 14
        
    'go to next file
        strFile = Dir
        Application.StatusBar = strFile
   End Select
  Loop Until Len(strFile) = 0
End If

Hii
I am have re-written it but i am not sure its right...i ran it and it kinda worked except when it got to the select case excel went into Not reponding mode

Have i written it wrong?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
I don't think you have your SELECT CASE set up correctly... hoping someone else on the board can answer though!
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
Thats is most likely i am not very good at Select Case statements....i have never really used them
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
This is a very quick example I've put where in cells A1:A5 I have the numbers 1, 2, 3, 4 and 5

Run this and see if it makes sense:
Code:
Sub Lemons()
Dim i As Integer
For i = 1 To 5
    Select Case range("A" & i).Value
        Case 1, 2
            range("B" & i) = range("A" & i) * 10
        Case 3, 4, 5
            range("B" & i) = range("A" & i) * 2 - 1
    End Select
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,901
Messages
5,598,766
Members
414,259
Latest member
beetle12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top