- prompts you to browse to an Excel workbook
- which opens in the background
- the Word VBA then reaches into the workbook and copies data into a Word doc
- closes the workbook
- prompts for the next workbook
I'm using a variable called 'Z' as the loop counter.
The title of the File | Open dialog that is presented is tweaked so that the first time round the loop it says "open the 1st workbook", then next time, "open the 2nd workbook", then "Open the 3rd workbook", then "open the [n]th". The ordinal suffixes are generated using a UDF from Chip Pearson's website. The UDF is at the bottom of the module holding all the other code.
[code]
With dlgOpen
.InitialFileName = DefDocPath
.Filters.Clear
.Filters.Add "Excel workbooks", "*.xls*"
.Title = "Select the " & Z & OrdinalSuffix(Z) & " workbook:"
[/code]
This all works tickety boo.
However, if I duplicate this construct in a new module (so can update the surrounding Word VBA to extract data from different workbooks and process it in a subtly different way) the VBE throws a compile error:
Ambiguous name detected: OrdinalSuffix.
I've tried moving the UDF into its own module and heading it 'Public Function OrdinalSuffix' and there is definitely only one copy of the UDF code in the entire VBA Project but the second use of it is still giving 'Ambiguous name detected: OrdinalSuffix'.
Any clues as to how I can use this UDF in more than one module?
Thanks,
Ken
EDITED: Forget all this, turns out the code for OrdinalSuffix was duplicated in another module. Having removed that, the compile error has gone away.
