How to Loop?

[Cinema]

Hi,

in the third row of my Excel Sheet there are CSV file names listed ("B3:S3"). In the Range("E1") is the path where the CSV files are. Now I want a code that does the following: 1) If there is a csv file in the third row then open it
2) Do some copy and paste (that part is unimportat at the moment)
3) Close this CSV file
4) Go to the next CSV file and do the same as in 2)

I tried to write the code, but it just opens the first CSV and ignores the rest.

Sub Makro1()

Dim tool As Workbook
Dim daten As Workbook
Dim ws As Worksheet
Dim fpath As String
Dim fname As String
Dim such As String
Dim rngt As Range
Dim Counter As Integer
 
Set tool = ThisWorkbook
Set ws = tool.Worksheets("Sheet1")

fpath = tool.Sheets("Sheet1").Range("E1").Value
 

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ws.Select
For Counter = 1 To 17
such = Cells(3, 1 + Counter).Value
If such Like "*.csv" Then
Set daten = Workbooks.Open(fpath & such, ReadOnly:=True) 
   
End If
Next Counter

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[SamT]

Makro1 is complete in this, You just have to code WorkOnCSV

Option Explicit

Sub Makro1()
     
    Dim fpath As String
    Dim Cel As Range
    
   fpath = Sheets("Sheet1").Range("E1") ' insure fpath ends with "\"    
     
    For Each Cel In Range("B3:S3")
        If InStr(Cel, ".csv") Then WorkOnCSV fpath, Cel
    Next Cel
    
End Sub

Private Sub WorkOnCSV(fpath As String fnameCell As Range)
 '
 'more code here
 '
     Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

            Set daten = Workbooks.Open(fpath & fnameCell)
'
'more code here
'
'
 Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

[Cinema]

that is it! Thank you

[Cinema]

Hi SamT,

there is one more thing that troubles. I want to use the same variable "Cel" (from Makro1) in Privat Sub WorkOnCSV. How can I use it?

[SamT]

It is already the same thing, just different name.

Edit "fnameCell" to "Cel"

[Cinema]

Thank you SamT