bmccollum

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

11 posts in this topic

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.

Share this post


Link to post
Share on other sites



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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Maybe "uniquely-named"? A typo :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

_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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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?


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

First thing for you to do:

Use _FileListToArrayRec to grab the list of Excel-files.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now