Replies

Worked a treat, many thanks.

Beryl,
final word(?), I correct my original post with:

activedocument.fullname

to show path and filename of the actual document and not the application program
Alan
UK
Cheshire

OK, I error’d .
Sorry Beryl, and much thanks for pointing out error in subsequent post.
OK, so instead of placing the path and filename in the title bar, (and risk not having it work for activewindow), why not include path as part of the header and change font to hidden? Unless the user has hidden text to print such as in track changes then you’ll have the path at the top of each page.
Just a thought.
Alan

p.s. Is it me, or is Word one of the best strategy games you’ve played as well?

Beryl,
Open the document in question and select Alt+F11
On the left of the VBE (visual basic editor) window you should see the project explorer window (if not, select Ctrl+R)
In this window select This document and press F7
In the code window on the right (maximise if necessary) select Document (under General) and Open from the right selection box.(when you first select general it may put you in the New-document sub-procedure)
Add the following code as necessary:

Private Sub Document_Open()
Application.Caption = Application.Path & ” ” & ActiveDocument.Name
End Sub

The existing limits of Private Sub Document_Open() and End Sub should already have appeared.
Save the document and next time it opens you should have the path and filename in the title bar.
(Although it does flag up macro warning each time you open the doc)
Hope this helps.
Regards
Alan
UK
Cheshire

Hedgehog,
Thanks for the tip. For now it doesn’t seem to want to know but I think I may have a ‘bodge-it’ solution – create a couple of lines macro in word to insert the page break, or change the font to blue, then call the macro by name from within the Excel code. So far it gets around the problem, but I’d really appreciate any help on not having to put a macro in the word template.
Thanks again.
Alan

Using the address of the currently active cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Select Case ActiveCell.Address

Case “$D$7”
Cells(1, 2).Select

Case “$E$19”
Cells(1, 2).Select
End Select

End Sub

hope this helps

For a while I had resigned myself to the fact that it could not be achieved.
Not so in this case.
Many thanks Rory.

JerryC,
Fantastic, saved my bacon.
Quite right you are that the format is to be changed in the row source.
fyi the original row source was along the lines of
SELECT [Data_Vehicle_Ref],Sum([Data])AS [Data point],
Sum([Nominal_Value]) AS [Nominal Value], FROM [qryChartData] GROUP BY [Data_Vehicle_Ref];

and has now been modified to:
SELECT [Data_Vehicle_Ref],Format(Sum([Data]),”0.000″) AS [Data point],Format(Sum([Nominal_Value]),”0.000″) AS [Nominal Value], FROM [qryChartData] GROUP BY [Data_Vehicle_Ref];

Still can’t understand where the last four dp’s came from, but now I don’t care.

Many many thanks.
All that remains now is to how to export the whole report and chart to word.
Regards
Alan
UK

Hello Brian,
Yes I have been to that stage and set the formats to fixed 3d.p.’s
I have also ‘rippled’ through all of the (known/common) stages at which the data is referenced to make sure it is set to 3d.p.’s
What is so puzzling and very annoying is that the figures have most definitely been inputted as say 345.220 and yet out of the ether 7d.p. figures are appearing of say 345.2199905 These data figures are totally fictitious yet are appearing to within .0000005 of the original figures (the actual variance is quite wide and not attributable to a calc but appears to be random)
TIA
Alan UK

Thanks Jerry but unfortunately the Row source for the chart is already containing the SELECT statement with SUM[Data] AS [Data points] etcetera. I have searched all help and knowledge base files but to no avail.
When I reached the row source to change the format I was back at my query which I have now changed to general format and 3 decimal places – still no luck with all numbers still showing 7 decimal places.
TIA
Alan