Pull files from folder

[jkwleisemann]

I'm trying to sort through and import a number of bar-delimited text files into an Excel spreadsheet, and running into trouble with the Dir function.

My code follows:

Option Explicit
Dim wbMonthly, wbYTD As Workbook
Dim strFileName, strType, strPrd, strFile As String
Dim lngBlank As Long

'Subroutine to load files from folder "\\vm165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files", check if file is GL,
'check if file has already been converted, and load data to first blank row.

Sub YTDLoader()
    'When starting, clear all shading from spreadsheet. Shaded rows indicate newly loaded rows,
    'while unshaded rows have already been processed and loaded to monthly files.
    
    Worksheets(1).Cells.Interior.Color = xlColorIndexNone

    'Cycle through files in Text Files folder
    On Error Resume Next
    strFileName = Dir("\\VM165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files\")
    If strFileName = Error Then strFileName = ""
    While strFileName <> ""
        If strFileName Like "*GL*" Then                         'Check if GL File
            If Not strFileName Like "*Conv*" Then               'Check that file not converted previously
                lngBlank = Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
                With ActiveSheet.QueryTables.Add(Connection:= _
                    "TEXT;\\vm165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files\" & strFileName _
                    , Destination:=Range("$A$" & lngBlank))
                    .CommandType = 0
                    .Name = strFileName
                    .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, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
                        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh BackgroundQuery:=False
                End With
            End If
        End If
        strFileName = Dir("")
        If strFileName = Error Then strFileName = ""
    Wend
End Sub

The problem I'm running into is this:

When I use the Dir("") function, it returns the first file in my home network directory. But when I put the file path in it, it keeps returning the first file in that folder, without iterating deeper into the folder.

I've gotten this to work before, but for the life of me I can't remember how to get it to work differently than what I've written above. I'm working in Office 2010, if that helps.

Help?

 

[Fishboy]

I'm trying to sort through and import a number of bar-delimited text files into an Excel spreadsheet, and running into trouble with the Dir function.

My code follows:

Option Explicit
Dim wbMonthly, wbYTD As Workbook
Dim strFileName, strType, strPrd, strFile As String
Dim lngBlank As Long

'Subroutine to load files from folder "\\vm165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files", check if file is GL,
'check if file has already been converted, and load data to first blank row.

Sub YTDLoader()
    'When starting, clear all shading from spreadsheet. Shaded rows indicate newly loaded rows,
    'while unshaded rows have already been processed and loaded to monthly files.
    
    Worksheets(1).Cells.Interior.Color = xlColorIndexNone

    'Cycle through files in Text Files folder
    On Error Resume Next
    strFileName = Dir("\\VM165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files\")
    If strFileName = Error Then strFileName = ""
    While strFileName <> ""
        If strFileName Like "*GL*" Then                         'Check if GL File
            If Not strFileName Like "*Conv*" Then               'Check that file not converted previously
                lngBlank = Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
                With ActiveSheet.QueryTables.Add(Connection:= _
                    "TEXT;\\vm165\ESDFinanceSTARFTP\FY2016 00YTD\Text Files\" & strFileName _
                    , Destination:=Range("$A$" & lngBlank))
                    .CommandType = 0
                    .Name = strFileName
                    .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, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
                        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh BackgroundQuery:=False
                End With
            End If
        End If
        strFileName = Dir("")
        If strFileName = Error Then strFileName = ""
    Wend
End Sub

The problem I'm running into is this:

When I use the Dir("") function, it returns the first file in my home network directory. But when I put the file path in it, it keeps returning the first file in that folder, without iterating deeper into the folder.

I've gotten this to work before, but for the life of me I can't remember how to get it to work differently than what I've written above. I'm working in Office 2010, if that helps.

Help?

Hi jkwleisemann, welcome to the boards.

Try changing the WEND at the end to LOOP

 

[WaterGypsy]

I think it should be Dir() not Dir("")

 

[jkwleisemann]

Hi jkwleisemann, welcome to the boards.

Try changing the WEND at the end to LOOP

That broke it because the "Loop" didn't have a "Do" to go with it, however -

I think it should be Dir() not Dir("")

This worked perfectly, thanks! That'll teach me to trust MS Help boards.

 

[Fishboy]

I think it should be Dir() not Dir("")

In fact I think it can just be Dir without any suffix at all

 

[Fishboy]

That broke it because the "Loop" didn't have a "Do" to go with it

Ah, in my own code like this my line is Do While strFileName <> "" and not just While strFileName <> ""