Help with multi application project planning

[davidboutche]

I'm planning to start a new project and would appreciate some guidance to confirm to me that i'm going the right way about it, or other ideas.

My organisation has case files. In those case files are held the data for several key people. The files and data are usually added progessively as the case builds.

The files are mostly paper based and new form have to be filled with the same data again and again.

What I want to do is use forms to input the data and store it for future use creating autopopulated forms. What I'm mostly unsure about is where I should store that data. Presumably word forms don't store the data once the file has been closed?

Therefore should I get it to write that data to a line on an excel spread sheet, or would another option be to store it in bookmarks in a word document for later use?

As the extra forms are created I want it to save the documents with predefined names and locations based on the case file name.

Are there any examples of this sort of project any where?

Help appreciated.

David

[lucas]

Here's one that tinbendr was working on that writes to the next line of an excel file:
Sub AddToExcel()
' paste information from O&A into Excel Sheet
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim FileNum As Integer
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
'specify the workbook to work on
'WorkbookToWorkOn = "C:\Users\Steve\Documents\PIM.XLS"
WorkbookToWorkOn = ThisDocument.Path & "\PIM.XLS"
'Define Worksheet name
WorkSheetName = "Sheet1"
'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If
On Error GoTo Err_Handler
'If you want Excel to be visible, you could add the line: oXL.Visible = True here;
'but your code will run faster if you don't make it visible
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
'Process each of the spreadsheets in the workbook
'Set oSheet = oXL.Workbooks(WorkbookToWorkOn).Worksheets(GlWorkBookName)
Set oSheet = oWB.Worksheets(WorkSheetName)
oSheet.Activate
'Paste fields from Word userform to Excel table
Dim RowI As Integer
'Find last cell with data in column A.
RowI = oSheet.Cells(oSheet.Rows.Count, "A").End(xlUp).Row
RowI = RowI + 1
'Title
oSheet.Cells(RowI, 1) = ufCHBlettergen.cboxTitle.Text
'Fullname
oSheet.Cells(RowI, 2) = ufCHBlettergen.txtFullname.Text
'Address
oSheet.Cells(RowI, 3) = ufCHBlettergen.txtAddress.Text
'CHB End Date
oSheet.Cells(RowI, 4) = ufCHBlettergen.txtCHBenddate.Text
'Processor
oSheet.Cells(RowI, 5) = ufCHBlettergen.txtProcessor.Text
oWB.Save
If ExcelWasNotRunning Then
oXL.Quit
Else
oWB.Close
End If
'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
'quit
Exit Sub
Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, "Error: " _
& Err.Number
FileNum = FreeFile
Open "Error Log.txt" For Output As FileNum
Print #FileNum, WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, "Error: " _
& Err.Number
Close FileNum
If ExcelWasNotRunning Then
oXL.Quit
End If
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
End Sub

[Tinbendr]

I'm planning to start a new project...

My organization has case files. Wa-hoe... bit off a chunk here!

I'm sure others will have better advice, but I can tell what I've dealt with.

I, too, store information as you describe. (But much more abstract; a bunch of numbers.) When I started that project, I was storing it all in Excel. It didn't take long for the file to become unweilding. So, I sucked it up, rewrote it and moved the data storage to a Access database. The program worked so much better and faster. And the file size when down by about 75%.

So, I guess my first question is, do you have Access? (Guys, does he even have to have Access? Can he just have the DAO/ADO/Jet library?)

If not, then, you can do something like what Lucas has posted.

What you propose is very doable. It will take a little effort to perfect it for your situation.

One last piece of advice. Don't use Word tables as a storage location if the list of case files will be very long. Word tables get a little flaky when they get large.

[davidboutche]

Thanks for that. Ideally I would have loved to have built the whole lot in access but the organisation doesn't have it. That's where the idea of using excel tables came from as next best thing.

The case files (in most cases) won't be very big at all. It will consist of several data to be held on several people. I can't give specifics away but I could say it would consist of:

1 event
Several Witnesses to that event
and ususally 1 key player in that event.

I just really want to hold the data for all these people having entered it in a word for and then create forms based on that data.

Then for it to save those forms in a directory based on the key player's name or event title.

[geekgirlau]

Sharepoint also springs to mind, or a document management system - depends on what you have available.

I would suggest you start off with Excel, iron out the kinks, and then put forward a really good argument to have a formal process developed.

[Charlize]

So, I guess my first question is, do you have Access? (Guys, does he even have to have Access? Can he just have the DAO/ADO/Jet library?)

You don't have to have Access to deal with a .mdb database . You can even create an acces database by coding.

Charlize

[fumei]

You need to really spell out EXACTLY your requirements.

"Presumably word forms don't store the data once the file has been closed?"

Well, yes and no. If the data is text, then of course it "stores" it. It is text in the document. It is "stored" just like all other text is stored.

The no part depends on what you mean exactly by "data". Word can store data in Variables that persist when the file is saved and closed. Obviously if the data is a variable, and NOT put into a DOCVARIABLE, then it is not stored (persist) when the file is closed.

Nevertheless, depending on your exact requirements, it may be better to function this in Excel. However, if the data is text, then I would not do that. Excel is not designed for text (no matter what the obsessive Excel people say).

[fumei]

"I just really want to hold the data for all these people having entered it in a word for and then create forms based on that data.

Then for it to save those forms in a directory based on the key player's name or event title."

My bolding.

Do you really mean "create documents based on that data" and "save those documents"?

What I am trying to get at here, is that if this information - event, Witnesses, whatever - are judiciously placed in bookmarks, then it is quite straightforward to:

1. get that information out programatically (to create a new document with that information, say)

2. propagate that information elsewhere - i.e. that information from a one location (bookmarked), and duplicate it at a another location(s).

[davidboutche]

Thanks for the advice from everyone so far.

Charlize, I appreciate what you say about building the mbe table manually but i thin that is going to be a little bit beyond me.

And I appreciate that comments about sharepoint.. which I like except the organisation uses different versions of office and which interact with sharepoint at different levels and I'm not so used to getting vba to interact with sharepoint.

I'll try and explain the process that i'm trying to automate:

* Events occur that we look into signified by a pre defined unique number

* There will often be several witnesses to these events

* There will normally only be one person to be at a loss from these events

* There will normally be one person who is either known or not known who is the cause of these events.

The organisation has !LOTS! of word forms that relate to the people involved in these events. Very often the data is repeated and shared across these forms causing the user to type it in again and again.

What I want to do is use a user form to start a new case. Rather than one large database of linked tables I think the best way may be when a new case is started it opens a new work book and saves it in a new folder based on the unique reference number.

I would then like to use a userform to add people to that table with more or less data depending what type of person they are (witness, victims etc).

Once I have people added to the table I would then like to have userforms with a drop down showing the people in the table and then buttons which will open word forms which will then copy the data for that person into the appropriate book marks in the form.

I'm thinking now it may be best to start the project in excel with is userform there?

Hope this all makes sense.

[Tinbendr]

This type of simple file management can be done it either Word of Excel.

You need to decide how you're going to manage this.

I'll assume each case number is unique.

You can use Word or Excel to create an index of case files. Each row would store some of the information from the document. (Case#, Case worker, Name, Address, Form #1, Form #2, etc.) Use the index document as a starting point for creating the forms.

To me, the only difference in using Word or Excel for THIS type of doc management is:

Word
Pro: You're working with VBA in the application that you will be creating the doc in.

Con: Word gets a little squirrely when the table gets too big and you'll need to break it up; maybe by year. (I have something similar to this. It routinely get to 300 rows without any trouble.)

Excel
Pro: You can easily have 10 years worth of data stored. (Although, I wouldn't recommend you let it get this big.)

Con: You're not working with VBA in the app. you be creating the doc in.


The organization has !LOTS! of word forms that relate to the people involved in these events. I hope they have then uniquely identified. (numbered for instance), or it's going to get difficult to keep track of which form needs to be called for creating.

Why don't you whip up a few sample documents, and show us what you're working with and what kind of info you need to recall.

[fumei]

Good comments tinbendr. Although your con vis-a-vis Excel is not all that serious.
Con: You're not working with VBA in the app. you be creating the doc in.This is not a big deal because in any case, either way - Excel to Word, or Word from Excel - you are going to use VBA instances of one of them.

The point about Word getting squirrelly with large tables is a point to be seriously considered.

David, tinbendr is correct: it can be done from both Excel and Word, but the MOST important thing is his second comment.

You need to decide how you're going to manage this.

You have to decide. Write it all out. I am still a bit unclear as to what - exactly - is required. However, the principle of doing such management is quite within the capabilities of Word and Excel.

You did not make any direct response to my posts, so I will let tinbendr continue with trying to help. I would suggest though that you try and get some terms clear, because it may help in the long run.

"The organisation has !LOTS! of word forms that relate to the people involved in these events. "

Again...do you actually mean !LOTS! of word documents?

[davidboutche]

Sorry yes, the organisation has lots of word template documents that currently use userforms to capture data and fill the bookmarks. Often the same data has to be retyped again in other word forms.

[davidboutche]

what i'm trying to do is stop people retyping the same data for use in new forms but refering to the same data. Also it would be nice to have the cases all kept together nicely with all the key peoples details kept in separate spreadsheets.

Does that make sense?

David

[davidboutche]

Dave (Tinbendr)

Attached is the basics of what I'm tyring to achieve.