Using VBA to open files in a folder when you don't know the file names.

[andrewsn]

Hi everyone. I'm trying to use VBA to open files, perform some calculations, and then move on to the next file in a folder. Is there a way to do this with just the folder name and not the individual file names?

For example, I am in the folder "C:\Users\andre_000\Desktop\Correlation Files\"

Then I need to open the file "Russell 1000 Correlation Matrix_V7_20020122.xls"

The next file I need to open is "Russell 1000 Correlation Matrix_V7_20020915.xls"

So as you can see, the last 8 digits on the end of the file name will change, but I do not know always know what they will be. Is there any way to just point VBA to the folder I'm looking through and have it loop through every file in that folder? Also, is there a way to access the file name so that I can take those last 8 digits and store them in a string?

I've got the rest of my code written but I just do not know how to open files without knowing the full file path.

 

[Norie]

Are they the only Excel files in the folder?

If they are you can try something like this.

Dim wbMatrix As Workbook
Dim strFileName As String
Dim strPath As String
Dim strExt As String

      strPath = "C:\Users\andre_000\correlation Files\"
      strExt = "xls

      strFileName = Dir(strPath & "*." & strExt

      While strFilename<>""

           Set wbMatrix = Workbooks.Open(strPath & strFileName)

            ' do stuff with workbook

           wbMatrix.Close SaveChanges:=True

            strFilename  =Dir

      Wend

 

[VoG]

Try like this

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\andre_000\Desktop\Correlation Files"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    MyFile = Dir
Loop
End Sub

 

[andrewsn]

Thank you guys. I'll try to implement this right now.

 

[tusharm]

For an approach that "black boxes" the search, see Process all files in a folder

Hi everyone. I'm trying to use VBA to open files, perform some calculations, and then move on to the next file in a folder. Is there a way to do this with just the folder name and not the individual file names?

For example, I am in the folder "C:\Users\andre_000\Desktop\Correlation Files\"

Then I need to open the file "Russell 1000 Correlation Matrix_V7_20020122.xls"

The next file I need to open is "Russell 1000 Correlation Matrix_V7_20020915.xls"

So as you can see, the last 8 digits on the end of the file name will change, but I do not know always know what they will be. Is there any way to just point VBA to the folder I'm looking through and have it loop through every file in that folder? Also, is there a way to access the file name so that I can take those last 8 digits and store them in a string?

I've got the rest of my code written but I just do not know how to open files without knowing the full file path.

 

[Instantaneo]

Hi all,

All the solutions and the similar ones I found over the internet aren't working in my case. I have a couple of files in a folder, that I open individually, change their structure and save. Initially I thought the problem was having different input and output folders, but the same problem happens with the same folder.

So, this works:

Sub test()
valor = 1
Do While file <> ""
        MsgBox valor
        valor = valor + 1
        file = Dir
Loop
End Sub

... but this doesn't (it only processes the first file):

Sub Macro()


Dim MyObj As Object, MySource As Object, file As Variant
   
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim Sheetname As String
Dim path_file As String
Dim folder As String
Dim filename As String


folder = ThisWorkbook.Worksheets("ControlPanel").Range("D10").Value
file = Dir(folder & "*.csv")


numfiles_total = 0
numfiles_i = 0


'Application.ScreenUpdating = False


    Do While file <> ""
       numfiles_total = numfiles_total + 1
       file = Dir()
    Loop
    
file = Dir(folder & "*.csv")
valor = 1


    With ThisWorkbook
    
        Do While file <> ""
        
        MsgBox valor
        
            ThisWorkbook.Worksheets("ControlPanel").Range("A14") = "Files processed: " & numfiles_i & "/" & numfiles_total


            ThisWorkbook.Worksheets("ControlPanel").Range("F15").Value = file


            ThisWorkbook.Worksheets("data").Select
            Cells.Select
            Selection.ClearContents


            path_file = folder & file


            ThisWorkbook.Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "data_temp"


            With ThisWorkbook.Worksheets("data_temp").QueryTables.Add(Connection:= _
                "TEXT;" & path_file _
                , Destination:=Range("$A$1"))
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 1252
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
                .TextFileDecimalSeparator = "."
                .TextFileThousandsSeparator = ","
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With


            ThisWorkbook.Worksheets("data_temp").Range("A:A").Select
            Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
                TrailingMinusNumbers:=True


            ThisWorkbook.Worksheets("data_temp").Range("B:B").Select
            Selection.Replace What:="000", Replacement:="", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False


            ThisWorkbook.Worksheets("data").Range("A:F").Clear
            ThisWorkbook.Worksheets("data_temp").Range("A:F").Copy Destination:=ThisWorkbook.Worksheets("data").Range("A:F")
            Application.DisplayAlerts = False
            ThisWorkbook.Worksheets("data_temp").Delete
            Application.DisplayAlerts = True


            ThisWorkbook.Worksheets("data").Columns("A").ColumnWidth = 12
            ThisWorkbook.Worksheets("data").Columns("B:B").NumberFormat = "0"


            totalrows = WorksheetFunction.Count(.Worksheets("data").Range("A:A"))


            For i = 1 To totalrows
                ThisWorkbook.Worksheets("Calculation").Range("A" & i).Value = ThisWorkbook.Worksheets("data").Range("A" & i).Value & "," & ThisWorkbook.Worksheets("data").Range("B" & i).Value & "," & Replace(ThisWorkbook.Worksheets("data").Range("D" & i).Value, ",", ".") & "," & Replace(ThisWorkbook.Worksheets("data").Range("C" & i).Value, ",", ".") & "," & ThisWorkbook.Worksheets("data").Range("E" & i).Value
            Next i


            newfiletxt = folder & Replace(file, ".csv", ".txt")
            newfilecsv = folder & file


            Open newfiletxt For Output As #1
            For i = 1 To totalrows
                Print #1, ThisWorkbook.Worksheets("Calculation").Range("A" & i)
            Next i
            Close #1


            If Not Dir(newfilecsv) = "" Then
                Kill newfilecsv
            End If


            Name newfiletxt As newfilecsv


            Call ClearTextToColumns
            numfiles_i = numfiles_i + 1
            
            valor = valor + 1
            file = Dir
        Loop


    End With
    
    MsgBox "All files were processed correctly!", vbInformation
    
'    Application.ScreenUpdating = True


End Sub

What am I doing here that is jeopardizing the Dir function?

Thanks for all suggestions.