Replies

Hi Gazza,

Here’s another way which may accomplish what you want.

What I would do first is create an area in Excel with all of your values in 1 place. Preferably a new sheet but it doesn’t have to be. The key is to have all the values you want in 1 place. This place can just refer to the key cells elsewhere in the workbook by just =location of item (the ordinary way).

Make sure you include a label for each item. So with, say 5 items (for brevity), you’d have across a row (or down a col):
item1 item2 item3 item4 item5
50 30 25 3 600
where the vals are derived by the assignment mentioned above.

Name the above range something like Sending_values.

Now go into Word doc where the info needs to be retrieved.

Go to Tools | Mail Merge…

Under 1. Main Document, you want to Create something. I don’t think it would matter if you chose Form Letter or Catalog since there’s not much diff between the 2 but for this purpose, there would only be 1 record to be merged anyway (the 2nd row of the above excel range).

Under 2. Data Source Get data, choose Open Data Source and navigate to your excel workbook (remember to choose files of type Excel). When you get to it, you will be given a choice of Entire Spreadsheet or any named ranges such as Sending_values. Guess which ones to pick.

You will then be prompted to enter Mail merge fields but Word will have picked up your col headings in row 1 as merge fields. Stick those whereever you want, can even repeat a field more than once. Alternatively, as has been suggested by others (but I don’t think it matters), you can insert the merge field once, bookmark the value, and then x-ref the bookmark wherever else you need it.

Now you are ready to merge (step 3). Just merge to your document and you’re done.

The downside to this vs linking is that you have to remerge each time the spreadsheet changes. But you can skip merge steps 1 and 2 since they won’t change.

What this approach does is avoid having to bring in the excel values as a table. Even if you link to them via the approach others have suggested, I would still put them in 1 place in excel, and then paste special with a link. You can put the paste special in a page at the end of the doc so people may not see the pasted table (don’t print it or hide it). then just use bookmarks as before for cross-ref’g.

Hope this helps.

Fred

I had what seems to be a similar problem. Running in Excel 97 but I think it will apply to 2000.

I had 2 copies of the same workbook (don’t ask how they got out of sync). Ver A had some updated values which Ver B didn’t have but B had a new sheet which I wanted to get into A.

I didn’t want to copy the values from A to B bcs I might mess up and not get them all. So I decided to copy the sheet from B to A. I renamed B so I could have both workbooks open at the same time. Then I did a copy of the sheet in B to A. I then removed B (had it on floppy just in case).

None of the formulas in the extra sheet had any links but A had a link to B. Nothing in a cell of Ver A referred to B but there it was – in the set of links – a reference to B.

Looked at help – no help, almost.

One of the things you can do in the Edit | Links is to change the source of a link. So I selected the link to B, clicked change source, navigated to the filename for A (ie, change the source to point to itself), clicked OK, and voila – the link to B was gone.

Hope this helps. If anyone tries this out in 2K, let us know how it works.

Fred

Phil,

Thks for the tip. And what’s even better is that you can copy some text to the CB and it won’t destroy the formatting info that you copied with CTRL+SHIFT+C.

Fred

Sherri,

Maybe I’m missing something but I do this all the time w/o a macro. ALtho I work in ppt 97, I believe what I’m about to say is in ppt 2K.

2 approaches that are really almost the same.
If you have a template, then what’s on the template shows as background for all slides by default – granted.

1. I draw a box around the whole slide and fill it as white (or some other color) with no line. Hides the template material and I can now do whatever I want for that slide.

2. For the slide that I don’t want the template material, do the following:
– right click a blank part of the slide and choose background (also get to from the Format menu). you’re now looking at a dialogue for background fill
– click the drop-down and choose fill effects
– click on the picture tab
– click select picture and browse to the file you want and click OK when you find what you want.
– click OK until you get back to the Background dialogue box
– make sure you check the omit background graphics from master at the bottom. Most important
– click on Apply (not Apply All) and you’re done

Hope this helps.

Fred

Mike,

In addition to dbl-clicking the format painter, here are a few other ideas:
Idea #1:
– when formatting the text in the TB, use the format | font dialogue box to make the change for the first TB. This way the font and size change are treated as one change.
– as you go from TB to TB (eg, using ALT+down arrow as suggested), just hit CTRL+A to select all the text in the TB and then CTRL+Y which repeats the single last change (which is why you want to do this in the font dialog and not off the toolbar).

Idea #2: kind of wacky but
– if the VBA thread finds a way to select all TBs in a doc, then you can do whatever it comes up with and then press CTRL+A which will select all the regular text not in TBs and deselect the TBs.
– with the non-TB stuff selected, change it to another style temporarily (I’m assuming you use the same style inside of TBs as outside; if not, see idea #3).
– you now have TBs just formatted with the style you started with. You can either change this style definition to accommodate the change in font and size or do a Find-Replace to change the style of text in the TBs to another style with the proper font/size (use the more and format buttons in find-replace to get at the style info)
– remember to change your non-TB stuff back to it’s original style if necessary (the style you used in the second dash doesn’t have to be a dif appearance, just a dif name so this step may not be necessary)

I tried this with 2 pages of text and TBs in Word 97.
– I reduced the view to view my pages 2 at a time (just to cut down the amount of time; does Word 2000 allow even more to be viewed at once?).
– selected the multiple text boxes (click on first TB and with shift held down click on the rest until they’re all selected)
– now do the CTRL+A; the non-TB stuff is now selected and the TBs are not
– proceed as above

Idea #3:
if the text in TBs is formatted with a dif style than the rest of the non-TB stuff, just change the style def. That’s easy but you probably have Normal style for both TB and non-TB stuff so forget this item and use #1 or 2 above.

hope this helps.

Fred

Keely,

I had the same problem with something I was doing a week or two ago. The problem in general is that you can’t add 1 (or anything else) to the cell directly, to the best of my knowledge, w/o going to a macro. You get a circular reference error.

So with my little knowledge of excel macros, here’s a starting point where cell(7,1) [ie, row=7, col=A] is the cell to be increased.

Sub add1()
‘
‘ add1 Macro
‘
Dim a As Integer
a = Cells(7, 1)
Debug.Print a
a = a + 1
Cells(7, 1) = a
‘
End Sub

I know you’ll get lots of other answers too. The embellishments you’d want would probably include:
– select any cell to increase (obviously) so the code has to look for the addr of the cell currently selected (assuming that’s the one to be increased)
– probably don’t want the user to have to go to tools | macros etc to invoke the macro. A button would help and is not hard to do.

fred

Leif,

You discovered it
Yes it was Freedom of Association. It even looks like it hasn’t been updated since I got it about a year ago. I didn’t think there was really much you could do to improve on it anyway.

Hope it helps Melanie.

Fred

Melanie,

I know people who use a lot of graphic programs. Seems like every time you load a new one, it takes over the file extension.

I found a nice little utility called File Association that is a bit easier and more flexible to use than the Windows method. Only trouble is I don’t remember where I found it and I don’t have it on this machine. It probably was on a Ziff Davis bulletin board.

Hope this helps.

Fred

Thanks for the thoughts, Phil. I’ll try your items. But a few quick things:
– the TClock utility does have a checkbox to Load when Windows starts and it is checked.
– recall that one of the vanishing items is my desktop toolbar and the absence of my quick launch bar (I want QL off). I turned on DT and turned off QL the same way on all 3 machines. But my “vanishing-act” machine, on reboot, also turns off DT and turns on QL.

At least as far as the registry goes, I did not see an entry for TClock (assume I would see something there). So maybe the off-reboot-on-reboot sequence might work.

I’m wondering if there is a fundamental problem/setting that affects both. If so, the potential solutions with TClock (turning it off/on in msconfig) won’t do it.

Thanks – fred

Thanks Phil. I do have tweakUI installed on the “offending” PC and Save Explorer Window Settings is set.

Fred