Looping through files in a folder

**pdb78** · 01-26-2012, 02:18 PM

Trying to loop through all files in a folder and copy and paste special values in two of the worksheets tabs. I know this has been discussed before but nothing I've tried seems to work. Below, is my most recent abomination in pursuit of saving time. The files names are not in any type of order.

Any help would be appreciated

Dim inputfile As String

inputfile = Dir("c:\myfolder\*.xls")

With Application.FileSearch
.NewSearch
.LookIn = inputfile
.FileType = msoFileTypeExcelWorkbooks

For i = 1 To .FoundFiles.Count
temp = .FoundFiles(1)
ActiveSheet.Columns("a:y").Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
Next i

End With
End Sub

Any help would be appreciated.

**tigeravatar** · 01-26-2012, 02:26 PM

pdb78,

Welcome to the forum!
In the future, please wrap your code in code tags per the forum rules (link in my sig).
As to your query, give this a try:

Sub tgr()
    
    Const strFldrPath As String = "C:\myfolder\"  'Make sure to include the ending \
    
    Dim inputfile As String
    inputfile = Dir(strFldrPath & "*.xls")
    
    Application.ScreenUpdating = False
    Do While inputfile <> vbNullString
        With Workbooks.Open(strFldrPath & inputfile)
            .Sheets(1).Columns("A:Y").Value = .Sheets(1).Columns("A:Y").Value
            .Sheets(2).Columns("A:Y").Value = .Sheets(2).Columns("A:Y").Value
            .Close True
        End With
        inputfile = Dir
    Loop
    Application.ScreenUpdating = True
    
End Sub

**pdb78** · 01-26-2012, 03:18 PM

Works perfectly!! Thank you soooo much. You've saved me hours and hours of work!!

Now perhaps you could help me understand some of the logic, so I can reason through other projects.

Do While inputfile <> vbNullString

How is this, as a counting devise,telling the loop that it is finished? Why doesn't it end in with an infinite loop if there are always files in the directory?

Also, the file are linked to other files. it seems they are not updating from their links so i'm saving #value signs into the cells. any suggestions to combat this?

Thanks again for your help. This is a huge time saver!

**tigeravatar** · 01-26-2012, 06:32 PM

pdb78,

inputfile is a string value, not a counter. You are using the Dir() method, which returns a string value of the next file in the directory. When there are no more files in the directory, Dir returns a length 0 string (or vbNullString). When that happens, the loop stops because it has reached the end of the list of files.

As to your second issue, I don't really have an answer, sorry