AndrewWarhol

Automation/batching of month-based worksheets

4 posts in this topic

I have a worksheet X that I want to replicate over the course of a number of months, for at least two separate people. As in, I want to do something like this:

 (WORKSHEET X) http://s1124.photobucket.com/user/Vernancular/media/help.png.html     (WORKSHEET X)

Worksheet A represents an attendance sheet and I want to fill as many relevant rows for easy user usage. Right now, the green highlighted cells are fine (can simply be left as is) but rows A, B, C and E need to be altered.

A&B need to be altered based on user and C&E need to be altered based on month.

Since there are very few people for this to be replicated for, I will be creating a different Feb file for each user (i.e 1 Rachel, 1 Vanessa) as this will take care of A&B, and then running the same month-based script for each person to alter columns C&E.

Something like this: http://i1124.photobucket.com/albums/l573/Vernancular/Methods.png

This is not as easy as first appears, as there are filters applied to the worksheet, making autofill date impossible. As such I have setup a separate worksheet for months March-December illustrating the number of dates for each month. Let's call it sheet Y, it looks like this:

(WORKSHEET Y) http://s1124.photobucket.com/user/Vernancular/media/how.xlsx.png.html    (WORKSHEET Y)

I will be doing the following: 

1. batch the transfer of the relevant column from sheet Y to columns C&E of sheet X, and then 

2. Delete all items in row 32 (Entry for the 31st day of the month) for month files with only 30 days

3. Save the file with the corresponding name (i.e March column -> Rachel ATTENDANCE Mar 2016.xlsx, April column -> Rachel ATTENDANCE Apr 2016.xlsx, and so on)

4. Cycle the script until ended at Rachel ATTENDANCE Dec 2016.xlsx

How can this be done within excel? What macros should I be using? What examples can I refer to?

Share this post


Link to post
Share on other sites



What autoit and excel tools should I be using?* 

 

I can't seem to edit the main post. Sorry for the double post.

Share this post


Link to post
Share on other sites

you should write this completely in vba macro. No reason to use AutoIT for handling that.

you need filesystemobject to iterate thru your folder with xls files

you need workbooks.open to read thru each specific workbook and workbook("name").saveas ...

you need range object or cells(iRow,iCol).value to read data out of a cell

Share this post


Link to post
Share on other sites

AndrewWarhol,

welcome to AutoIt and the forum!
You need to have at least 5 or 10 posts to be able to edit your OP.
To get a list of files I suggest function _FileListToArrayRec.
If you want to do it with AutoIt you could use Excel UDF that comes with AutoIt.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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
Tutorials:
ADO - Wiki

 

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