looping through all files in a folder, etc

**bored** · 01-25-2012, 07:29 AM

Hi,

Say I have a folder of 300 text files, named ‘1.txt’, ‘2.txt’, ‘3.txt’, and so on. I wish to use Excel to open these files one by one, do something inside, save the file, and close it. What I used to do for smaller folders (of a few files) would be to record a macro for one file, then copy+paste this macro as many times as there are number of files, and then edit each copy accordingly. But with so many files, this wont work anymore.

My question is, how do I write a macro to loop through all the files in the folder?

There is one other issue related to the above problem. Say, after manipulating all the files in the folder and saving them, I want to copy column F from each saved file and put that into another file, called ‘compile_F.xlsx’. As explained above, what I would do if the number of files is small, is to edit each segment of the code by hardwiring the specific cell to copy into, with codes like

Range("G7").Select
ActiveSheet.Paste

How can I write my macro to select consecutive (adjacent?) cell to replace this kind of hardwiring? I can record things like “Selection.End(xlToRight)).Select” but I cant seem to record cell selection via arrow pad.

Thanks in advance for any advice.

**Bmoe** · 01-25-2012, 07:52 AM

Hello,

To answer your first issue, looping through text files I use something

For fileIndex = 1 to 100
     Open fileIndex & ".txt" For Input (or output) As #1
     ...
     'All your manipulation code (Saves automatically)
     Close #1
Next fileIndex

For the second part, you can do something like

Dim fRange as Range
Dim copyCell as Range

Set fRange = Sheets("theSheet").Range("B1", Sheets("theSheet").Range("B1").End(xlDown)
For Each copyCell in fRange
    'Copy and paste operation here.
Next copyCell

Here, each time it loops it selects a cell one by one and you can do whatever you need to to it. copyCell.copy, copyCell.value... etc.

**bored** · 01-26-2012, 01:22 AM

Hi Bmoe,

thanks for your reply. i tried what you suggested, and also searched through the forum for further methods, but come up with syntax errors everywhere. basically, when i record what i do for 1 file into a macro, i get

Workbooks.OpenText Filename:="D:\myfolder\1", Origin:=1251, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _
        , 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
        TrailingMinusNumbers:=True
'some procedures
    ActiveWorkbook.SaveAs Filename:="D:\myfolder\1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close

how do i make use of what u have suggested to modify this macro, so that it can loop through all the hundreds of files in the folder? i think my main problems here are how i can open and save individual files without hardwiring the filenames. thanks!

Thread: looping through all files in a folder, etc

Thread Tools

Display

looping through all files in a folder, etc

Re: looping through all files in a folder, etc

Re: looping through all files in a folder, etc

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions