Open multiple files one at a time from s list

[bmueller]

I have approximately 4000 files I need to open and then copy/paste the contents into an Excel file (at the last empty cell of column A) so that all of the contents of each

file ends up in one Excel file. Any help would be much appreciated.

[rberq]

I don't quite understand.

(1) Do you want each file to be pasted into its own cell, so you will be adding 4000 cells to column A of the one spreadsheet?

(2) Or do you want all 4000 files concatenated into a single file, adding only one cell to the spreadsheet?

(3) Or do you want to end up with 4000 spreadsheets each representing one of the original files?

(4) Do you want to paste into the last empty cell of column A, or into the first empty cell?

[bmueller]

Thanks for responding.

I need to:

* open the first file (.doc extension)

* copy the contents

*close the doc file

*paste the contents of the clipboard into the first empty cell of column A in an Excel file

*repeat for each .doc file in the folder

 

So I will end up with all of the contents of each file in column A of a single Excel file. From there I can manipulate the contents of the Excel file with some basic text to columns and other Functions.

[rberq]

Here's a brute force method:

 

In the Macro Express "Help" screens, look up the Repeat With Folder command.

Each time through the repeat loop your macro will be passed a file name (with full path if you checkmark the box).

The macro can then use Program Launch to start Microsoft Word (once for each file name), passing the .doc file name as the parameter. You will have to look up just what format the parameter takes -- Google is your friend for this. If there are embedded spaces within the file path and name the whole thing will probably have to be bracketed by double-quotes.

After launching Word, the macro can issue keystrokes to highlight all and copy to clipboard and then exit from Word.

Macro then can Alt-Tab to Excel, paste, and ENTER to drop down to the next cell in the column.

The macro will then go through all of the above for the next file, and so on. The Repeat With Folder loop will end itself when there are no more files.

(For the first time through, just manually position Excel by mouse clicking on the first open cell, prior to starting the macro. Or if you want to get fancier and figure out how to position to the open cell, go ahead, and let us know how you did it.)

 

Your biggest problem here will likely be timing -- copy to clipboard, and pasting, and Alt-Tabbing between applications, can take unpredictable amounts of time that may vary widely between one file and the next. You may want to insert some Wait Time Delays between steps, or Wait For Window Title, or both.

 

Good luck, let us know how it comes out or if you run into snags.

[amonaghan]

Depending upon which version of MS Word you are using, you could try the method in my link to merge all the .doc files into one long file then Ctrl+A to select all then paste it into Excel from the clipboard. Depending on the contents of each original file of course. If there is more than 1 line of text in the original documents, they will be given more than one cell in Excel though.

 

Link here: https://support.microsoft.com/en-us/kb/2665750