Number of files

[melmac82]

How can I find out the number of files in a folder when coding in VBA? I need to use the number of files that I have as the bound on a loop in the code, and the number of files in the folder can vary so I can't just count them.

 

[melmac82]

bump

 

[Norie]

How/why to you need to use it in the loop?

 

[melmac82]

I am pasting one column from each file into one worksheet, so each time through the loop I need to increment to the next column.

 

[Norie]

So you don't actually need to know the no of files then?

You just need to loop through all the files in the folder.

 

[melmac82]

But how do I know what column to put them in then. I am using Offset(1, col) where col is the number of files that I have. Is there a way to put the info into consecutive columns without needing to tell it the column number?

 

[Norie]

Just set col to 1 and increment it as you loop through the files.

col=col+1

PS It would be helpful if you posted your code.

 

[rafaelspeixoto]

You might find some useful information about files in a folder in this page: http://www.contextures.com/xlfaqMac.html#LoopBooks
(just in case you still wanto to learn how to work with workbooks in a folder)

 

[melmac82]

Here's my code that I have currently, where I have set the number of files to 20 for the loop just as an example.

Sub getfiles()

Dim col As Integer

strpath = "H:\..."
Strfile = Dir(strpath & "*.csv")

Do While Strfile <> ""
For col = 0 To 20
Workbooks.Open (strpath & Strfile)
Columns("b:b").Copy
ActiveWindow.Close

Workbooks("test3.xls").Activate
Sheets("Sheet1").Activate
Range("A1").Offset(1, col).Select
ActiveSheet.Paste

Strfile = Dir()
Next col
Loop

End Sub

 

[Norie]

Why do you need the For loop?

Sub getfiles()
Dim wbThis As Workbook
Dim wbOpen As Workbook
Dim col As Integer
    
    strpath = "H:\..."
    Strfile = Dir(strpath & "*.csv")
    col = 0
    Set wbThis = ThisWorkbook
    
    Do While Strfile <> ""
        
        Set wbOpen = Workbooks.Open(strpath & Strfile)
        
        wbOpen.ActiveSheet.Range("B:B").Copy wbThis.ActiveSheet.Range("A1").Offset(, col)
        
        col = col + 1
        
        wbOpen.Close
    
        Strfile = Dir()
    
    Loop
    
End Sub