Help with VBA cell reference in loop

[yits05]

Hello - I have the following VBA which is supposed to loop through a list of files and import tables from them. I did most of this via recording a macro, but am having trouble editing it so that when it loops, it references the cell in the next row. I get an error when I try to run the below. The "filepath" variable contains the file extension from which I extract the tables, and which I would like to change as the VBA loops. Many thanks!

Dim reffile As Long
    Dim filepath As Range
    Dim i As Long
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    filepath = Range("A" & i)
    For i = 2 To FinalRow
        ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""" & filepath & """), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & "    Table001 = Source{[Id=""Table001""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Table001"
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""" _
            , Destination:=Range("$P$2")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table001__Page_1"
        ActiveWorkbook.RefreshAll
        Application.Wait (Now + TimeValue("0:00:03"))
        ActiveWorkbook.Queries("Table001 (Page 1)").Delete
        Range("O1:s50").ClearContents

    
    End With
    Next i

  
End Sub

 

[offthelip]

to get rid of the error just swap these two lines around;

   filepath = Range("A" & i)
    For i = 2 To FinalRow

should be

    For i = 2 To FinalRow
   filepath = Range("A" & i)

Also I don't think you should define filepath as a range it should be a variant or a string

 

[yits05]

to get rid of the error just swap these two lines around;

   filepath = Range("A" & i)
    For i = 2 To FinalRow

should be

    For i = 2 To FinalRow
   filepath = Range("A" & i)

Also I don't think you should define filepath as a range it should be a variant or a string

Thank you!