Hello all
This is my first post as a brand new (wannabe) Excel VBA programmer who is not much past the Hello World stage!
I am trying to automate a process we use in work where we cut and paste data from a .CSV database extract into separate Worksheets which are accessed and updated with results remotely, via a shared drive.
What I need to do is probably bread and butter stuff to many of you VBA experts out there but has me completely flummoxed!
What we do at the moment:
Data is extracted from a database (in .CSV format) for the last eight days once a week on a Tuesday. The reason we extract eight days of data is to ensure we capture any jobs which were created later on in the day after the last extract was taken. This creates a bit of a problem the VBA would need to address, in that it extracts duplicates of jobs already extracted too. The data is all jobs created on all dates for all department between the two dates specified during the extraction. The data is two columns containing a date in column A and a URN in column B. The date (Creation Date) is always in the form DD/MM/YYYY .The URN is always four letters and eight numbers. The first two letters of the URN are the Department Code. So for example, a typical URN (job number) for department CN would be "CNJB00345678"
The raw data is pasted into a worksheet called "New Results ALL 2016.xls" and then sorted by Date Created then URN. Starting from the first row of the data, we then copy columns A and B for all rows where the date equals a given month for a given department. Next we find and open the results worksheet for the department, select the appropriate month sheet (JAN, FEB etc) and then paste the data after the last entry. Finally we remove any new entries which duplicate existing entries by deleting the row.
Note to the above: In most cases there is only one month sheet to deal with but where the extract comes at the end of the month, it can span the end of one month and the beginning of the next.
All of this is very slow and laborious (there are in excess of 40 departments) and often leads to mistakes slipping in through fatigue.
What I am after is a Worksheet with a VBA routine which will automate the process something like this:
Having pasted the data into the Worksheet, the VBA would sort the data by Date then URN.
Starting from the first row the VBA would find what department the data relates to by looking at the first two characters of the URN (always the department code). It would then find what month the data relates to by looking at the month section of the date. Finally it would select columns A and B for all rows where the department code equals the one selected above and the date range is all dates for the month selected above. The selection would then be copied into the clipboard.
Using the two letter department code, the routine would then build a string containing the name of the appropriate Department Results worksheet and open it. Then using the month from the date it would select the appropriate month sheet (JAN, FEB etc)
So for the worksheet name it would be something like WkBkName = DeptCode & " Results 2016.xls" (where DeptCode is a variable containing the first two letters of the URN) and the date something like If MM = 09 then MonthSht = SEP (where MM is the month section of the date E.G. for 01/09/2016 MM would equal 09)
Note to the above: Some of the workstations we have run Excel 2003 and some run Excel 2013 so the file name extensions may be .xls or .xlsx or.xlsm as some have a bit of VBA used to colour code results. Perhaps a wildcard would be needed in building the file name such as WkBkName = DeptCode & " Results 2016.xl*" if that would work?
Having opened the relevant results workbook and selected the month sheet, the data would be pasted below the last entry in the sheet, populating columns A and B.
Next it would need to remove the duplicates. However, as the entries in the sheet from the previous week may have already been resulted (in columns C and E which are labelled Outcome and Outcome Details) we need to remove the new duplicate row, not the existing one.
The code would then close the department results workbook and go back and start the process again in the New Results ALL workbook for the next department. This would continue until the last entry in the New Results ALL workbook.
Finally the code would pop up a 'All Complete" message box and close the close New Results ALL workbook when the user clicks the "OK" button.
I have uploaded some example data to a public folder in my DropBox and given a link to it below. If you have any problems accessing the folder or editing these workbook please let me know.
https://www.dropbox.com/sh/a4dvs8ioruzdiuf/AADfTvpZ2j4lS6-IjdinIGL6a?dl=0
My apologies for my question being a bit wordy but I have tried to explain the issue as completely and accurately as I can from the start. I hope I have succeeded! If not them please let me know what you need to clarify.
Thank you VERY MUCH in advance for any coding examples, ideas and suggestions you can offer.
This is my first post as a brand new (wannabe) Excel VBA programmer who is not much past the Hello World stage!
I am trying to automate a process we use in work where we cut and paste data from a .CSV database extract into separate Worksheets which are accessed and updated with results remotely, via a shared drive.
What I need to do is probably bread and butter stuff to many of you VBA experts out there but has me completely flummoxed!
What we do at the moment:
Data is extracted from a database (in .CSV format) for the last eight days once a week on a Tuesday. The reason we extract eight days of data is to ensure we capture any jobs which were created later on in the day after the last extract was taken. This creates a bit of a problem the VBA would need to address, in that it extracts duplicates of jobs already extracted too. The data is all jobs created on all dates for all department between the two dates specified during the extraction. The data is two columns containing a date in column A and a URN in column B. The date (Creation Date) is always in the form DD/MM/YYYY .The URN is always four letters and eight numbers. The first two letters of the URN are the Department Code. So for example, a typical URN (job number) for department CN would be "CNJB00345678"
The raw data is pasted into a worksheet called "New Results ALL 2016.xls" and then sorted by Date Created then URN. Starting from the first row of the data, we then copy columns A and B for all rows where the date equals a given month for a given department. Next we find and open the results worksheet for the department, select the appropriate month sheet (JAN, FEB etc) and then paste the data after the last entry. Finally we remove any new entries which duplicate existing entries by deleting the row.
Note to the above: In most cases there is only one month sheet to deal with but where the extract comes at the end of the month, it can span the end of one month and the beginning of the next.
All of this is very slow and laborious (there are in excess of 40 departments) and often leads to mistakes slipping in through fatigue.
What I am after is a Worksheet with a VBA routine which will automate the process something like this:
Having pasted the data into the Worksheet, the VBA would sort the data by Date then URN.
Starting from the first row the VBA would find what department the data relates to by looking at the first two characters of the URN (always the department code). It would then find what month the data relates to by looking at the month section of the date. Finally it would select columns A and B for all rows where the department code equals the one selected above and the date range is all dates for the month selected above. The selection would then be copied into the clipboard.
Using the two letter department code, the routine would then build a string containing the name of the appropriate Department Results worksheet and open it. Then using the month from the date it would select the appropriate month sheet (JAN, FEB etc)
So for the worksheet name it would be something like WkBkName = DeptCode & " Results 2016.xls" (where DeptCode is a variable containing the first two letters of the URN) and the date something like If MM = 09 then MonthSht = SEP (where MM is the month section of the date E.G. for 01/09/2016 MM would equal 09)
Note to the above: Some of the workstations we have run Excel 2003 and some run Excel 2013 so the file name extensions may be .xls or .xlsx or.xlsm as some have a bit of VBA used to colour code results. Perhaps a wildcard would be needed in building the file name such as WkBkName = DeptCode & " Results 2016.xl*" if that would work?
Having opened the relevant results workbook and selected the month sheet, the data would be pasted below the last entry in the sheet, populating columns A and B.
Next it would need to remove the duplicates. However, as the entries in the sheet from the previous week may have already been resulted (in columns C and E which are labelled Outcome and Outcome Details) we need to remove the new duplicate row, not the existing one.
The code would then close the department results workbook and go back and start the process again in the New Results ALL workbook for the next department. This would continue until the last entry in the New Results ALL workbook.
Finally the code would pop up a 'All Complete" message box and close the close New Results ALL workbook when the user clicks the "OK" button.
I have uploaded some example data to a public folder in my DropBox and given a link to it below. If you have any problems accessing the folder or editing these workbook please let me know.
https://www.dropbox.com/sh/a4dvs8ioruzdiuf/AADfTvpZ2j4lS6-IjdinIGL6a?dl=0
My apologies for my question being a bit wordy but I have tried to explain the issue as completely and accurately as I can from the start. I hope I have succeeded! If not them please let me know what you need to clarify.
Thank you VERY MUCH in advance for any coding examples, ideas and suggestions you can offer.
