How To Loop Thru Many Excel Spreadsheets & Add Text in A Cell?

[bmccollum]

I have a collection of 500 dynamically-generated Excel spreadsheets that reside in 500 uniquely-naked folders... 1 spreadsheet per folder.

I'd like to try to use AutoIt to dynamically loop through all of those folders and open each spreadsheet (with AutoIt somehow being able to navigate through all of the randomly named folders/files without me specifically having to indicate what all of the names are). I'd like for the process to find the last row of data in each Excel file and insert the red/bolded text of "Proprietary Info" in cell "A" of each spreadsheet, 2 lines below the last row of data in each spreadsheet.

So if 1 spreadsheet has 200 rows of data, I need AutoIt to dynamically determine that "Proprietary Info" should be inserted into cell "A202" and similar for each spreadsheet.

Has anyone else done this successfully? I've done a ton with AutoIt but nothing to this point like what I'm trying to accomplish here. Thank you in advance for any info any of you might be able to provide.

[kylomas]

bmccolumn,

It sounds harder than it is.

What does this mean?

500 uniquely-naked folders

 

1 - Are the folders under another folder or under the root folder? 

2 - Is the spreadsheet the only .XL* type file in the folder?

The trick is to get _FileReadToArray* to create a list of workbooks you want to operate on.  The rest is gravy...

kylomas

[water]

Maybe "uniquely-named"? A typo

[water]

_FileListToArrayRec should fill an array with all the Excel workbooks and directory information (if needed).

The functions in the Excel UDF should do the rest.

_Excel_RangeWrite writes the data to your Excel sheet. Use UsedRange.Rows to get the last used row of each sheet.

To set color and boldness you have to use the Range object and do it yourself (but it's quite easy).

If you have problems we will be happy to assist.

[bmccollum]

Sorry. Ha! Typo. Uniquely-named, not uniquely-naked.

The 500 folders I mention are all under a main folder such as:

C:tempABCCompanyExcelFile1.xls

C:tempDEFCompanyExcelFile2.xls

C:tempGHICompanyExcelFile3.xls

Etc...

[kylomas]

Hmmm...the "k" is right above the "n", D'Oh!

So would you say that you want to operate on all .XLS files under c:temp?

[bmccollum]

Second question you had regarding # and type of files in each of these unique folders... each of the 500 folders has a single files in each of them. No other files... just a single .xls file in each of the 500 folders.

[water]

First thing for you to do:

Use _FileListToArrayRec to grab the list of Excel-files.

[bmccollum]

Kylomas:

I'd want to operate on all .xls files under each SUBFOLDER under the main "c:temp" folder. If someone else has arbitrarily put some Excel files directly in "c:temp", I don't want to do anything with those. I'd only care about what's in "c:tempABCCompany", "c:tempDEFCompany", etc.

[kylomas]

Then your task is easy.  Follow the procedure water laid out.  Post back if any questions... 

[bmccollum]

Wonderful!  I believe I have enough of a point in the right direction to tackle this.  Thank you to the several of you that posted replies and suggestions as to how to accomplish this desired task.  Have a great weekend!