I keep getting Run-time Error 1004 application-defined or object-defined error for the lines highlighted in green and "run-time error '1004' Excel cannot find the text file to refresh this external data range. etcetc" highlighted in red. The thing is, that line of code came from when I was recording a macro and I have no idea how to modify it. I know the code is really clunky, but this is the best i'm able to come up with haha, suggestions are appreciated. Basically what I am trying to do is pull out data from the same table found in multiple documents and put it in an excel file. Can't seem to do that directly with word so I have batch converted the word docs into text files and am now importing the text files into excel so its easier to extract data from the cells.
Sub importtxt()
'
' importtxt Macro
'
Dim rngFileNames As Range: Set rngFileNames = Sheets("Sheet1").Range("I2", 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:=False
End With
Sheets("Sheet3").Select
EmptyCellA = Cells(Rows.Count, "A").End(xlUp)
EmptyCellB = Cells(Rows.Count, "B").End(xlUp)
EmptyCellC = Cells(Rows.Count, "C").End(xlUp)
EmptyCellD = Cells(Rows.Count, "D").End(xlUp)
Sheets("Sheet2").Select
For i = 2 To 1000
Range("A7").Copy Destination:=Sheets("Sheet3").Range("EmptyCellB")
Range("A11").Copy Destination:=Sheets("Sheet3").Range("EmptyCellA")
For j = 26 To 51
Range("A" & j).Copy Destination:=Sheets("Sheet3").Range("EmptyCellC")
j = j + 4
Next j
For k = 27 To 52
Range("A" & k).Copy Destination:=Sheets("Sheet3").Range("EmptyCellD")
k = k + 4
Next k
Cells.Select
Range("B6").Activate
Selection.QueryTable.Delete
Selection.ClearContents
i = i + 8
Next i
Next FileNameCell
End Sub
Bookmarks