Macro to open CSV docs wont stop looping?

[tonywatsonhelp]

Hi Everyone,
I have an excel document with a macro i need help with,

all I'm trying to do is open every CSV file in the folder where my excel document is saved and do the following.

"
ActiveSheet.Range("H1") = "ISRC"
ActiveSheet.Range("H2:H" & LR1).FormulaR1C1 = "=LEFT(RC[-2],2)" "

but im stuck in a continues loop?


heres my code,

Sub Open_Docs_v2()
   Dim Fname As String, Pth As String
   
   Pth = ThisWorkbook.Path & "\"

   Fname = Dir(Pth & "*.csv")
Application.ScreenUpdating = False
   Do Until Fname = ""
If Fname <> ThisWorkbook.Name Then
         With Workbooks.Open(Pth & Fname)


    LR1 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Range("H1") = "ISRC"
    ActiveSheet.Range("H2:H" & LR1).FormulaR1C1 = "=LEFT(RC[-2],2)"


            .Close True
         End With
End If
      Fname = Dir
   Loop
End Sub

please change or just give me a better way of doing it if there is one.
thanks
Tony

 

[EFANYoutube]

I have tested this and seem to work for me
I changed a couple of things

Sub Open_Docs_v2()

Dim Fname As String, Pth As String
Pth = ThisWorkbook.Path & "\"
Fname = Dir(Pth & "*.csv")
Application.ScreenUpdating = False
Do Until Fname = ""
    If Fname <> ThisWorkbook.Name Then
        With Workbooks.Open(Pth & Fname)
            LR1 = .Sheets(1).Cells(Rows.Count, "A").End(xlUp).row
            .Sheets(1).Range("H1") = "ISRC"
            .Sheets(1).Range("H2:H" & LR1).FormulaR1C1 = "=LEFT(RC[-2],2)"
            .Close True
        End With
    End If
    Fname = Dir
Loop
End Sub

 

[tonywatsonhelp]

Thank you Efan, i'll give it a try

 

[Carlos1019]

I have tested this and seem to work for me
I changed a couple of things

Sub Open_Docs_v2()

Dim Fname As String, Pth As String
Pth = ThisWorkbook.Path & "\"
Fname = Dir(Pth & "*.csv")
Application.ScreenUpdating = False
Do Until Fname = ""
    If Fname <> ThisWorkbook.Name Then
        With Workbooks.Open(Pth & Fname)
            LR1 = .Sheets(1).Cells(Rows.Count, "A").End(xlUp).row
            .Sheets(1).Range("H1") = "ISRC"
            .Sheets(1).Range("H2:H" & LR1).FormulaR1C1 = "=LEFT(RC[-2],2)"
            .Close True
        End With
    End If
    Fname = Dir
Loop
End Sub

Thanks so much!!! You have solved my problem!
Greetings from Spain