Plan of Attack for VBA Multiple Forms to Multiple Emails, wedged into worse practices

[Volos]

Hi everyone, I thought I would give this forum a second go. Even if I answer my own questions again, hopefully it might help someone else. I work mostly in computer vision, and thus my language experience didn't include VB until about month ago (though I think I'm learning fast), when I was co-opted to help my lab move.

I was wondering what the best practices/plan/logic flow would be for the following problem:

1. taking data that is in SQLServer with multiple tables, do a one time migration to an Excel Spreadsheet [I know this is soo backwards]
2. create a daily form sheet (in Excel) where a user can input several manual readings (thinking of these as global variables) [coming from analog readings...I may try to automate that later]
3. create a user-specific report based the newly entered global values and user-based variables/constants [currently about 60 users, user variables include things like distance multipliers]
4. send all the users their user specific email, preferably in the body of the email (I just got some of these guys off faxes, don't want to teach them to read a .csv file)

My first thought when given this project was to handle it all in SQL (where I'm comfortable), or do a port/C++ ODBC . Boss man wants it all in Excel, in an macro. I don't feel like that's the best way to do this, but he seems to hate updating the original database, and so he just want to "put everything in Excel and push a button." In case you are wondering, not only did I not design the original database, I haven't even used it before today.

Going into my first thoughts as I try map out a plan:
I was going to put each of the tables [users, locations, etc] into it's own sheet. Would it be better if it was it's own file? Then I will make another sheet for the for the daily readings to be plugged in. And finally have a button to run a monstrously large (perhaps I don't have perspective yet) macro.

Where would the best place to store each user report be? Make an CSV file? Keep it in memory until it's sent to Outlook then dump it in a loop? Boss man uses Outlook, but I may not have to use it. Assuming that I do end up using Outlook, if I use .send in a loop, he won't actually have to click send or anything to have the reports mailed to the users? That was my understanding, but most of the examples I saw included a .display line (which I assume I can just leave out).

Thanks in advance for your input, and cheers to you all who do this all year long. I'd hate it.

[Ecniv]

If you are happy in SQL... so stick it in there.
The top man is not comfortable updating main data - don't. Drop all the users bits into a separate table. You can then run validation etc on them before commiting to the db.

Initially sounds like a data capture and entry to db. Will the users need to see anything? Will the users need to update existing data or just add new ?

Can you not connect Excel direct to the SQL in the connection string? So the user sees only a data capture and click a button, but behind the scenes it goes to your fave environment; SQL server.

[Ecniv]

Boss man uses Outlook, but I may not have to use it.
Assuming that I do end up using Outlook, if I use .send in a loop, he won't actually have to click send or anything to have the reports mailed to the users?

He still will need to click, but it's just a warning that says something is trying to send an email on your behalf, do you want to...?

.Display only generates the email onto the screen for the user to view before they send.

[Volos]

If you are happy in SQL... so stick it in there.
The top man is not comfortable updating main data - don't. Drop all the users bits into a separate table. You can then run validation etc on them before commiting to the db.

Initially sounds like a data capture and entry to db. Will the users need to see anything? Will the users need to update existing data or just add new ?

Can you not connect Excel direct to the SQL in the connection string? So the user sees only a data capture and click a button, but behind the scenes it goes to your fave environment; SQL server.

Good to know others have the same initial thoughts I did. There are some unfortunate problems. First the poor old server the DB is on is slated to be junked, and a copy of the DB is to be thrown on optical media then put in a box where it never sees the light of day (unless there's a problem). I realize I could run the DB locally, but the boss man (let's call him BM) wants everything in Excel. For lack of a better word, I don't think he ever "trusted" the data in DB as he felt he couldn't directly change and monitor it. This despite the fact the PHP front end seems to be operating correctly. I tried to point out it had much greater integrity in SQLServer (or even ported to MySQL which is what I really want to do), but I got shut down. Perhaps I should take some negotiating classes.

Any way so the spec and scope is in VB, so I'm at least going to try and cobble it together from Excel.

The users table is a bit misleadingly named. It would be better named "field_agents," as the people are never connecting to the database (which was only locally available anyway). In fact, BM and one other person have been handling everything on the DB as sa (Skrik moment now). Really the 'users' table is only relevant to the future of the project in so far as it contains their contact information, and a series of user-specific base values to be used in some logic.

He still will need to click, but it's just a warning that says something is trying to send an email on your behalf, do you want to...?

.Display only generates the email onto the screen for the user to view before they send.

Right so on this, if I put generating the email in a loop, he will only have to do this once as a security measure, or does each message need to be confirmed. My first simple test this morning would indicate that it only has to happen once (two blank emails to myself).

[Ecniv]

I think the email (MS Outlook) asks once and allows vba code for ten minutes... after that it will ask again.


So essentially the fairly decent (if old) solution is slated to be removed and the BM wants it all in excel cause he can change as required...

Option to use Access?
At least you can keep a relational database using it. Maybe a VB front end..?

I'm not comfortable with the

"trusted" the data in DB as he felt he couldn't directly change and monitor it.

that statement could be very worrying


I hope you find a solution that works for you n BM

[Volos]

Access might be a viable option, but at the moment I'm thinking an entirely VB front end that prompts for the daily values and pulls from csv files for client data. Is there any reason that I would have to store each individual report somewhere, or could the emails efficiently be created that sent the values of 40 or so variables (created from global+field_agent) that are recalculated and sent on a loop?

For one report I currently have 75 calculations, 4 Gets, and then however I'm going to email. I'm worried on the time over a 60 iteration loop. I guess I will start benching the time by spamming myself hardcore.

I hope to have a semi-working code up by tomorrow night.

[Volos]

Ok so I'm moving along in hacking this together in VBA, and I have actually imported the whole DB into one Excel file (turned out to have 12 tables, all of which I put on a separate sheet). I've already groaned enough for the lot of us on that regard. As I've mentioned I'm new to VBA, and I'm noticing there's no skip/continue command. The records from the DB aren't perfect, so if I don't have the email for the contact, I don't want to take the processing time to draft an email I can't send. The work around I have is nesting the whole operation in an intial if statement:

Sub Terminal_Arranger()
 Dim terminal_count As Long
 Dim terminal_list As String
 Dim i, count, x, lastRow As Long
 Dim SpData As Worksheet
 On Error GoTo Err 
 Set SpData = Sheets("User-data")
 lastRow = SpData.Range("A" & Rows.count).End(xlUp).Row
 SpData.Select
 For i = 2 To lastRow
   If IsEmpty(Cells(i, 29)) Then
    Cells(i, 32).Value = "No Email"
   Else
     ///rest of my program
   End If
 Next i
 Exit Sub

Err:
   ///error handling

End Sub

Is there a better or faster way to handle this? I don't want to exit to my Error handling for the scattered invalid enteries I have. I am still cranking through the data validation and repair (I'm cheating and using python for that), so this may not be an issue in the future, but I would like to know the way most people handle it.

Cheers,
Volos

[Ecniv]

Either that or get the length of the cells value

if len(sht.cells(1,1))=0 then 
'there was nothing in the cell
else
'there was something in the cell
end if