Populating A Master File - Managing Work Log Hours

[logann2292]

I am managing the volunteer hours for a volunteer fire department. In a nutshell, the member logs in to the website, fills out a form, and then at the end of the month, the admin exports the data. The website template provider doesn't allow us to change the export features, but we can change the type of fields that are populated in the form.

I can't figure out the best way to populate a "master file" because the downloadable export is going to have different data every time. For example, If name = John Doe, only populate the hours for his name.

At the end of the day (month), I need the admin to be able to view the volunteer log history in a few different ways. He would like to be able to see the total hours by person for the month, and the volunteer history of an individual person by month. I have thought of several different ways to do this, but can't figure out the best. Any help would be appreciated.

http://www.lebanonvfd.org/content/apparatus/exportfromwebsite.png

http://www.lebanonvfd.org/content/apparatus/masterfile.png

 

[HackSlash]

That export data from website looks pretty good. You could do it by column, or if the columns change you could do it by column header text. I can see that you want a "master file" that has a different person on each tab of the report.

What is the data selection when you export from your website? Is it already per user or do all users come out in the same export?

 

[logann2292]

Thanks for the reply. It comes out as the data is entered. So if Member A submits time, then Member Z, then X, etc, it comes out chronologically.

That export data from website looks pretty good. You could do it by column, or if the columns change you could do it by column header text. I can see that you want a "master file" that has a different person on each tab of the report.

What is the data selection when you export from your website? Is it already per user or do all users come out in the same export?

 

[HackSlash]

Ok, this is the way I would do it.

Make an import data macro to analyze the export and update your master. The macro would live in the master. You just open the master and run this program at each interval. For this to work the Name in the export must match the name in the sheet tab exactly and we can force that by generating the sheets as we go along.

1. Start by making a macro that opens your CSV file with the correct format settings so that the data comes in the same way each time.
2. Make a FOR...NEXT loop to step through each row where the name is kept (Looks like your A column)
3. Check the name against the worksheet names in the master using a "sheetexists" function that steps through thisworkbook.sheets(i).name
4. If a tab for that name doesn't exist then make a new sheet named after the person or select the tab that exists
5. Copy the data out of the current row in to the sheet however you want it
6. Paste it in the next row after the last used row on the master sheet
7. Next row

Each one of these steps is a small block of code that you can write one by one. I'm not sure what your VBA skill level is but it should be clear that nobody can write this for you without a lot of information.

You should be able to look up how to do each of these things and then put them together.

Come back when you have some code if you need more help with it.

 

[logann2292]

Will do! Thanks for the suggestions. Currently having trouble with a VBA because of something from the export interfering. Although the form's export from the website has the user enter a Time start/time end, unfortunately for some reason when the export loads in the time fields a date 1/1/1900 is generated.

Additionally, the times are being generated wrong. Ex: Time in is 11:00 (am) and time out is 14:45 (pm), note no date entered. What generates is corrected military time, always AM. The output for the same example ends up being: Time in: 1/1/1900, 11:00am; time out 1/1/1900, 2:45am. Can't really proceed until I figure out if it's something I can do or if the website design template I purchased (no edit access in the root) has to be modified. Thank you for the help though, going to try to roll with your ideas on a dummy file for now.

 

[logann2292]

*For Clarification for Hackslash & Anyone else that would be willing to help me get started, here are more images of the database/form creation, the export, and the error.*

...What is the data selection when you export from your website? Is it already per user or do all users come out in the same export?

...the times are being generated wrong.

Link 1: http://www.lebanonvfd.org/content/apparatus/editdatabase.png
Admin Database Overview w/ "Export to Excel" Noted (see link 4)

Link 2: http://www.lebanonvfd.org/content/apparatus/newfield.png
Admin Database Edit Form Options

Link 3: http://www.lebanonvfd.org/content/apparatus/adminview.jpg
Admin Submission Summary (Note Users view is similar & User can see User's "New Entry" button also, which if clicked by user, would be seen as in Link 5.)

Link 4: http://www.lebanonvfd.org/content/apparatus/exportwitherror.png
Admin Export w/ Auto-Generated Year of 1900?

Link 5: http://www.lebanonvfd.org/content/apparatus/usersubmitview.png
User Submit View (admin settings shown because I'm logged in as admin.

 

[HackSlash]

You need to format your cells to match the data for it to display properly. Time is represented as the number of milliseconds since midnight. The format command allows Excel to translate "14:45" in to this number.

This example would take the value from F10 and put it in to A5 with the proper formatting to turn the string in to a time value that excel understands and can do math on. This is a prerequisite to doing the total hours calculations you want. Just keep in mind that everything you are importing from CSV comes in as a plain text string. If you don't tell Excel how to handle the data it will guess and you don't want it to guess.

Range("A5").Value = Format(Range("F10").Value, "hh:mm")

See this command reference:
https://msdn.microsoft.com/en-us/library/office/gg251755.aspx