Loop through a folder using a mutiple lists

[Legacy 93538]

(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?

 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

 

[JackDanIce]

I think you need to use a SELECT-CASE statement

Have a read of:
http://www.ozgrid.com/VBA/select-case.htm

 

[Legacy 93538]

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]

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:

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

 

[Legacy 93538]

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?

 

[JackDanIce]

Sorry, I meant inside the DO LOOP (I used the FOR NEXT LOOP instead), i.e:

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)

 

[Legacy 93538]

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]

I don't think you have your SELECT CASE set up correctly... hoping someone else on the board can answer though!

 

[Legacy 93538]

Thats is most likely i am not very good at Select Case statements....i have never really used them

 

[JackDanIce]

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:

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