+ Reply to Thread
Results 1 to 5 of 5

VBA to automatically populate Excel calendar with staff vacation (leave) data

  1. #1
    Registered User
    Join Date
    08-23-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    10

    Question VBA to automatically populate Excel calendar with staff vacation (leave) data

    Hi guys

    I would appreciate some help with a piece of Excel VBA please. Essentially, I'm creating a database of staff vacation (annual) leave. This is stored in the format below.

    tblLeave
    entry staff_id staff_name leave_start leave_end
    1 9991 Marie Smith 31/12/2012 04/01/2013
    2 9994 Richard Owen 28/01/2013 01/02/2013
    3 9991 Marie Smith 11/02/2013 12/02/2013
    4 9998 Jennifer Muller 14/01/2013 18/01/2013

    What I need is a piece of VBA code that will look at the dates (that will be extracted using a SQL string from a .mdb file) and will loop through the results, marking an "A" (for annual leave) against the member of staff's name and under the correct date on the attached calendar (I've used January 2013 for an example).

    Calendar Example.xls

    My real issue is how to get VBA to understand how to mark the days in between the start and end dates as "A" (especially if they span into another month).

    Any help or pointers would be much appreciated.

    Kind regards
    Paul

  2. #2
    Registered User
    Join Date
    08-23-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: VBA to automatically populate Excel calendar with staff vacation (leave) data

    *Bump* Can anyone offer any advice on this? Would be extremely grateful even for a partial answer or some pointers.

    Thanks
    Paul

  3. #3
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: VBA to automatically populate Excel calendar with staff vacation (leave) data

    Use the code below passing the start date, end date and employee name to the sub SetVacationDays. This is built from the format in the example workbook you posted.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-23-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: VBA to automatically populate Excel calendar with staff vacation (leave) data

    Thanks so much for your reply, Yraen.

    The only problem is that the GetColumnByValue function can't seem to 'find' the start and end dates. Could this be because the date string needs converting into a number before trying to find the value in the worksheet?

    Thanks again,
    Paul

  5. #5
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: VBA to automatically populate Excel calendar with staff vacation (leave) data

    It worked fine with the sample you had posted above. Can you show me exactly what is returned from your database? You might try wrapping the dates returned inside of a cstr, that might do it. If not, post the code and I'll look at it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1