Excel or VBA gurus, help please!

[mboy]

Here is the situation. My employer asked me to come up with something that would do the following:

We have an excel spreadsheet that will be placed on a server. There will be 4-5 people that will have to access this file so they can each input their various peices of data. Easy enough. The tough part:
They want to file to be emailed to 4-5 other people WHEN ALL THE DATA IS ENTERED and everything is calculated. So when all the data is entered, they want it automatically emailed to those who need to have it. This will go on every week.
I know very little about Excel (thats why I am the Network admin and not the programmer, database guy .
ANy help with this would be SUPER appreciated. Thanx!

 

[thornnspear]

Excel is not the tool - you'll want an Access DB with wither VBA or ASP forms to control data.

I wrote an app at my last job that gathered data from oracle tables, and every 5 min would email if certain values were present.

I've never delt with report output though...someone else?

 

[CodeJockey]

Excel can do that, provided the people entering data can each learn to click a button after they are finished.

Simply create a button (select Command Button from the Control Toolbox) on the worksheet, double click it and you are taken to the VB Editor to write the code for a macro that will be run when the button is clicked. Write VB code that checks the various cells in the worksheet to see if everyone has entered their piece(s) of data. If all data is present, the spreadsheet is emailed to whomever you like. If more data is required, nothing special happens.

The VB macros are fairly easy to write...especially if you use the macro recorder to generate code that does what you want.

The VB code can be very powerful (doing all sorts of validation, cross checking, etc.), or it can be very simple...depends on your requirements and your expertise. I would suggest starting very simple, then expanding and extending it once the basic system is working for you.

Good luck.

 

[mboy]

Thank you for the reply. Unfortunately, I know ZERO about writing macros for excel or VB.

 

[CodeJockey]

That's why I suggested the Macro Recorder (go to Tools->Macro->Record New Macro)...it records all actions that you take and writes a macro (which is actually just a VB function or subroutine). It provides some handy examples you can use to see how to select a cell, set a value, etc. Just cut, paste, and edit to change the way it behaves.


As I see it, they have 4 choices:
- Have you learn enough to write the macro, test it, and get it working
- Assign a developer to do the above (probably faster than you could, but takes them away from other work that may or may not be more important)
- Hire someone (perhaps a consultant) to do the work (possibly costly, or may result in useless crap)
- Abandon the idea


Below is a commented example that reads through a list of values in column A (from A1 to Ax, it ends when it hits an empty cell), and counts the number of unique values that it sees. It might serve as an example of basic control (looping, testing conditions), reading and writing cell values. Sorry about the formatting, but the forums remove all leading spaces, so it looks like crap.


Private Sub CommandButton1_Click()
Dim curRow As Integer
Dim j As Integer
Dim UniqueTotal As Integer
Dim unique As Boolean

curRow = 1
UniqueTotal = 0
' Ensure that there is at least one row of data
If (Not IsEmpty(Cells(1, 1))) Then
UniqueTotal = 1 ' So far, there is one unique cell
' Keep checking rows until we hit an empty cell
curRow = 2
While (Not IsEmpty(Cells(curRow, 1)))
' Check cells until we encounter an empty cell
unique = True ' Assume it is unique
' Check the current cell against all cells above it
For j = 1 To (curRow - 1)
If (Cells(curRow, 1).Value = Cells(j, 1).Value) Then
' Not unique, it matches another one above it
unique = False
Exit For
End If
Next j
' See if it was unique or not
If (unique = True) Then
' Yes, so count it
UniqueTotal = UniqueTotal + 1
End If
curRow = curRow + 1
Wend
End If
' Finished, Place Unique Total into the worksheet
' 'curRow' contains an empty cell, so place the total
' in the cell just below that one.
Cells(curRow + 1, 1).Value = UniqueTotal
End Sub