If you have finalised your sheet layout and can describe what you want to do clearly and concisely then lets hear it.
If I had an Excel specialist sitting next to me I would simply say...copy that as best you can..... and that would be what I have on Access. Not copy a whole data base but the part that handles all correspondence in Word.
In Access the person using it opens a form from the clients mainform and that form is where everything that is Word related is done.
On that form are a series of labels for pre prepared Word docs as well as for general letters. These would be for such things as product comparisons and so on. The person clicks the label and the following happens
1) The Word doc is opened and the details of the client/prospect are inserted to bookmarks along with details for the letterhead.
2) A texbox on the form gets the following
Forms!PrintAndClose!LetterName = ("" + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss") + " " & ([Forms]![PrintAndClose]![CLSurname]) + " " & "Name or Type of Letter" + ".doc")
3) Next is
WordDoc.SaveAs ("c:\StoreLetters\" & Format
([Forms]![PrintAndClose]![LetterName]))
4) Next is the openening of a form for a Many record.
DoCmd.OpenForm "LN", acNormal, "", "", acEdit, acNormal
DoCmd.GoToRecord , "", acNewRec
Forms!LN!LName = Forms!PrintAndClose!LetterName
Forms!LN!ID = Forms!PrintAndClose!NameNumber
DoCmd.Close acForm, "LN"
Thus the Many form has had the Word doc file name inserted into a textbox.
5) The rest is the Word.doc being printed, a copy of it made and the copy is pasted to a memo field on a Many record. This is done so that a copy of all correspondence is held within the data base. I have no idea whether something similar could be done for an Excel file but it is low priority.
The Many form that stored the document name is opened against the client/prospects form (or forms) and each record simply shows something like
"2008-12-09 22-35-59 Hutchinson Description of Letter" and next to that they click on a text box that will open the letter. The Word doc is actually saved in 3 formats, one with date/time first, one with last name first and one with letter description first and of course that is for sorting purposes.
That is what I am trying to duplicate in Excel.
So far I can click on a cell with the Word doc name and the Word doc opens and it will save as "2008-12-09 22-35-59 Hutchinson Description of Letter" and it will put that file name in another cell. I can also click on the cell where "2008-12-09 22-35-59 Hutchinson Description of Letter" is showing and that will resave it with a new date/time, in other words a secord letter being done of the same type. But I can't the new name to land in another cell.
At the moment I can only get the "2008-12-09 22-35-59 Hutchinson Description of Letter" to go into a nominated cell. In other words I can't. In other words I can't the counterpart of [texbox]=Whatever
I think my next stage will be to investigate drop down lists in Excel and these could perhaps have the name of the template documents. I did receive today from an insurance company a quote system for a new policy and as an interim measure it has been done on Excel and so that gives me a good idea of what can be done with drop down lists and other things. One thing I did notice with this quote system is when you click its Save button it saves it as another .xls file. I don't know if that is because of Excel limitations or because it was produced in a failry short time.
But I am lot more familiar with Excel than I was a couple of days ago
and will probably on the weekend start from scratch but with having a lot more familiarity with Excel. Up until a couple of days ago my experience with Excel was simply for transfer to and from Access tables.
