Excel 2013 - Scheduling export of data from 1 workbook to another every 4 hours

Hi there,

Thank you for your time.

I am not sure if what I am after is possible, it a concept that I have been presented with as a solution for auditing purposes and KPI compliance.

I currently have a workbook (daily log) where dispatchers enter in the time of an event and other details based on feedback from patrols. This Daily Log is in use 24/7.

In order to encourage timely entry of data, and integrity of previously entered data, I would like to move rows of data from the Daily Log workbook and move the data to a 'Master' workbook every 4 hours, whilst the Daily Log is still open. The Master workbook is to be password protected and closed after the import of data.

I have a Daily Log Workbook, that is kept for the current Month and Year entries (which by the end of the month is a minimum of 1000 rows of data and 100 columns of data, approx 4-5 MB in size). When the new month starts, then a new Daily Log for the next months events is created based on a Excel Template. I'd would like a new Master Log Workbook for each Month and year.

Is it possible to use VBA to program such a schedule based on the time of the last 'TIME' entry of the previous row?

I have searched the internet to see if I could find something similar that I could perhaps modify, but no matter how I have worded it ... i haven't been able to come up with anything, even by breaking down the tasks.

I am open to perhaps any other solutions that would provide a similar outcome.

If you need any further information, please don't hesitate to contact me.

I really appreciate your time and possible solution.

Kind regards,
Shybutterfly

Hi Richard,
Thank you for your very quick response I really appreciate it.

I sort o get the gist of what you are saying (in relation to the 'Cloud') and appreciate your suggestion.

The bottom line is that only 1 person is working on the document at any given time (1 dispatcher), and by moving the data to a 'master' monthly log, and password protecting it, it stops the dispatcher from changing previously entered details - they have a KPI which states the data entry must be completed within a certain time (say 4 hours of being entered). If they haven't completed the details, they will need to contact the Supervisor and explain why they hadn't updated the details within the 4 hours. It is a method of monitoring if the dispatcher is meeting KPIs or not.

Isn't VBA capable of scheduling a data export (completely moving the data from the daily log and moving it to the next available free row in a Master workbook)?

Kind regards,
ShyButterfly

Thank you Richard, again, for your speedy response.

I'll try to further clarify the process in response to the points and questions you've raised

In response to your Questions:
1. Actually, to err on the side of caution, I will revert back to my original idea of having the rows LOCKED (based on the 4 hour criteria) and then copied to the Master Log workbook - because if there is a break in the link to the Master (IT moving/renaming folders ) then there is a possibility of loosing data etc. It also allows the Dispatch to review events of his shift.
2. I understand that the Code for the Master Log workbook will need to be contained in the Daily Log workbook. The developer tab will not be visible and the level of knowledge of excel is very basic and have no idea, so I don't believe a password to get into the VBA is necessary.
3. The idea of copying the data into a separate workbook is because the file will get very large and previous experience has shown that Excel is likely at some point to corrupt the workbook once it gets past the 5-6MB size. Excel handles the file well under the 6MB. Another consideration is also, the larger the file, the likelihood that the file will start to lag in performance (become less responsive).

Further clarification:
1. The Daily Log is pretty much continuously open, and it is the Dispatcher who enters the time based on when a call comes through, this is not VBA generated.
2. The Dispatcher completes the details of the call as information comes in and saves the open file, which remains open.
3. The Dispatchers have strict instructions to not sort the file, which they have followed. (Password protection to prevent sorting, would that effect/conflict with the VBA code?)
4. The structure of the sheet is that the new entry is added to the next line of empty row, working down the sheet. So it would need to lock and copy the data starting from row 2 and down to the period that meets the '4 hours' cut off.
** Just as an aside in case it has any bearing on the proposed process - there are sheet has multiple excel functions and formulas (VLookups, etc - non VBA), calculations of time
differences from time of call, to dispatch, to on site and leaving site.

I would be able to work out how to transfer the data, but the coding to setup the scheduling of the 4 hour criteria based on the Time (24hr clock) in column 'K' of the preceding row, I have not a clue how to code that.

So in summary, we are not deleting the contents of the Daily Log file, but just locking the rows and copying it to a separate Master Log workbook (password protected)

Is the information a little more clearer than my first attempt? I hope so

Thank you again for your time
Cheers,
ShyButterfly