Hi there Friday!

Happy Friday!

Nice day at work so far! working on this timesheet application and database, have finally sorted out the SQL code to pick out the rolling 12 month data! 

rs.Open "SELECT * FROM Timesheet WHERE Area = '" & Area & "' and Date BETWEEN #" & startdateroll & "# AND #" & enddateroll & "#", cn, , , adCmdText

didnt have the hashtags in my starting code but due to being an access database need to use # and not ' for a date range, who knew! 

But now the data is coming across based on 2 cell values

Start date for the rolling 12 months 

=TEXT(EOMONTH(TODAY(),-13)+1,"dd-mmm-yy")

End date for the rolling 12 months 

=TEXT(EOMONTH(TODAY(),-1),"dd-mmm-yy")

This is now working like a charm for the automation! 

When the button clicked to refresh the data, the code heads to the DB finds all rows between the two dates, finds the correct user and department for the correct graph then update all pivot tables and charts in the workbook

For Each ws In ActiveWorkbook.Worksheets

n = 0

For Each PvtTbl In ws.PivotTables

n = n + 1

Next

If n >= 1 Then

For i = 1 To n

ws.PivotTables(i).PivotCache.refresh

Next i

End If

Next

So now, on the Graph page, have last months time sheet data in one graph and then a up to date rolling 12 month graph and all the data will be live from the database in the background! 

Now to head out for lunch and come back tidy up all the graphs and data structure within the excel sheets, sort the permissions out for each user and this should be ready for some testing! 

Heading to Bens tonight to carry on the DnD game we have going on! looking forward to it! will no doubt blog about it later!