Replies

Update: The ‘Edit / Links’ dialog box shows locked links as ‘Locked’ instead of Man or Auto.

All links are already set to manual.

I have never heard of locking fields – I just tried it and it seems to work very nicely. How do you tell that the fields are locked? I can imagine someone opening this word doc months down the track and not understanding why the fields don’t update.

Can user form drop downs be multi-select so that I can capture that functionality of the pivot table?

To answer my own question … no. Combo Boxes (the excel name for Dropdowns) from ‘form control’ cannot be multi-select. List boxes can be but you need vba to get the selected items.

An idea is starting to form (ugly combination of combo boxes and expand into list boxes when clicked) that can replicate what I am after.

Although you can set up dependant-dropdowns based on what has been selected from a ‘previous’ dropdown, this is not as simple as it sounds. For example, using your data, it is straightforward to create dependant dropdowns that allow you to select USA [/B]from dropdown1, NY from dropdown2 and Rye from dropdown3. I would call this a ‘valid’ selection.

However, having made these dropdown choices, in my experience there is usually nothing to prevent a User from then returning to dropdown1 and selecting England, leaving the three selections showng as England, NY and Rye respectively, which I consider an ‘invalid’ selection.

True. When I set this up for a different spreadsheet, I had it such that if you changed a ‘higher’ drop down, it reset (ie cleared) any ‘lower’ drop down using VBA.

In which case, with VBA, you can achieve what you require. I would create my pivot table with the required page fields, but have a Userform overlaid on top of the pivot’s page fields (to ‘hide’ them). I would then use dropdowns on the Userform to ‘control’ the underlying pivot page fields.

Interesting. I’ll give this a whirl and see what I can knock up. I am assuming that the userform you are talking about is not a floating one but one that is ‘attached’ to tab in question and that the underlying pivot table filters are modified by the VBA as the user changes the userform drop downs.

Can user form drop downs be multi-select so that I can capture that functionality of the pivot table?

I was thinking about this more while I was walking my dog … I have the tables in excel with range names … I could set up a style (red, do not print) in word that held the corresponding excel range name that is one paragraph about the graphic in question … then run an excel macro that does something like this …

[*]go to the excel range name
[*]select range name
[*]copy
[*]swap to word
[*]go to the top of the document
[*]search for the range name
[*]go down 1 paragraph
[*]delete the contents of that paragraph
[*]paste as picture
[*]swap back to excel

If I put that in a subroutine and feed it the range name, excel doc name, word doc name then I could call it multiple times with the appropriate range names.

I’ve had plenty of experience coding in excel vba so that wouldn’t be a problem. The word side if things … not so much. Can someone with word vba experience tell me if #5 thru #9 is possible? And is it possible to jump back and forth between excel and word?

When I test this on 2010 (on Windows 7), it works fine – the linked picture is visible in all views, and is resizeable.

I am using win XP – maybe that is one of the issues. The other thing is that my Word / Excel 2010 is still set up to use 2003 as the default format. I did try all combinations without much success.

Does it come through as a field?

With regard to a macro to export as a jpg, couldn’t you do the same manually by copying the range in Excel and then pasting as a picture in Word?

I guess I could do that – can excel vba ‘control’ word? If so, do you have an example that I could cheat from?

I was able to use the Paste, Paste Special…,Paste link:, Picture (Windows Metafile) selection to paste an Excel object into Word 2010 and then center it, wrap text {by right-clicking and selecting Format Object}, and resize it {note: when you select the object there is a little handle in the bottom right corner do to this with}.

This is exactly what I have been doing and did repeat.

Options 2 & 3 insert a LINK field code; use Alt-F9 in Word to toggle between the result and field code views. If you have a regular requirement, consider setting up your Word styles to match the Word document formatting and use option #2 to reflect the current values in the linked Excel sheet automatically for you.

I have a special style that I created (‘Graphic’) that is centered, no fixed height, etc that displays the pictures quite nicely.

I’d also recommend setting the Word Option to always display field shading (File | Options, Advanced, Show document control).

I totally do this – also show bookmarks, etc. If you don’t do this and you update the bookmarks, you end up moving the new item outside the boundaries (I often bookmark the date of the report and then link my header to that bookmark).

I’ve been reading other posts in this forum and quite a few of them talk about macros. Re the above, I could see a macro solution being possible …

[*]macro in excel to highlight range name and export it as a jpeg (or similar)
[*]macro in excel to import the jpeg

One question that I have always had with macros in word is how do I control where actions are done. In excel I can just say ‘activecell’ or range(“A1”) … but how do I do the same sort of thing in word?

I do this all the time with dates, numbers, etc. The way I do it is as follows:

[*]Have an excel cell that holds the value I want to put in the word doc
[*]Give that cell a range name
[*]copy the cell
[*]flip over to word and use ‘paste special’ with the following options

[*]paste link
[*]paste as ‘unformatted text’

[/LIST]

This adds a field that is linked to the excel file / tab / range name. If you Alt-F9 (reveal codes) it should look something like this:

{ LINK Excel.Sheet.8 “C:\blah blah path\blah\blah\workbook.xls” “Sheet1!R11C3” a t }

Changing the value in the workbook and refreshing the link will update the word doc.

I think I will be running the sort process 3 to 5 times (different sort keys) each quarter. I am certainly looking for a ‘set and forget’ approach that doesn’t involved import, sort, export.

lLinkTest = InStr(1, lCell.Formula, “.xls]”)

Yeah – sloppy testing on my behalf. Just found out the above fix during a live demo … sigh.

Also that check will always return 0 for a xl2007 workbook extension.
You might want to just check for both a bracket and an exclamation point.

Your check will be quick but not complete as you can also have links in charts, shapes, hyperlinks, names, pivot tables and ?
‘–
Jim Cone
Portland, Oregon USA
( Special Sort add-in )

correct – but good enough for what I am after as I am only interested in links in cells.

Thanks for your comments.

This is what I ended up using

Code:

Public Function isHaveLinkedCells() As Boolean
Dim lCell As Variant
Dim lCells As Range
Dim lSheet As Object
Dim lLinkTest As Integer

' remember the current location
' StoreCurrent   0 Then
                isHaveLinkedCells = True
                GoTo TheEnd
            End If
        Next
    End If
Next

TheEnd:
'ReturnToCurrent  ' you can guess what this does
On Error GoTo 0
End Function

Well, it didn’t get some of my numbers correct – but that was mainly because I got my maths wrong

I typically leave it running for a few days and then turn it off for 1 or 2. When the screen starts flickering, I take it out side and blow canned air through it – sometimes I get lots of dust out – sometimes not much.

I was listening to Click and Clack the tappent brothers the other day and they had someone who was living in Alsaska near the recently erupted volcano – they suggested putting a stocking over the air intake of his car to cut the dust intake into his engine.

Sure a car isn’t a pc, but maybe that is an idea. The other option is that I go with a liquid cooled system (next time!). Edit: doh! liquid cooled will still be dust prone.

Thx guys. I use the canned air approach now – but would prefer the dust not to get in there. This is my graphics card …

256MB nVidia GeForce 7900 GS (pic is not my actual card – just an image I found that looks similar)

It appears to have a fan right on top of it. I think the main problem is the air intake at the front is basically open with no good filter. Will my machine get too hot if I put some form of linen (or similar) material over the air intake?

thx Bob. I have acronis so imaging / back up is taken care of. I will be steering away from SP3 as I have seen reports that it hoses Media Center – like it did to me.