I wrote the following code to import text data and fill in an excel database with the data from the imported text file. The text file is then deleted, a new text file imported and the cycle repeats. However I'm getting a Compile error: Invalid Next control variable reference at Next FileNameCell How can I fix this? Thanks!
![]()
Sub importtxt() ' ' importtxt Macro ' Dim rngFileNames As Range: Set rngFileNames = Sheets("Sheet1").Range("I1", Cells(Rows.Count, "I").End(xlUp)) Dim FileNameCell As Range For Each FileNameCell In rngFileNames If FileNameCell.Value <> vbNullString Then Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Documents and Settings\wancinlee\Desktop\SOR\SOR_txt" & FileNameCell.Value & ".txt" _ , Destination:=Range("$A$1")) .Name = "SOR_" & FileNameCell.Value .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=True End With Sheets("Sheet2").Select For i = 2 To 1000 Range("A7").Copy Destination:=Sheets("Sheet3").Range("B" & i) Range("A11").Copy Destination:=Sheets("Sheet3").Range("A" & i) Range("A23").Copy Destination:=Sheets("Sheet3").Range("C" & i) Range("A24").Copy Destination:=Sheets("Sheet3").Range("D" & i) Cells.Select Range("B6").Activate Selection.QueryTable.delete Selection.ClearContents Next FileNameCell End Sub
Bookmarks