Copying rows to new sheet then emailing findings.

[Ross85]

with my work I have a large spread sheet with multiple tabs, what I'm trying to do with no success, is copy the rows of the individual and paste his detail or multiple personnel onto the tab WITHIN ONE MONTH , then do the same with the tab OUT OF DATE if you look at the PASTE YOUR RESULTS HERE tab, you will see that each column has different test, with dates attached. but when the individual has then completed the test to become in date it automatically removes it from the within 1 month or the out of date tabs.

I would then like these findings automatically sent to all the email addresses in the email address tabs, every Monday. I have left emails out for obvious reasons.

I really hope someone can help me out on here.

Regards

Ross

[dflak]

I understand the requirement to email. I don't understand the rest.

There is no data in the Within 1 month or Out of Date page. Are we to read data from these pages or put it there? Or is there data there and when the person modifies something (what thing?) on the Paste your results here page, you are expecting us to write code to find that same row (based on what?) in the Within 1 Month and Out of Date file?

I suggest you attach a file with more representative data, including what the formats of the emails should look like (with fictitious names and addresses).

Also a better explanation is needed as to how the user is supposed to use the sheet would help, at least for me.

As for the mailing. What do you want to mail? The entire workbook? Do you have Outlook? If not, what mailer are you using? The mailing can probably be handled using the Windows Task manager.

[Ross85]

sorry my apologies. I had to cut some data off the spread sheet to make it fit.

what I require is a few things.

if you look at the data on print version you will see info for personnel, I have removed names for security.
you will also see that they are colour coded Green, Amber and RED.
When a cell changes from green to amber I would like that cell and the persons information, I.E service No. Rank, First Name, Surname, Unit, Gender, and DOB all copied and automatically pasted onto the Within 1 MONTH tab. I realise this may be multiple personnel so what ever you think will work best?
I would like the same to happen but move to the OUT OF DATE tab if their cell is red.
Both these tabs will then be sent via outlook to management so they can see if any of there personnel are WITHIN 1 MONTH or OUT OF DATE tabs.

I would like this to happen every Monday so the management are getting a reminder each week, to make it easier to keep on top of their personnel.

if you can think of any better ways of doing this I'm all ears!

thanks in advance

[dflak]

That clears up the requirement a lot, but I am not seeing any color coding. How is this assigned?

[dflak]

In the meantime, look at the attached. One is a template for a script program. The other is instructions on how to use the script in conjunction with the Windows Task Scheduler to run reports.

[Ross85]

sorry about that, I had to cut a few pages out to make it fit. There is another page called Print version with all the colour coding on it, with the rules to change it colour depending on dates, these dates are the same as Paste here tab.

I cannot get the full doc on here to show you but happy to email it to you directly.

thanks in advance for your help.

[Ross85]

had a look at your attachments, I understand how to complete this task. But i need help still with the copying of the data to the other tabs. Sorry not a complete idiot on excel but no where near your level. Sorry if this feel like baby steps.

[dflak]

The file I downloaded is only 86 KB. I'll need a "full deck" to play with. Can you provide a workbook with this color data and tell me how to use it against the items on the Paste your results here sheet. How do you assign colors and to which cells on the Paste page?

Also I will need data on the Within 1 month page and Out of date page that is consistent with the Paste page so there are records to take off these sheets. I'll still need to know what the record identifier is. How do I match a record on the Paste page with a record on the Within or Out pages?

these dates are the same as Paste here tab

I see 24 columns with dates embedded in them. Which date are you talking about.

I think it best if you provide a book with specific an consistent data and walk through, step-by-step what you do and what is supposed to happen when you do it.

[Ross85]

yeah, i cannot give you a full doc as its to a large to upload. its 6.25Mb

unless you have another way to upload it?

like i said i can email you direct if it helps?

[Ross85]

my Email is [email protected]

[dflak]

I don't need the complete doc. All I need is enough data to get the idea. The doc without data is 85K the full doc is 6.5 MB, so give me about 5% - 10% of the data. I realize that some of it may not be in sync. For example of 100 records on one sheet, there may only be 10 matching records on the other sheet.

Also once I know how to match records, then I can "doctor up" records whose results I will know. It's an old QA trick called "stacking the data." However, I have to get a good look at what normal data looks like before I do that.

[Ross85]

Roger,

i have removed all files but 1 row and its still 5.56Mb. no idea how to cut this doc down more without removing tabs?

[dflak]

Compress the file and attach as a ZIP file.

[Ross85]

Finally, I have compressed the file.

I hope you can help me out, thanks so much....

so like I said anything that will highlight individuals who are going out of date and out of date onto 2 different sheets. then the ability to email the finding of these sheets to the management.

thanks

[dflak]

I do not understand why this file is so big. Also I notice that you are using Excel 2003 - that does take some options off the table.

Anyway, I don't see an Within One Month and Out of Date Tab on the file you sent. I can create these if necessary.

Also I ask again, "Within one month of what?" What date do I look at on the Paste Your Results Here sheet?

I see some military rank in the data. As you can probably guess by my avatar, I'm ex-military myself.

[Ross85]

yes, if you could create these tabs that would be great.

To make it simpler is it possible to just copy and paste the rows of the cells that have turned amber and red, to different sheets. called Amber and red?

These are the year matts, that all soldiers must complete to be qualified to a basic standard.

I have no idea why the file is so large. If you can make this as functional as possible with improvements. I'm all ears!! the print version is for management of visual ease of use.

I looked for the conditional formatting or code that defines why the cells change from amber to red but, just could not see where it was.

there will be maybe another 300 personnel pasted in, with there results, each Colum from H-AH has a test.

thanks for your help.

Seriously any improvements willing to listen.

[Ross85]

DFlak,

thank you for your help, yes I have no idea why this file is so large, also I cannot see why its so big either.

I was having a think. Results get pasted on the paste results here tab this effects all the details throughout the full document.

However on the Print tab there is formatting to change the cells from Green, to amber then Red. is it possible to just set the Rule to copy all amber cells with the test and that persionells detail to an amber TAB, this could be multiple tests though. then complete the same for Red cells?

when this is done then copy all results off these and email them to management.

I'm happy to get advice from yourself on this.

[Ross85]

DFlak,

thank you for your help, yes I have no idea why this file is so large, also I cannot see why its so big either.

I was having a think. Results get pasted on the paste results here tab this effects all the details throughout the full document.

However on the Print tab there is formatting to change the cells from Green, to amber then Red. is it possible to just set the Rule to copy all amber cells with the test and that persionells detail to an amber TAB, this could be multiple tests though. then complete the same for Red cells?

when this is done then copy all results off these and email them to management.

I'm happy to get advice from yourself on this.

Also on Excell 2010

[dflak]

If you save it as an XLSB file, it's about 1/10 the size.

All my mid month reports have come home to roost for auditing - it may be a day before I can get my teeth into this one.

[dflak]

I’m waiting for some more reports to come in. So I looked at the file and am clarifying my understanding.

I see that there are 24 columns with data of a format similar to G PASSED 08/04/2016 Level 1.

I see that on the Print Version sheet, the color is assigned by the first letter. I assume that this part of the requirement is satisfied. It seems to work with the formulas you have in place.

There is a minor tweak that could be done on this page by copying the data (via VBA) from the Paste Your Results Here sheet in Excel tables on the Print Version Sheet instead of extending the formulas down into rows that don’t have data. This would simplify the formulas and reduce the number of formulas and may reduce the size of the spreadsheet. But this would only work in 2010. It won’t work in 2003.

Be leery of 2003 it has not been supported for several years. It is, after all 14 years old. That’s about 90 years old in software-years.

I assume that I need to check the date part of the string to see if any of the 24 cells are within 1 month or out of date. If any of the 24 dates are within a month, then the soldier gets put on the within a month sheet. If any of the dates are out of date, then the soldier is put on the out of date sheet. A soldier may wind up on neither sheet, just one sheet or both sheets. I can arrange to highlight the dates when I copy them.

Then you want these two sheets emailed off to an address list. I can do this if you have Microsoft Outlook. I would have to look up how to do this with other mailers. I will address this and the automatic running at a later time. They are not difficult to do, but I’ll need to provide you with step-by-step instructions. I also may need to know what OS you are using. I am assuming Windows 7 or Windows 10.

I am fairly sure I know what out of date is: if it’s older than today, then it’s overdue.

I do have a question as to what constitutes within a month. Is it within a month if it is due this month? Is it within the month if it is due by the end of next month? Is it within the month if it is due by the same date next month? I tend to think that the latter makes the most sense.

Timing is perfect, these are my questions for the moment. Two more monthly reports just landed in my inbox - "Incoming!"

[dflak]

Here is a partial solution. Tell me if I am on the right track. For the purposes of QA, given the dates you have in the data, I set the current date to March 10, 2016 - this is set in cell V1 on the Notes page. In the final copy the code will set it to the day on which the code is run.

Interestingly, I am not having any issues with date format. I'm parsing out dates like: 21/04/2016 which makes no sense in American date format, but it is translating to April 21, 2016! It is also showing up as such when I look at the value in the debugger.

Where we are now.

I go down the list of names. For each name, I look at rows H:AE and get the date embedded in the string for each cell.

I compare this with the current date. If the date parsed out is less than the current date or is blank, I turn a Flag on to indicate that this record contains at least one cell that is out of date. I also color the cell red.

I also compare the date to due within the month. This date is the same day of the month as the current date, only a month later. So for March 10, 2016 it is April 10, 2016. If the date is less than this date I turn on another flag to indicate that this record contains at least one cell that is due within the month. I also color the cell Orange.

After checking all the dates, I check the flags for the records. If the overdue flag is true, I copy the record to the out of date tab. If the within a month flag is true, I copy the record to the within a month tab.

Based on which flags are set, a record can be copied to one or both or neither of these tabs.

Please confirm that this is what you want to happen.

Once I get confirmation, I will move on to emailing the tabs - I may copy them to a separate book or, I can even save them as a PDF file.

After we get the mail set up, then I will provide instructions on scheduling the program to run each Monday. I do have a question about this. I'm getting the impression that different units will be using this spreadsheet. If you can give me the big picture, it will help me design the system. It might be a matter of putting all the raw reports in a common folder for processing. A master program can run through each report in the folder and do the copy / paste and mailing (Well, separate mail lists might be tricky, but we can discuss that).

[Ross85]

That sounds perfect, I'm sorry about the late reply, been dying since Saturday with the Flu.

your ideas seem like they will fit the bill perfectly.

we also use Outlook at work.

[dflak]

Keep the bug on your side of the pond, please!

OK, the next version I will send will have the email working - I hope .

[Ross85]

just been having a look, this is a document I have inherited.

been looking it, just to add to the complecation. the paste your results here tab is when the person passes the test. for each MATT there is a different time line.

For example

MATT 1, is yearly
MATT 1 WHT is 6 monthly.
MATT 2 AFT is yearly.
MATT 2 PFA is 6 monthly.

the rest are all yearly.

so if the person passed 18 Jan 17, (British Date) I would like it to highlight yellow when within his last month. then go RED when his year has passed.

Thanks.

your a complete star for all your help

[Ross85]

just been having a look, this is a document I have inherited.

Just been looking , just to add to the complication. the paste your results here tab is when the person passes the test. for each MATT there is a different time line.

For example

MATT 1, is yearly
MATT 1 WHT is 6 monthly.
MATT 2 AFT is yearly.
MATT 2 PFA is 6 monthly.

the rest are all yearly.

so if the person passed 18 Jan 17, (British Date) I would like it to highlight yellow when within his last month. then go RED when his year has passed.

Thanks.

your a complete star for all your help

[Ross85]

just been having a look, this is a document I have inherited.

Just been looking , just to add to the complication. the paste your results here tab is when the person passes the test. for each MATT there is a different time line.

For example

MATT 1, is yearly
MATT 1 WHT is 6 monthly.
MATT 2 AFT is yearly.
MATT 2 PFA is 6 monthly.

the rest are all yearly.

so if the person passed 18 Jan 17, (British Date) I would like it to highlight yellow when within his last month. then go RED when his year has passed.

Thanks.

your a complete star for all your help

[Ross85]

sorry computer acted up

[dflak]

The forum has been acting up a bit. Got a couple of duplicate posts there .

OK, I got your "complications" - we can address that issue with a table that contains these rules.

In the meantime, here is a version that does emailing.

Put the list of addressees on the Email List sheet. You can have as many as you want.

What the mail part does is it creates a workbook called ODR with a date stamp for the current date and it copies the two sheets into it. Then it mails it as an attachment.

[Ross85]

have just pasted all the Nominal in with the dates attached for results.

rows 7-11 are acting up on a few of the pages.

also no other personnel was transferred to the pages?

am I totally missing something here?

I'm very new to VBA and excel.

[Ross85]

Print version.
grade
age
date

your a star thank you for all your help so far!

[dflak]

Send me the data to paste in (make sure there is no sensitive data) and I'll see why it may be acting up. There was no attachment in the previous post.

[Ross85]

attached info, nothing sensative

[dflak]

I found the issue. The program was looking at column A to determine where the last line of data should be. You removed the service numbers to keep the data non-sensitive. I restored it by placing a zero in cell A132.

Now that I have a better set of data, I'll work on those "complications."

[dflak]

The light went on and based on your questions I figured out that the dates I am parsing out are dates last accomplished. I have adjusted the code, and computed due dates based on the rules you gave me above. I am assuming that the rest of the due dates are due 12 months after last accomplished.

[Ross85]

yeah, that's correct.

thanks

[dflak]

Here is my last version for the day. I "disconnected" the mail part for test purposes.

You can play with it if you wish. What I would like you to do is look at the results to verify if they look correct. Also take a look at the metric sheet to see if it's what you need.

[Ross85]

no attachment

[Ross85]

just had a look.

the same problem is happening as I paste all my data in, the print version tab rows 11-15 all say #REF! this happens on the Grade, age and date tabs but roes 7-11 are affected

The Personal details are also not showing on the within a month Tab or Out of date tabs. It looks rather busy on the within and out of date tabs is there a way to blank out the cells on these pages that are not affected? as it is a lot of unnecessary data showing? what you think? is this achievable, possible?
I just want them looking at the cells that are out of date.

was also think of another colour for when they have not completed the Matt trg (what's your thought)

I just need this to be very easy to read.

your Metric tab is brilliant! answers questions quickly, very good tool!!

thank you so much.

cheers

Ross

[Ross85]

Just seen the Metric tab is counting 516 personnel, when there is only 131. thinking its counting the info from 3 sheets, or am I way off?

[dflak]

I have not looked at the Print Version Tab at all. What do you want to display here? I can probably also generate this tab as I go along. Actually, as I go through the pasted data, I assign a red or orange color to the cells and then copy the record if needs be. I can assign a green color if neither flag appears and then copy and paste the page to the Print Version sheet. I'll leave the formulas in place for rows 1:4.

I'll have to take another look at the Within a month tab to see what is causing the gaps.

As for showing only the dates need (instead of all dates and having the dates that matter highlighted). This can be done. It may slow down processing. Now it's taking about a minute and a half to run. I'll estimate that it will

As for the metrics: some personnel are counted more than once. If a person has three items overdue, that person shows up three times. So what you are seeing is that for the 131 people they have a combination of 515 items overdue. Some have more than one item overdue.

Instead of (or in addition to) the raw numbers, I could provide percents. For example, there are 58 people overdue MATT 1 WHT so that's 58 /131 or 44.3%.

[Ross85]

if we could leave the print version tab as it always is. Its easier to print out and put on a board.

yeah that makes sense. no!! I like how there is exact numbers.

what about the personnel details not transferring to the OUT OF DATE and WITHIN A MONTH?

could we also maybe do a tab for personnel who have not completed the training?

just gonna say your a complete gent!!

[Ross85]

if we could leave the print version tab as it always is. Its easier to print out and put on a board.

yeah that makes sense. no!! I like how there is exact numbers.

what about the personnel details not transferring to the OUT OF DATE and WITHIN A MONTH?

could we also maybe do a tab for personnel who have not completed the training?

just gonna say your a complete gent!!

[Ross85]

if we could leave the print version tab as it always is. Its easier to print out and put on a board.

yeah that makes sense. no!! I like how there is exact numbers.

what about the personnel details not transferring to the OUT OF DATE and WITHIN A MONTH?

could we also maybe do a tab for personnel who have not completed the training?

just gonna say your a complete gent!!

[Ross85]

could we do both numbers and percentages?

[dflak]

I am still wanting to know what you want to do with the print version page. Take a look at what I produced to see if it meets your needs.

I made some of the updates you requested and I fixed some errors.

I fixed the issue with the gaps in Within a Month. There was also an issue with the metrics that I corrected.

I am tired of typing the names of the sheets so from now on,
P = Paste your results here
W = Within a Month
O = Out of date

I changed W and O so that I don’t copy the row whole from P. I populate a cell only if it fits the category. One thing I am not doing is populating the cell with “No Data” if I find it blank. I will fix this on the next incantation.

I was thinking if there is a way to simplify W and O. Do you really want to see all the data? I can simply put an X in the cell which means that we can make the column headers vertical and see more on the same sheet. I could also just post the date last accomplished or the due date or I can leave it as I have it now.

I am still wanting to know what you want to do with the print version page. Take a look at what I produced to see if it meets your needs. All I did was copy the colored P page. I clear P after doing this so P is ready for a new Copy / Paste.

I could also clear P completely after processing to remove all data or give the user a button to do this manually. Better still have the user save the CSV file in the same directory with the application and give it a specific name like ODR.csv (let me know what you want to call it). I can import this file automatically.

Basically
Step 1: Save report as ODR.csv.
Step 2: Press “Import Data”
Step 3: Review the document
Step 4: Press “Mail”

We’ll have to give them instructions on maintaining the email list.

Since there are manual steps (saving the report as a CSV file), I don’t think that this can be run from the task scheduler. The report is apparently run against a training database. If you had read access to the database, then there is a way to read the data directly into Excel.

If that were the case, you could start with step 3. Find out from your database administrator what kind of database is being used, and if they are willing to allow people to have direct, read-only access to it. DBAs are usually very protective of their data.

Or ask the DBA if they could produce the CSV files automatically and deposit them in a common directory.

If either of the above is possible, I can give more details on what is needed.

[Ross85]

looks good,

is there a way to populate the names of the personnel automatically from the Paste, to the W,O and Print tabs.

the idea of due date for the W tabs is great, could be do that with the O and say how many days they are over due?

This is a live document, multiple people use it. with each matt there is different instructors, who will be inputting data at different times.

This is used on a shared network.

all these personnel have password access, the rest can view but not amend.

looks great so far though!

[dflak]

What do you mean by "populate the names of personnel from P to W, O and Print Tabs?" I do copy columns A-G to these tabs. It's just that columns B-E have no data in them currently. BTW: Internally the Metric Tab is M and the Print Tab is R (already used P).

I can do due date on W and due date / days overdue on O and give you a separate list of people who have no dates for training.

Fortunately, the code has shaped up to be very modular. This makes some of these changes relatively easy.

[dflak]

What do you mean by "populate the names of personnel from P to W, O and Print Tabs?" I do copy columns A-G to these tabs. It's just that columns B-E have no data in them currently. BTW: Internally the Metric Tab is M and the Print Tab is R (already used P).

I can do due date on W and due date / days overdue on O and give you a separate list of people who have no dates for training.

Fortunately, the code has shaped up to be very modular. This makes some of these changes relatively easy.

[dflak]

What do you mean by "populate the names of personnel from P to W, O and Print Tabs?" I do copy columns A-G to these tabs. It's just that columns B-E have no data in them currently. BTW: Internally the Metric Tab is M and the Print Tab is R (already used P).

I can do due date on W and due date / days overdue on O and give you a separate list of people who have no dates for training.

Fortunately, the code has shaped up to be very modular. This makes some of these changes relatively easy.

[Ross85]

when I paste the details in, I.E Number, Rank, Name, Sqn, etc this info is not transferrin into the W and O tabs, so I see their data but not there personal info.

[Ross85]

do I need to do anything my end, when I edit the info on the P tab. or will this all be done automatically?

[dflak]

Be prepared to have your socks knocked off, if I do say so myself

Changes in this version

W now displays the due date.

O now displays the due date / days overdue

Metrics now has a new category: No Date

There is a new sheet, Training Due. It has two displays: Training by Course Description and Training by Person.

Training by Course Description shows a list of courses and the people who need to take the course.

Training by Person shows a list of people and the courses they need to take.

Both of these are pivot tables. There is a filter for type on each. You can filter by No Date, Out of Date or Within the Month. I have it set for (All) since people in any of these categories should be scheduled for training.

The Metrics Sheet and the Training Due sheets are run off a table hidden in Rows A:D on the Metrics Sheet.

[dflak]

In answer to the question on personal data. I'm not seeing that problem here. I dummied in a Service Number, first and last name and they are coming across. Take a look at the sample I just sent.

[Ross85]

that's Brilliant.

I Downloaded your file, I then pasted in all my info for the Sqn, Number, Rank Name etc. but its still not tranfering these across to the other tab.

BTW, wow you did blow my socks off.

I'm running Excell 16 on my Computer at home and Ex 10 at work, or am I missing something?

[Ross85]

that's Brilliant.

I Downloaded your file, I then pasted in all my info for the Sqn, Number, Rank Name etc. but its still not tranfering these across to the other tab.

BTW, wow you did blow my socks off.

I'm running Excell 16 on my Computer at home and Ex 10 at work, or am I missing something?

[dflak]

Both Excel 2010 and Excel 2016 should work. In fact I haven't done anything yet that would prohibit 2003 from working.

If it is working in one place and not the other, then it most likely is the data. Do you have any hidden columns or anything like that? Can you send me the first half dozen rows of data? Retype the names to preserve confidentiality. Since I'm only asking for a half dozen rows, you can use dwarf names: either Disney or Tolkien will do. (You can tell work is driving me crazy). There is something I am not seeing in the data from 3,000 miles away.

I not only copy personal information once but in some cases, several times. You will see in the code the comment: ' Copy Columns A-G and specific date. So every time I find a date to copy, I also copy this information in whether it is already there or not.

[Ross85]

cool done a few bits of details, when I done this is never refresh just to show that there where fewer personnel?

thanks.

I'm back to work tomorrow would love to blow there socks off with this all signing. lol

[dflak]

Maybe I forgot to mention something obvious. I have not hooked the macro up to the button yet. It must be run manually. The name of the macro to run is FindDue.

Here is the file you sent me after running this macro.

[Ross85]

lol,

nope never mentioned that, so are you going to create a button for this, also does this have the email active?

[dflak]

Yes, I will put buttons in for data import and for mailing.

[Ross85]

lol,

nope never mentioned that, so are you going to create a button for this, also does this have the email active?

[Ross85]

its Amazing!!!!!

just ran the full 132 names took about 2 mins. must say its brilliant.

YOU ARE A STAR!!!1

all I would need to send now is the W,O and T

just fantastic

[dflak]

All you need to run the email is to be logged in with the correct credentials. Outlook does not have to be open, but it does need to be installed.

Do you want both training sheets mailed or just the one with the chart?

[Ross85]

Is this ran of with windows task manager?

the one with the charts, that's should be fine.

[Ross85]

how would I set all this up?

[Ross85]

They Loved the Spreadsheet, got asked if it able to add a cloum?

so in the P tab, have the first Colum be called Category:

then have 5 references attached to letter,

R1, Green
R2, Orange
M, Yellow
C, Red
S, Grey

so the though Process is, if they have these in the first Colum, it will colour code that Personals name. So when looking through the sheet its obvious who is attached to chat colour. is it also possible to have a bar some where of how many of these meanings have been attached?

there should be 13 on R1
then at least 97 on R2
then the rest will vary.

but they were blown away by your results today.

I think the only sheet that will need to be forwarded into management is the Trg due page.

can I set this up to run automatically every Monday and send this out?

thanks in advance Ross

[dflak]

The problem with setting it up with the task manager is getting the raw data.

As I understand it, currently, they run the report and then save it as a CSV file and then open the CSV file and copy and paste it. I will cut out the copy / paste part and replace that with an Import Button, but they will still have to provide the CSV file.

There are two ways to circumvent this as I mentioned in an earlier post:

You can tap into the database directly - I'd need to coach you on how to set up computers to do this. Although this is the preferred method from a technical standpoint - it is more difficult - each user would have to take some one-time steps to configure the computer. Also there's the politics of database management. DBAs are very protective of their databases and are hesitant to grant even read-only access.

So I recommend you lobby for the following if it is possible. I do know that it is possible with many applications. The application owner can set up the system to run these reports on Sunday evening or early Monday morning and deliver them to a specific directory as CSV files. If I know the name of the files, I can import it automatically and the entire process is automatic.

I'd suggest that they download the files with a predictable naming convention such as ODR_UnitDesignator_YYYMMDD.csv.

Failing that, the only thing I can suggest is everyone put a repeating reminder in Outlook to run the report on Monday morning. As you noted, it only takes minutes.

[dflak]

Explain to me more about the first column. Adding a column at the front will "shift" the code over a column. This is not a big deal, but I do have to go over my code methodically to make sure I catch all the shifts .

Will this category be provided as part of the CSV file?

I understand that you want the person's name highlighted with the color associated with the code. This can be done with the W and O sheets and the Print Version easily enough. If you want the colors to show up in the pivot tables, that will take a small bit of programming.

Also making a chart showing how many people are in each category should be easy.

[Ross85]

my boss asked if we could colour code peoples names:

R1, Green
R2, Orange
M, Yellow
C, Red
S, Grey

I thought the easiest way to do this was to add a Colum on the P tab. with the Colum named category.
if you can think of an easier way to accomplish this and put the Colum in another location. I'm totally all ears!

[dflak]

Let's go back to basics. This is my understanding of how the process works.

People run a report on the training system and it produces a CSV file as indicated by the very original attachment you sent me. This CSV file does not contain a category column.

People open the CSV file and copy and paste the contents into the Paste here sheet.

Then the automated process takes over.

Now you want to add a new column. Where does this column come from? Do you want people to insert a new column A on the Paste Sheet and fill in the information manually?

Or are you going to modify the report that comes from the training system to include this as the first column?

Or some other method?

[Ross85]

when we have got the final ODR, I will keep it as that file. this file will then be uploaded onto a share Microsoft office sharepoint server(MOSS). I will be able to get permission from ther administrator, so you will have to talk me through how to complete this.

when this is done I just want the management to receive the Trg due Tab.

your a star. I hope your works calming down today?

[Ross85]

when we have got the final ODR, I will keep it as that file. this file will then be uploaded onto a share Microsoft office sharepoint server(MOSS). I will be able to get permission from ther administrator, so you will have to talk me through how to complete this.

when this is done I just want the management to receive the Trg due Tab.

your a star. I hope your works calming down today?

[Ross85]

sorry, my boss asked if there was a way to flag people on the sheet:

R1, Green
R2, Orange
M, Yellow
C, Red
S, Grey

this changes due to different circumstances. so he asked if it was possible to somehow be able to flag these people, so its obvious to the eye. as the people on R1 must always be in date, because of deployments etc.

if you can think of place to put tis data so its easier, I will bow to your better knowledge(you've been right 100% in everything you've done so far)

[dflak]

Do you expect to make the category assignments manually on this sheet? Or can this be done in some other file in some master roster?

Maintaining this as part of this workbook is problematic. I can detect who is currently on the roster, and compare the list to who was on the roster the last time, and identify new people. You will have to assign a category to the new people as well as keep track of changes to current people. Then you click a button and the current roster will be saved for comparison for the next run.

I will use the current roster to look up the category based on the person's service number.

[dflak]

Here is a look at what I think the new notes page will look like. This may change if we have to take a "detour" to manage categories.

[Ross85]

yes, this will be done manually.

when the roster changes that will be the new roster.

I was aware it would need to be done manually because of the amount if times it may change.

[Ross85]

looks great. WoW!!

leaving to pick my son up from school in 10, will be back about 30 mins after that.

i really do appreciate all your help..

[Ross85]

Wow, looks fantastic.

I'm leaving to pick my son up in a few mins, will not be away to long prob 40 mins max.

looking forward to seeing what you produce.

your a star!!!!

[Ross85]

looks great

[dflak]

I also spent some time as a technical writer, so here is the user's manual. There are two things to mention: I can give you any number of slots for email addresses, I've limited it to 10 - do you think you'll need more? By putting a limit on the number of email addresses, I can lock down the sheet better and limit the user's ability to make a mistake.

Also if you look at the metrics sheet, I changed the chart type from clustered column to stacked column. I think stacked columns better indicate the areas that need the most attention.

[Ross85]

looks great

[Ross85]

yeah I will need more can you make it 25?

[dflak]

OK - I'll work on a way to maintain the roster as I described before. It will be on a separate sheet, but the colors will show up on the W, O and Printed sheet.

Do you want the colors on the Pivot Tables?

Also, you will have to do some editing on the user's manual to translate it from American to English. Things like color = colour.

[dflak]

You want 25, you got 25 .

[Ross85]

yeah I agree is does look better. the whole sheet looks fantastic.

[Ross85]

yeah that's fine with me. I will get that squared,

can we get the colour for the personnel on the Trg tab as well?

[Ross85]

yeah that's fine with me. I will get that squared,

can we get the colour for the personnel on the Trg tab as well?

yeah showing on the pivot table will look good as well.

[dflak]

We have the target bracketed. The only thing missing is assigning the colors to the pivot tables. Then we will be ready to "fire for effect."

[dflak]

Take a look at the Print version of the last file I sent you. There are a couple of problems with the formulas at the top of the sheet.

First. Somehow, I lost the formulas and when I copied them in form another version, I wound up linking to that version. So I have to clear them out properly.

Second. Some of these formulas reference sheets no longer in the workbook.

Please take a look at these formulas and revise them as needed and send them back to me.

[dflak]

Explain to me the logic behind the formulas and I can probably come up with more efficient formulas.

[Ross85]

ha, that's what I have been thinking to myself. I cannot actually see the logic to them any more, there is different timings for personnel within certain age groups. an age category would be good say every
16-18
19-20
21-25
26-30
31-35
36-40
41-45
46-50
51-55

[Ross85]

I honestly cannot see the purpose off them any more.

[Ross85]

can you see any reason for the formulas that aren't being covered already?

[dflak]

Good, they will be gone.

I can replace them with three rows from the metrics table. I can give you percentages for:
Current (not overdue or no date) - people due within the month are considered current. -or I can break this out to its own line.
Out of Date
No Date

[Ross85]

what I think would help is maybe a symbol in the work sheet that would mean medically exempt. as some personnel are downgraded, due to short term/long term injury they cannot complete some tests.

[Ross85]

do what you think will be best.

[dflak]

I tell you what. Let me add the metrics as I described them on the Print Version Sheet. Then let's release this as a beta.

Have two things already in my for future improvement:
- Color code people on the pivot table. I understand the requirement here.
- Medical exemption. I'll need some more definition on what you want to do with that.

Let's operate for a couple of weeks and get more user comments. I find that after a release, people say, "Gee, I didn't know it could do that. Can you make it do this?"

[Ross85]

yeah ok, well with our job people have medical exemptions from completing specific test, for example a man with 1 leg is not expected to complete a run fitness test, so he would be medically exempt.

[Ross85]

so when I paste that persons data in, instead of it being blank. we devise another symbol and this gets shown that he is exempt from completing this test

[dflak]

I think I can tack this onto the same code that does the category.

The way I work the category is that it pulls the current roster and displays it. It pulls the category from the previous roster. You update the roster with new information and click a button. The program saves the current roster in place of the previous roster for the next time you use the program.

The first time you bring the roster up, you will have to fill in all the categories. After that all you need to do is change in those that need to be changed.

I can add another column to the roster for medical status. I'll assume you won't be changing it that often.

Some questions are: how many medical statuses are there? I'll assume two: exempt and non-exempt. Also I will need a list of courses that are affected by the exemptions.

Attached is the latest version with new instructions. I think you'll see how I can handle the medical exemptions by looking how I am handling the categories.

[Ross85]

yeah I see what you mean, the two medical exempts would work. they could be exempt any of the trg, depending on medical issue.

[dflak]

Well then we will need an extensive list of medical issues and which courses are affected.

E.g
Medical condition 1 exempts the person from Course A
Medical condition 2 exempts the person from Course B and C
etc.

[dflak]

Well then we will need an extensive list of medical issues and which courses are affected.

E.g
Medical condition 1 exempts the person from Course A
Medical condition 2 exempts the person from Course B and C
etc.

[Ross85]

yeah ok, can you walk me through importing the data. I seem to be having real Drama with it.

sorry, must be frustrating for you.

[Ross85]

got it was over thinking it. lol

[Ross85]

just set it up looks amazing!

[dflak]

Excellent! The two outstanding issues are medical exemptions and the coloring on the pivot tables.

I have not had good luck with conditional formatting and pivot tables in the past and other people also have problems. I'm going to have to do some experimentation as to which approach to use.

[Ross85]

well like I sais I'm more than wiling to go with your knowledge.

I will find out the medical exemptions tomorrow and send them your way,

the one question I do have, can we get the colouring on the training Due tab as well?

[Ross85]

What ever ideas you have, I'm more than willing to put them in place and give lots off feed back.

you'll need to give me quite a bit of direction on setting this up the run automatically. I hope that's ok?

[dflak]

Coloring the training due tab will take a while. Those are pivot tables and don't work well with conditional formatting. I know of at least one way to do it, but I need to do some experimentation to see if it's the best way. Whatever I come up with will not affect the remainder of the code. So you can operate without it until I can come up with a version that does the coloring.

You can not run this report automatically. There is too much human intervention. Someone has to download the data and after it is imported and someone has to confirm the categories.

I've already mentioned some alternatives to importing the data. If your IT people can deliver the reports automatically as CSV files, then we can proceed. I'll have additional suggestions about the download if this is possible. One question to answer is: does it matter if one unit can see another unit's data? (they would have to dig to do this, it won't show up on their report).

As for the categories, they could be defined in a separate spreadsheet that is maintained totally manually. This spreadsheet will be kept in the same directory with the application and have a specific name. It will complicate matters a bit for the end user since it would be one more thing they will have to remember to do.

[Ross85]

I really appreciate all your help. just getting the medical info, have another cat for you. Para Trg,

Is the personnel para Trg,

Yes
No
LCR

Then some how make this visible on the personel. as we have already colour coded the names.

whats your thoughts on applying this.

i seem to be moving the goal posts all the time, but this is just blowing my mind all the application and how it will make life so simple

[Ross85]

just been told the medical cont is not required, as its usually done on a case by case bases.

that's a help

[Ross85]

to get our rolling currency we are required to log on to a site, password protected and download the file manually. we can only see our data.

I'm just thinking about ways around this, I'm sure it would be possible.

[Ross85]

hey,

I have created a Para Tab, named ShS on the VBA, its the exact same set up as the Cat Rosta.

but I just cannot get the it to work as I require it.

what I have been trying to work is;

Category Symbol
CR CR
LCR LCR Double underline
P Coy P Coy strike through
Nil Nil

I would like this to appear on the surnames of the personnel when I have selected there roles on the Para Sheet.

cannot figure this out at all.

[Ross85]

hello,

getting a problem when I try to run the import?

Function GetDate(InString As String) As Date
Dim MyString() As String

' If blank default to 1900
If InString = "" Then
GetDate = 0
Exit Function
End If

' Parse the string apart
MyString = Split(InString, " ")

' The 3rd item is the date
If IsDate(MyString(2)) = True Then
GetDate = MyString(2)
Else
' If it doesn't look like a date, return 1900
GetDate = 0
End If

highlighted the area where its having the problem.

Run time Error 9

[dflak]

This project has a severe case of "feature creep." I may be moving towards the goal posts but you keep moving them.

On the case-by-case exemption. You will need a table with a person's name and the course the person is exempt from. You may have multiple entries per person: one for each course that the person is exempt from.

Instead of color coding people, we may be to a point where we can add initials after the person's name - so a person can have multiple categories at once. I can add the initials as I process the record and they will carry over to all the displays. Again, the person will have one entry per category. This may be better since it's easier to remember what an initial means than what a color means.

The situation has become complex enough that you'll need to maintain these two lists in a separate spreadsheet and I'll import them at run time. The good news is that you can work on these lists as things happen and it will reduce the size of the main program.

The other good thing about the two file system is that it removes one of the obstacles to full automation.

[dflak]

As for your error message. I'll need the data that caused the error - you can de-sensitize the names. The error is an indication that a piece of data is not in the correct format.

[Ross85]

I redownloaded from this site and done nothing new

[Ross85]

this is what I tried to import

[dflak]

I just downloaded the CSV file. There are extra rows - the data does not start until row 5. Is this the format you are getting the data in?

[Ross85]

It's sorted now. No idea why that happened. They are living the format. Will give you an update tomorrow.

Thanks. You getting any job with the experimenting

[dflak]

Because of the increased requirement, the design of the program has changed.

Categories and Exemptions are now tracked in a separate file that must be maintained manually and saved and closed before the report is run. Initial setup will be a pain, but copy and paste can get a lot of the work done. Routine maintenance should not be that hard. Keeping track of who joins and how leaves a unit on a weekly basis should not be that difficult.

The report will read these files at run time to get the information it needs.

Attached is a sample file and some instructions.

[Ross85]

thanks, will have a proper look after I get in from work. your a star!!!!

[Ross85]

looks amazing, so I'm guessing we are loosing the Cat tab of the main Sheet?

can we also put an R3 into the cat page, id that possible.

the med cat is brilliant and very explanatory.

whats the possibility of having a page as well with the para ability on the same database as the Cat and Med exemption.

we need to be able to see if the person it at certain levels.

such as

CR
LCR
P Coy
Nil.

maybe have the name underlined etc so we can see this clearly on the printable sheet. it would help with out planning. how many peop[le are CR, LCR etc

[Ross85]

also had the same problem today with uploading the data.

this data was downloaded directly from the site, no longer than 30 mins ago. it downloads in this exact format.

same run issues. can you help ??

[Ross85]

once again I cannot tell you how help full you have been and wish I could get you a beer. Your help has been above and beyond.

Thank you for being so generous with your time, and patient with me!!

[Ross85]

here is pics of the fault on the upload, its must be like you said, starting with a few extra lines. I have no idea how to sort this problem though.

[dflak]

looks amazing, so I'm guessing we are loosing the Cat tab of the main Sheet?

Correct. I will import the information I need into hidden sheets so you won't have to deal with it in the main program.

can we also put an R3 into the cat page, id that possible.

Just add it to the table and it's available. I design things to be data-driven as much as possible so code changes are few and far between.

the med cat is brilliant and very explanatory.

Thanks. It can be used for exemptions for ANY reason. The program does not care why the person is being exempted from the requirement.

whats the possibility of having a page as well with the para ability on the same database as the Cat and Med exemption.

we need to be able to see if the person it at certain levels.

such as

CR
LCR
P Coy
Nil.

I don't understand this question.

maybe have the name underlined etc so we can see this clearly on the printable sheet. it would help with out planning. how many peop[le are CR, LCR etc

Let's talk semantics here - we have a category, and according to my understanding of a recent update you sent me a person can have more than one category. A person can be para and R1 or something like that. That is the way I have the system designed now.

I have no idea what CR, LCR, P Coy and Nil are.

I'm beginning to think that you want to go back to a single category and also have a separate "level" for Para, the values of which are CR, LCR, P Coy and Nil. We can run these off the same table as category, but just have a separate column for it. Then on the displays I can display (Category/Para) for example (R1/Nil) after the person's name.

If this is what you want, then this simplifies things a lot as you can track these directly on the Main Roster. The exemptions will still have to be tracked separately.

I will look at the data issue. The file you sent me starts with the headers. There are no extra rows. The file is missing the personal data which I assume you removed for the purpose of privacy.

[dflak]

I found the issue with the data import. There are cells that are neither blank nor containing a formatted string. Instead they contain "N/A." Is there a significance to this (such as this is the training system's way of saying that the member is exempt from that requirement), or does it mean something else? Do you want me to treat it like a blank cell or put something else in its place?

I made temporary accommodation for it in the code and went on to import the data successfully.

Are there any other codes or data that you can think of that show up in the download besides the formatted date, blank or N/A?

[Ross85]

well at my unit there is a requirement to jump out of planes, but it depends on what cat they are at:

CR means they can complete Day/night
LCR means they can only jump day
P coy means they have completed the basic trg, but have not done any parachute trg yet
Nil, meaning they have not completed anything.

the reason this becomes apparent is for when planning exercises.

it would be helpful to see what men have these capabilities at a glance.

if you think that would help or be simpler. I'm always willing to listen to your advice.

is there a way to make it obvious and keep the format the same,

saying underlining/double underline, () and names as normal on the printable sheet???

like I said totally open to your experience here

[Ross85]

not to my knowledge, like I said I DW this direct from source a few hours ago. so anything you find is al news to me.

I will leave this to your judgment/

[Ross85]

sorry DL this direct from the source today

[dflak]

Here is the latest update on the separately-maintained Roster file. You maintain columns A:G and I, J which can be populated by drop-down lists.

Column M contains a sample of what the name might look like on the report.

Do you see any reason why the service number, Unit, Gender and DOB be repeated on the report? I can start off with just the name. The attached picture will give you the idea.

[Ross85]

no i understand, this looks great, will the Print page still be colour coded. and i like the idea of (C/CR) etc in this, will that be like that also on the print page?

looks amazing!!

[dflak]

Yes, The Print Page will still have coloring based on due within a month, overdue and OK.

Yes the names will appear like this on all reports.

[Ross85]

wow, well your smashing this out the park!

[Ross85]

I really appreciate your help

[Ross85]

these programs will be uploaded on the Sqn share page, they will both be kept in there. Will doing this effect the running of the programs?

[dflak]

As long as the Program, CSV file and Roster files are all in the same directory, things should be fine.

The ultimate report will have a date stamp on it, be produced in the same directory and be emailed out as an attachment.

I have a question. As I parse the dates, I can come up with 7 statuses. I know what color you need for three of the statuses on the Print Sheet. Do you want to assign colors to the other statuses and if so what?
- OK (Green)
- Within a Month (Orange)
- Out of Date (Red)
- Exempt
- No Date
- N/A
- No Data (the program can't figure out what's in the cell).

BTW: I check to see if a person / course is supposed to be Exempt before doing any of the other checks. If the person is exempt I put the word "Exempt" in the cell where applicable.

[Ross85]

yes that would be extremely help full, you can use any colour you think is appropriate.

will all these reports be put onto the Metric tab,

for examples how many people are CR,LCR etc

how many people are exempt

I have a quick question, is this report automatically set up for 130 pax, the only reason I ask is when, this get pushed to other squadron they have different manning. they may only have 50 Pax, so the number of people in each squadron can change.

[dflak]

Changing the number of people is not an issue. I have the program detect how many lines of data there are.

The metric tab will show how many Current, Within a Month, Out of date, N/A, Exempt and No Date by course.

I don't have any reports that report by category or level, but the data are there to do so. What would you like to see?

[Ross85]

just to see how many people are CR,LCR,P Coy and Nil. number then a percentage. then the same for people on R1,R2 etc. how many then percentage again. As there is only a certain amount for each. will make my bosses life easier. to seea number then make changes.

[dflak]

Quite easy given the data. I can put this information in this report or I can also make it a report from the roster sheet.

The only reason it would *have* to be on this report is if you want it mailed.

[Ross85]

do what you think will look best, I like the idea of it all being on one tab, then that being easy to email, as its simple to pass into up the CoC in a nice set format. you no how it is bullshit baffles.

[dflak]

I need one more thing before we are ready: tell me, by sheet name, the sheets you want mailed. That is the only bit of development I have left.

As for coloring the R1’s, let’s leave that as a future enhancement. I think it would be more advantageous to go after the low-hanging fruit on the new requirement, get the critical 80% there and come back and pick this issue up later.

Although I tested pieces of this, I’ve not done a thorough integration test or regression test. It is in your best interest to run it through its paces.

You will see that I added some “whistles and bells” on the Training Due Sheet to show clearly what is being tracked. The filters for both pivot tables are driven by what is called a slicer.

I also added something called “sparklines” to the Print Copy Sheet. Sparklines are miniature graphs. In this case they show the distribution of Current, Within a Month, etc.

Both slicers and sparklines are ridiculously easy to implement, so I put them in because they look cool.

[dflak]

I thought of something as soon as I hit the post button. I forgot to hook up the new macro to the button. This is the good version.

[Ross85]

training due, roster and metric

that would be fab

[dflak]

As I mentioned in my mail message, I can put roster and metric on a single page and make it look good. There is no need for these to be interactive anyway.

I'll keep training due as a regular spreadsheet because you might want to filter on a course or a name. Offering Course XYZ next month? Who should you schedule? Or Joe Jones just transferred in. What training does he need?

We might have an issue with the A3 paper. As I mentioned in the mail message. You'll need to format the big reports. We'll probably have a go around or two with the single sheet layout - it's not just a matter of paper dimensions. When I copy a picture to a sheet, I tell it where to put it based on 72 dpi. That's dots per INCH!. I don't know how that will translate into cm.

I'll work a mockup of what the metric sheet will look like and pass it on shortly.

[Ross85]

do whatever you think best! totally believe in your judgment.

[Ross85]

getting a fault, please see attached doc.

same doc I sent you, I just kept the personnel's details in, also filled out the roster page.

[dflak]

Images do no good. Especially when I can't see them. Like may other members of this forum I can't see PNG images. Attach a JPEG file. Better still, send the exact file you are using. Telling me what the fault is usually useless. Showing me what line it occurs on helps a bit more. What I really need to see is what the values are, so I need to either reproduce the error, or I'll teach you how to debug the code and get the values for me.

[dflak]

Good news. If I lie about what printer I am using, I can select A3 paper size. Attached is a mock-up of that the Metrics page would look like.

I have no way of printing this to check.

[Ross85]

teach away,

will make a Jpeg also

[Ross85]

that sheet looks amazing

[Ross85]

pics attached

[dflak]

I'm going to let you fix this one yourself.

Take the underscore out of Find_Due so it reads FindDue.

To open the VB Editor open the spreadsheet and click ALT-F11. Then find the module ModMain and double click it if it isn't already open. This code is near the top.

[Ross85]

ha, nailed it!!! worked straight away

[dflak]

Yes, that was a typo on my part.

If you experience a failure in the future. What you sent me was a good first step. That tells me the line of code on which the program failed.

Then after I look at it, I might have follow-on questions about what some of the variables are set at when the failure happens. So either with the failure still up there or run the program to make it fail again, I'll tell you what to right click on and the value of that variable will appear. Quite often this can pinpoint exactly where the failure is - such as when we got N/A for the first time, or at least it will leave me scratching my head with "How the heck did it get to be that value?"

If it's more complicated than that, then sending the file so I can duplicate the error is best. There are ways I can step through it and see what's happening as each line is executed - what the values are at that point, and where the logic takes the program. The problem with this program is that there is so much data, that what takes a fraction of a second to blur by while executing in real time can take a number of minutes to step through manually. Sometimes I have to set "traps" to catch the data doing something bad.

[Ross85]

your a complete star, I do find this interesting, will takes a few decades to get anywhere near your standard.

[Ross85]

its running the program but not picking up/showing on the master what there R1 or CR.

both docs are in the same folder

[Ross85]

its appearing correctly on the metric, the correct numbers of personnel in each category

[Ross85]

so the persons name is appearing for example steve steven (_/_) no cat or para Lv

[Ross85]

So, Uploaded the documents today. but have seen a little problem. when I upload them. I MUST apply the naming convention. see attached doc.

this is creating issues such as this, I believe its the naming convention that is throwing the program out, I'm sure you'll be able to tell me either way

[Ross85]

these are all getting uploaded into the same location. so I'm hoping its a simple fix.

BTW its blowing minds.

[Ross85]

also still having the same issue with the naming its not showing the personnel's Roaster Information, next to their name The Charts are working correctly though.

sorry not meaning to be a pain, just out of office for a week, so would like to get this put to bed, so they can run it, test it for the week I'm away.

[Ross85]

this is how it looks when uploaded

[dflak]

What is the picture in your last post telling me?

The file names have to be exactly as indicated in the instructions. I can do more with this if the file names are predictable. Do check for the correct file extensions.

If you are not getting (Category / Level) it means that the service numbers in the CSV file are not matching up with the Service numbers in the Unit Roster file.

[Ross85]

I have cut and pasted from the CSV file for the roster. Will check when I get in.

The last pic, is how the files look when uploaded.

The naming convention is a must. As soon as you upload some the files they make you put thee naming convention.

Say the CSV file it will give it the naming convention year month day. I tried to delete this but it makes you use this format.

So when I upload the CSV file for next month it will say 20170203

I have no idea how to get round this?

[Ross85]

The weird thing was. The top name worked with the (cat/ lev) but no other names?

So it was

John, Johnston ( R1 / CR )
Steve, Stevins ( _ / _ )

[Ross85]

Sorry I never predicted or could even foresee these problems.

It's all very new to me. I do appreciate you taking the time to help me out.

The last picture was to show you they are all in be same directory

[dflak]

If I can predict the name of the file, I can work with it. So you are telling me that the next month's file will be 20170203.csv. You will be downloading this file in February. The reason I ask is that I can use the current date to make the file name - and I will give you the option to override the file name.

I do not know the issue with the (Category / Level) - without seeing the actual data I don't know what is going on.

[Ross85]

Well it depends what day you upload. If was to upload tomorrow, it woks be 20170128.

But was thinking I could just paste the new data in.

I will re try the cat/Lv when I get in make sure I'm not being a idiot. If no luck will send you the info

[Ross85]

This is the data I'm using, I have removed names, for security.#

you will see what I mean about the Roster (Cat/Lv)

[Ross85]

sorry back home now, I'm saying when I upload any file they are stamped with the naming convention, so I uploaded the 3 files today, they where giving the naming convention of 20170127-ODR.CSV.

the file properties stay the same, but they must have the naming convention before the upload is completed. I tried to leave this out and it wouldn't let me finish the upload.

the red * in means I must fill this data out. the 2 swirling arrows must be hit, that then produces the time stamp. I have no idea if there is a work around. sorry!

[dflak]

OK. I can deal with the file download easily. If for some reason you can rename it to the fixed name I have, I'll get the program to look for a file name with the format YYYYMMDD-ORD.csv. Where the YYYYMMDD is calculated on today's date.

[Ross85]

sounds like a plan.

what was up with the Cat/Lv ?

[dflak]

You are not going to believe why the unit roster category / level was not being updated properly. There is a hidden sheet called Roster. This is the sheet into which I copy the unit roster at run time. It was copying the service numbers in properly, but the column was too narrow to show it. So Excel figured I must want the number in scientific notation. So 30189415 became 3E+07 as did the rest of the numbers. The Major made it because his number was narrow enough to fit in the column. At least we know the part that makes the name when the number is not found works.

I took a look at the results - at a high level, they look good. I did notice that you had no exemptions. Just be aware when you get to the training due sheet that exemptions should not be turned on. This is mentioned in the documentation. I think if exemptions show up, they will be "turned off" by default.

There is no difference between this report and the previous version other than I made the column on the hidden roster sheet wider.

[Ross85]

ha, that's unbelievable. I cannot believe it was so simple.

I was racking my brains.

[dflak]

You are not going to believe why the unit roster category / level was not being updated properly. There is a hidden sheet called Roster. This is the sheet into which I copy the unit roster at run time. It was copying the service numbers in properly, but the column was too narrow to show it. So Excel figured I must want the number in scientific notation. So 30189415 became 3E+07 as did the rest of the numbers. The Major made it because his number was narrow enough to fit in the column. At least we know the part that makes the name when the number is not found works.

I took a look at the results - at a high level, they look good. I did notice that you had no exemptions. Just be aware when you get to the training due sheet that exemptions should not be turned on. This is mentioned in the documentation. I think if exemptions show up, they will be "turned off" by default.

There is no difference between this report and the previous version other than I made the column on the hidden roster sheet wider.

[Ross85]

cannot believe it was so simple.

how the hell did you figure it out?

[dflak]

I went to the sheet and looked. I had to expand the column to check that the numbers there matched the Unit Roster sheet. When I did I said, "Hmmm, I wonder if that's it?" It was.

Most of my best discoveries start with "Hmm. that's odd."

[Ross85]

I'm gonna have to start getting some homework off you, so I can learn all this.

[Ross85]

I just found it strange that the Maj was getting picked up. I look at the formulas and they all looked spot on. I just cannot believe it was because the cell was to narrow. the simplest of things

[Ross85]

so is the file ODR 8c ready to work with the yyyymmdd?

[dflak]

Version 9 uses YYYYMMDD-ODR.csv. Take a look at the Instruction sheet. There is some new information there.

Cell F10 now has what the program thinks it should be using based on today's date. If you like it, do nothing. If you don't like it enter what you think is correct in cell H10. The program is going to use whatever shows in J10. This part has been tested. It's not been documented yet.

I think I have what I need to make the A3 page. Now that I know what sheets to mail, I'll work on the mail part on Monday.

[Ross85]

works brilliantly, not received the instruction sheet yet. I'm sure your smashing that out the park already.

you must show me how to do all this, its amazing.

[dflak]

I believe we are ready to fire for effect:

[Ross85]

looks the bollocks.

I uploaded the ODR version 9 yesterday, when I tried to run it I had the same fault, could this be because it gets uploaded to MOSS, it sits on a server? the attached pic is where the documents are saved. could this be having an effect. as when this document is uploaded personnel from my Unit, will be able to use this, as long as they have DII access any where in the world.

[dflak]

I'm not sure I can help you there. Somehow you are going to have to download the CSV file and get it in the directory with this spreadsheet. I don't know what your process is for that. Each squadron should have its own copy of the spreadsheet and roster.

In the past, I built a web crawler that opened a dashboard page, logged in, navigated to a page, got the contents of a drop-down box, ran a report and downloaded the report. However, I had unrestricted access to the dashboard to develop it. I'd have to relearn it all.

I think you are going to have to click the buttons manually.

[Ross85]

no, when I upload all the files up onto MOSS all the documents are in the same location, but when I try to import the data, its not working!! I'm asking if this is because of it being uploaded to the Moss sight? is so is there any ways around that, Happy to upload the docs, but like I said when I hit import getting error!! just wondering if this is because of it being uploaded on to the server?

the last pic is where is loaded onto

[dflak]

I'm going to assume that your Business Intelligence System works like ours.

We log into the BI tool and navigate to the report we need. We then select the parameters we need from various drop down prompts and click on an apply button. This runs the report that is displayed on the screen. At the bottom of the report are two buttons: one to print, the other to save. If you choose save, then you are given the option to choose several formats (Excel, CSV, PDF ...). In this case I would choose CSV and save the file to my local disk.

The report is designed to run in a directory on the local disk. You will need to get the spreadsheet, the CSV data file and the Unit Roster file in the same directory on the local computer.

I cannot say what happens in your private cloud. It is quite possible that the spreadsheet does not have the permissions it needs to do its job.

[Ross85]

yeah, that makes sense. sorry about the late replies. I'm out off office at moment on a course.

I will get this uploaded today and get it running.

will give you the feed back, very soon!!!!

[dflak]

Got your message. I don't know if this will do any good since I am still not sure of your directory structure and why you can't download a file from your report system to the local drive.

This version of the report allows you to enter in a path name in cell G13 on the instructions sheet. This is the directory where the Roster and CSV file must exist. I don't know how you will plan to keep your roster file and data file separate from other units.

The ODR spreadsheet itself can be anywhere on the network that can "see" the folder entered in Cell G13.

[Ross85]

both files are kept in this location. when I right click and look at properties their path are this

http://cui1-uk.diif.r.mil.uk/r/244/5...20squadron%FOE

if I pate that in will it still find them in the directory?

[Ross85]

here you can see all 3 locations, when I re-upload them they will have the file naming structure of 20170207.

I hope that makes sense?

[dflak]

Put in the URL for the address and see if it works.

There might be an issue with the path and file names. %20 is HTML-ish for space. There would be no problem with the source csv file since it's a single word. We might have to rename the Unit Roster file to Unit_Roster - I'd have to check the code to see where I refer to it before changing the name will make it work.

Then I suggest you try entering the URL both ways with %20 or with a space instead.

Standby for version 10.2.

If this doesn't work then I suggest you contact your system administrator and ask them how to download a file from the system to your local disk. I can't believe that the system doesn't have this capability.

[Ross85]

yeah I see what you mean, will the naming convention throw this out. having the 20170207-ODR have an effect on the code?

[dflak]

Change the name of the Unit Roster.xlsb file to Unit_Roster.xslb and use this version. This will remove the ambiguity of whether to use %20 or space in the file name.

As for the path, try both ways with %20 and with spaces to see if either works.

[Ross85]

yeah, thank you. I was trying today. Well these documents are sitting on the server //cui1. I was chatting to a guy today who has passed this issue up the chain (I think I had more knowledge than him, he just clicks boxes and follows a protocol) this this out the box issue stumped him, however I should hear back in 12hrs.

I did say that when I was putting these documents onto the server. when I was running the import the File path was not being found so was getting a path error.

He was explaining that it was being uploaded to the server, but its on as a hyperlink. So the files I upload are only shown to me as hyperlinks.

I have spent all afternoon on this, but with my limited knowledge I can only go so far.

I have no idea how to solve this issue. I was hoping that maybe someone on this site could chip in with a suggestion.

So far you (dflak) have been am absolute superstar!!!!

[Ross85]

I have also figured out how to upload the CSV doc with out the 20170207-ODR.csv naming convention today.