Jump to content

Automation/batching of month-based worksheets


Recommended Posts

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?

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

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 (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...