PDA

View Full Version : How to Loop?



Cinema
07-27-2016, 04:55 AM
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
07-27-2016, 10:01 AM
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
07-29-2016, 12:34 AM
that is it! Thank you

Cinema
07-29-2016, 02:58 AM
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
07-29-2016, 08:37 AM
It is already the same thing, just different name.

Edit "fnameCell" to "Cel"

Cinema
08-02-2016, 02:33 AM
Thank you SamT