Replies

Thanks Hans. Just tried it and the results are as expected. Excellent.

Holy smoke!!!
Nice one Andrew, I read all of the associated links and MS Q’s and I must say this is a gotcha of the highest order.
The links are recommended reading for all loungers with Word.
Problem not yet solved but a few long hours should see it off.

Alan
no longer at Wits end – moved out today.
Cheshire
UK

Thanks Hans.
I’ve just tried the save_as to a couple of doc types and found probably the best results from save_as html.
The tables come back strange and I can then save as word document again but now it has no corruption.
I genuinely believe the corruption stems from an errant section break held in a table cell or some such like. (notice how the original attached file, whilst only two lines, was 300+kb!?!?)
Still, following your advice at least I now have an uncorrupted virgin text doc from which tocontinue.
Thanks again and if anyone does have a solution, please post.
alan
Cheshire
UK

Thanks Steve.
Yes, the cell selection that I have is one only. I too have been caught out with that.
In the intervening moments I now have a working system that has a search button which prompts with an inputbox for user input.
I have taken this route for the simple reason that I can now stipulate exactly how the search is started, i.e. one cell selected,values only etcetera.

One of the most frustrating problems which I’m sure a lot of people have is that whilst the end user (125 miles away oop north as we say) insists that he is not looking in formula and “of course I only have one cell selected” I sometimes wonder whether the cell he is referring to is enclosed in his cranial cavity.

I shall be producing a list of do’s and don’t’s for the end user and the “single cell selection” will be on the list.

Thanks to all for the responses but I guess we can now chalk this upon the success chart.

Alan
Cheshire
UK

Thanks guys. Good pointers to try.
Strange things are afoot.
Hans’ suggestion that the find was in formula/values solved part of the problem and I did get results from ensuring Lookin was “Values”.
I believe I now have sufficient to drop a sub in there to cope with the generic find requirements of the team.
Ctrl+F works most of the time but for some strange reason ( and I cannot reliably replicate it every time) the search comes back with not found dialog.
Still, from your suggestions with the xlDialogFormulaFind I have the solution.
Thanks again.
Alan
Cheshire
UK

OR, you could just use whilst iterating through a pages shapes collection:
If shape.style = “Connector” then
<>
end if

Alan
Cheshire
UK

But then when you open the .doc file, and you go to save_as – you are again faced with a greyed out box and only the template option.
This is a common problem where I work.
Always, always, always create a new document based on the template. To rename the .dot as a .doc will come back to haunt you in the future in the form of associated templates and styles and macros and……
Alan
Cheshire
UK

1 – can you still print to a file instead of to the printer (use FilePrint and select Print to file)
2 – in toolsoptions check that you are picking up the “Use printer settings”
3 – when installing the printer did the administrator log back onto the pc as themself or the user
4 – check out the epson website helpdesk

Thanks Steve, fresh pair of eyes for me please.
The “for x = 1 to shapes.count” didn’t make any difference but it did prompt me to get the shape name; extract the number part of the name (rightmost end characters); and then place the shape name in an array at the corresponding value position ak la:
For i = 1 to activepage.shapes.count()
Set objcurrent = activepage.shapes(i)
ShapeNumber = objCurrent.name
ShapeNumber = Right(ShapeNumber, Len(ShapeNumber) – 3) ‘the shape name is same format as Pro76, Pro77 etcetera
ShapeNumber = Cdbl(ShapeNumber)
ShapeArray(ShapeNumber) = Shape.name
Next

Then I can address the shape names in order by because shape named Pro76 is at the array position 76 and Pro77 is at array position 77 etcetera.:

For i = 1 to 100
Set Objcurrent = ActivePage.Shapes(ShapeArray(i))
‘do other code to shape like start aligning and spacing from left
Next

Thanks again Steve.

Alan
Cheshire
UK

ps. anyone got any good recommendations on Visio VBA books.

Unfortunately I work in an environment where they say sympathy is a wasted emotion. Did your beard grow that fast before you worked with MS products?
Thanks anyway, it’s nice to know there’s others with similar problems. Part of the problem lies not just within the flowchart wizard (that is the means to an end of importing information) but within the next step of using VBA to reposition the shapes on the page.
If anyone can please try a logical explanation as to how and why VBA decides which shape to report on in which order I’d much appreciate it. I think that once I understand that principle it will make the customised positioning of any of the shapes a lot easier.

FYI.
– the shapes are named in a list as PRO1;PRO2;PRO3;PRO4;PRO5 etcetera (no more than 5 on a line).
– when imported they lie sequentially on the page, one after the other left to right 1;2;3;4;5
– on reformatting the page using the for each shape in activepage.shapes…..next sequence, the shapes are moved up the page ordered randomly such as 5;2;3;4;1
– any suggestions as to why it selects shapes in that order?
Alan (my order for laser guided sharks in in the post)
Cheshire
UK

Can’t believe it’s got the lounge speechless. It must be a first.
I even trekked through VB lounge to no avail.
But in the meantime, I resorted to good old legwork and a constant supply of rocket fuel caffeine.
I didn’t solve how the flowchart wizard brings the data into the flowchart (i.e. how it decides which is first) but I did solve the positioning by sending each objects PinY value to an array and then counting the occurences of that value in the array. If anyone is interested I’ll share the info and code but it seems very lonely out here.
I thought I had it cracked, but then……. how does VB decide on which shape comes next when I use the code?:e.g.
For each shape in activepage.shapes
msgbox shape.name
next
will present the shapes names not in the same order as in the excel worksheet, and not back to front, nor reverse, nor alphabetical, nor numerical, nor ascending, nor descending, blah blah blah…..
Please someone help, I refuse to give up on this and I’m getting a reputation as a clone of Dr Evil after refusing to help the MD to add a new folder in his outlook account.

Alan…maniacal laughter sound effect…..
Cheshire
UK

Thanks for contributions, I believe we may now be on the road to a solution. Thinking at a tangent and substitute Flowchart for Database. By using various queries and ‘make table’ queries I can now get an output in excel format which I can use the visio flowchart wizard on. This is probably the best method for completing my ‘connectivity’ diagram.
By modifying the Excel hidden sheets I can even spec which visio template to use.
Many thanks to all posts.

Alan
Cheshire
UK

Thanks Leif.
The site was a great help and resolved the query.
Alan
Cheshire
UK

Many thanks Hans. Your solution works fine.
Alan

Sorry Jan, forgot to mention that the error occurs AFTER the code has run. I too had no problem in 97sr2 deleting the columns before the code has run. Unfortunately, in the file I attached, because it has to be less than 100K, I removed the other worksheets so onactivate will have to be run from the vba code and F5 or add a new sheet to jump from.
The resultant column has #NAME? as values but this is just because the original named ranges are not in the sample workbook.
TIA
Alan
Cheshire
UK