Replies

I had the same Pivot Table in mind. It doesn’t HURT to aggregrate the data, does it? It’s already aggregated. That handy “Page field” lets you select the year to display very nicely.

I’m not really sure what’s going on there with your data and why transposing it would make it easier to read, why the field names are “part of the data”, etc….
Pivots, Autofilter, PasteSpecial…Transpose, =VLOOKUP() or =HLOOKUP() or some combination of the above is all I got for ya.

If comparing the data between companies is the goal, maybe a chart based on the pivot table would really do the trick. You can use data labels or a data table to show actual numbers.

(Edited by HansV to activate link to post – see Help 19)

Good points, Steve. I just posted a summary of how I like to do “form fill-in” templates in another thread: post 255721

I insert a UserForm in the VB editor and use it to allow proper data entry with all the bells & whistles then stuff the results in fields & bookmarks, etc.
Form Fields just don’t cut it after you’ve used real forms. UserForms are practically the same thing you’re used to in MS Access forms.

If you are just looking for a unique number for each user and want to stick with the PrivateProfileString multi-user method you could give each user their own .INI file on their C: drive.

Append their next sequential number to their initials or something to make it unique across the group. You’d have to make sure their personal ID was unique.

For instance:

Dory Owen’s 5th check request would be: DO-005
Jon P Smith’s would be: JPS-005

Using Access is best, but this would use what you know and still be unique. Maybe problems would go away if users aren’t trying to share the .INI file.

I don’t know if this will help, but you can copy the styles from your latest, greatest template into any old legacy docs with this command:

ActiveDocument.CopyStylesFromTemplate Template:=”C:TemplatesNewTemplate.dot”

I don’t know how the user is supplying the rest of the file name (the part before the date) but I did a macro like this once for some sales reps and had a weird problem.

The users got used to just hitting a button to save and my code would automatically build them a suggested file name based on customer name and the date. So when they started including characters like “/” or “?” in the customer name they got an error message. I had to add a little code to strip out any invalid characters from the suggested filename.

Just something to consider if you are planning to “build” a file name out of available text on the spreadsheet. And if it’s possible they will type something dumb like “Next Thursday” in the date cell you may have to consider that, too. I had that happen as well.

I do something similar. I have a macro import all the data to one worksheet and define a “Range Name” to include the whole pile of data. Then on other pages I have pivot tables that summarize the data, referencing the data by it’s range name. That way the pivots can be updated accurately even if the number of rows in the original data import changes.

Start playing around with the pivot tables and I’m sure you’ll figure them out.

Autofilter is also a good suggestion if you just want to show the original data rows but limited to different criteria. Pivots do COUNT and SUM queries — good for summarizing a big pile of data.

I also use VLOOKUP formulas sometimes to “look up” matching data on other sheets. This is a one-to-one kind of matching.

If you already know Access, I doubt any book will help you out as much as just using online help and doing a few little experiments with these features. I’ll bet you’ll have a solution in no time!

Page numbers?

If copy/pasting 100 times to make a big 100 page doc sounds like a hassle, maybe you could make the form a mail merge doc. Then use a spreadsheet with a column of 1-100 as your merge data. Place the “number” merge field on your form and let ‘er rip: Merge to document and you’ll have a big 100 page, numbered doc to print….once.

Otherwise, you might need to do some coding to do this. Like:

Sub Print100()

For x = 1 To 100
    ActiveDocument.FormFields("Text1").Result = x
    ActiveDocument.PrintOut
Next x

End Sub

Just plop a formfield in the doc where you want the page number to be. In the example above the form field is called “Text1”

There is this command:

FileCopy SourceFile, DestinationFile    ' Copy source to target. 

But it won’t work on a file that you have open in Word, so it’s not too helpful here.

To make sure you are left working on the C: copy you could save to A: first, then save to the C: folder you “captured”.
Try this code:

OrigName = ActiveDocument.FullName
ActiveDocument.SaveAs FileName:="A:" & ActiveDocument.Name
ActiveDocument.SaveAs OrigName

Yikes! You are brave. I’d avoid all that AutoOpen stuff. That’s how baby “macro viruses” are conceived.

You can customize your toolbar. Add two new buttons: one for color printing, one for B&W.
First, make your two macros, then add the buttons to your toolbar.
Right-click the toolbar, Customize, Commands, Categories=Macros, drag macro name to the toolbar.
Right-click the existing Print button and “Copy Icon” and paste it onto your 2 new buttons.
Edit the icon image for your color print button so you can add some snazzy color to it.

Here’s a sample macro to assign to the color printer button:

Sub ColorPrint()
    ActivePrinter = "Color Printer X100"
    Dialogs(wdDialogFilePrint).Show
    ActivePrinter = "HP Laserjet"
End Sub

Then pull off the old default Print button from the toolbar. Just use your customized buttons for printing. Note that it doesn’t actually send anything to print. Just displays the Print dialog. I like to have a chance to select what pages to print, or how many copies I want.

Here’s an idea. Use the “Run macro on Exit” property of the first drop-down field to update the second field:

Sub DropDown1_Exit()

Select Case ActiveDocument.FormFields("Dropdown1").Result
    Case "Peter"
        n = 10
    Case "Paul"
        n = 20
    Case "Mary"
        n = "30"
End Select

ActiveDocument.FormFields("Dropdown2").Result = n

End Sub

Unfortunately, the user has to actually EXIT the field before the updating occurs.
In a real UserForm (not a form field in a doc) you could make this update “real-time” which users will find more intuitive.

I’m wondering why the printer is changing to another printer, requiring you to set it back again to the default.
If you have another macro that is switching the printer for some special job, it should probably reset Word to the default printer when its done doing it’s thing.

Can you tell us what situation is creating this need?

If you think it’s worth the effort (lots of users, lots of usage of the template) here’s my favorite recipe for form fill-in:

1. Insert your FORM fields and REF fields in a template

2. In VB Editor, design a UserForm for collecting all the necessary text fields to fill-in. You can also user checkboxes, drop-downs, default values, validation, etc…

3. Add an “AutoNew” macro in the template — this macro runs as soon as a user creates a new doc from the template.

4. Have the macro display the UserForm to collect all the info to fill in and then use the results of the form to fill in the form fields in the doc.
ActiveDocument.FormFields(“Text1”).Result =dlg.TextBox1

5. Last step of the macro is to:
Selection.WholeStory
Selection.Fields.Update
Selection.Fields.Unlink
Selection.HomeKey Unit:=wdStory

At the end of this process the user has a regular ol’ document with no fields to accidentally “whack” or stumble over. It’s all been converted to regular text.

The userform and macro let me do a lot of extra work for the user in the background. Like major modifications to the doc depending on options the user selects. (I customize logos, the body text, signatures, etc.) And they like just filling in all the info in one little dialog instead of having to hop around in the doc filling one field at a time. I used this method to roll out a large number of templates to a national user base of 10,000 without having to train them. Hooray for that! It takes more coding on my end but is worth it for the daily users.

To create the body text of the template I use a macro (wish I had the source here at home to post for you) that basically does this:

Deletes any pre-existing text in doc.
Loops through all autotexts, typing first the name of the autotext using SPECIAL style, then back to Normal style, then dumps contents of Autotext.

That creates a document filled with all your autotexts that easy to edit. Then the SECOND macro updates the actual Autotexts based on what you’ve got in the body. This macro does:

Goto top
START LOOPING:
Select row (name of autotext and store to a variable)
Go down a line and start selecting down one row at a time until you hit a row that has SPECIAL style applied. Then back up a row.
Now you’ve got the updated (or not) text selected.
Store it as an autotext using the name you stored to the variable.
GOTO START LOOPING

Hope that makes sense.

>>If the exported file ends up in a database…

I should hope the exported file would be IMPORTED into a database. In which case, the import process merely USES the quotation marks and field separator (comma or pipe or tab) to tell it how to chop up the records into fields. These characters are never meant to be stored as data in the resulting database.

Edited by DoryO on 27-Jun-02 01:22.

The cool thing about the Autoformat (oops! meant to say AUTOTEXT!)** option is that you can also save text in the headers and anything else you need in the table. It just pops out onto your document in perfect shape ready for input. We use a “starter table” this way that has the header row and one data row all prepped for the user. They just tab down to create more rows.

But, if your table content is being imported or pasted to Word from another source then this isn’t such a great solution. In that case, I’ve had to write VB code to fill in the cells on the “starter table”.

** There is very little coolness to the AutoFormat feature.