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