[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]

G'day there One & All,

I'm back again with another problem that's beyond my
capabilities. I'm currently writing an automation application where I
have a Word document (which is basically a single table) loading various
XL worksheets.

There can be any number of worksheets which are produced by a
report builder that's run daily on the latest dataset. I have absolutely
no control whatsoever over the output of the report builder. There are
various staff members who run it each day with the results being saved
as individual workbooks with a single worksheet in each. The users save
the sheets to a folder of their choice, usually on the desktop, with
file names they choose themselves. The usual course of events is to name
the files after the report heading and then copy/paste the relevent info
into appropriate table cell in the word doc. As you can imagine, the
amount of data in each sheet is arbitrary and may even be blank.

There is *some* consistency in that the report headings and
formats are always constant for each report. I.e. the "Left Handed
Hertzelflanger" report always has the heading "LHH Output" in cell "B4".
Naturally whoever wrote the report builder gets it to export headings
and titles into merged cells. I don't think they're familiar with
"Centre across Selection", but the mergers haven't caused any issues
yet.

What I've done so far is to get the Word app to open a
"Folderpicker" dialogue which gives me a folder (oddly enough). This
folder is that which has the XL files within it. Having gotten a folder
the code then loads the path to each file into an array with each
element holding the path & filename of a single XL file. These files can
be in any order and the names are not constant (as above).

I then open an early bound Excel instance with:

Set obj_xlAPP = CreateObject("Excel.Application")
Set obj_xlTGTWORKBOOK = obj_xlAPP.Workbooks.Add

From there I add one page for each element of my files array
using late binding:

For int_X = 0 To UBound(str_INPUTFILEARRAY)
Set obj_xlSRCWORKBOOK = GetObject(str_INPUTFILEARRAY
(int_X))
obj_xlSRCWORKBOOK.Sheets(1).Copy Destination:
=obj_xlTGTWORKBOOK _
.Sheets.Add(after:=
(obj_xlTGTWORKBOOK.Worksheets.Count))
Next

and then iterate through the array copying each file to its own
page:

Do Until obj_xlTGTWORKBOOK.Worksheets.Count = UBound
(str_INPUTFILEARRAY)
obj_xlTGTWORKBOOK.Worksheets.Add after:=obj_xlTGTWORKBOOK _
.Worksheets(obj_xlTGTWORKBOOK.Worksheets.Count)
Loop

After that I have a single workbook with a number of sheets each
containing the required data. I'll then search for the report headings
and copy each report to its respective Word table cell and process
further from there.

The problem I'm finding is that even though I set all of the
objects back to "Nothing" at the end of my code, it only runs once. If I
shut everthing and load Word the code works fine and gives me a workbook
as I expect. If I then close XL and run it again, I get a whole bunch of
"Do you want to keep the crap in the Clipboard messages" and one blank
page for each file in the array. These messages don't appear when first
run. This is my first attempt at automating with Office, and I was quite
pleased with my progress up until now. Admittedly, my users will only
need to run the code once anyway, but that's not the point. I think it
should run when I tell it to, not when it feels like it.

Does anyone have any ideas? I don't see it as appropriate to a
Word forum so I thought I'd ask here first. BTW, I'm building it on
Office XP running on Vista. I've forgotten what version of Office they
have at work, but previous stuff has worked fine on their NT systems.

Take care,
Thanks for listening,
Ken McLennan
Qld, Australia.