0

Need assistance with figuring out why my code is not looping through my folder. It loops the same text file over and over again. When I put a break in and run the code, it appears that the next file in line is correct, but it opens the prior file.

     Sub MikesMacro()

     Dim strFile As String
     Dim intNumberOfFiles As Integer
     Dim wbText As Excel.Workbook
     Dim path As String

     path = "C:\Users\MStarks\Desktop\Cincy Data Edits\PULSE IMPORTS\"

     strFile = Dir(path & "*.TXT")

         Do While Len(strFile) <> ""

         Workbooks.OpenText Filename:=(path & "*.TXT") _
         , DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(Array(1, 1), _
         Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))

         Set wbText = ActiveWorkbook

         'Starts the macro used to set up format the Telog likes
         'Macro not included
         'start save as .CSV cycle

     strFile = Dir

     Loop

     End Sub
K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
Starks
  • 1
  • 1
  • 1
    `Filename:=(path & "*.TXT")` is inside while loop and the value of path is never being updated. Use `strFile`instead. I wonder even that open statement is working at all. – cyboashu Feb 05 '18 at 18:13
  • Needed to remove the .TXT from `Filename:=(path & "*.TXT")` and add to path & strFile. So final code ended up being `Filename:=(path & strFile)`. – Starks Feb 05 '18 at 18:45

1 Answers1

0

How about this?

Sub LoadPipeDelimitedFiles()
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\*.txt")
    Do While xFile <> ""
        xCount = xCount + 1
        Sheets(xCount).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & xStrPath & "\" & xFile, Destination:=Range("A1"))
            .Name = "a" & xCount
            .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 = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            xFile = Dir
        End With
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox("no txt files")
End Sub

https://www.extendoffice.com/documents/excel/3388-excel-import-multiple-text-csv-xml-files.html

ASH
  • 20,759
  • 19
  • 87
  • 200