Automation stumble

[Ken McLennan]

[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.

 

[Bob Phillips]

Ken,

I am trying it from Excel not Word, but I cannot get past the copying of
sheets.

Can you post the full code as you have it in Word?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

[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.

 

[Ken McLennan]

G'day there Bob,

I am trying it from Excel not Word, but I cannot get past the copying of
sheets.

Can you post the full code as you have it in Word?

Certainly, thanks for having a look for me. Here 'tis...



Public Sub XLProcessor()


Dim obj_ACTIVEFOLDER As String

' Set files array to nothing so we don't end up with false data
ReDim str_INPUTFILEARRAY(0)

' Use folderpicker to obtain path to folder for processing
' If no folder (dialogue cancelled) then exit sub
obj_ACTIVEFOLDER = foldername
If obj_ACTIVEFOLDER = "" Then: GoTo bye

' Having got our folder location, begin to process files therein
str_INPUTFILEARRAY = getInputFiles(obj_ACTIVEFOLDER)
' First make sure that there's at least 1 spreadsheet in there
On Error Resume Next
If UBound(str_INPUTFILEARRAY) = 0 Then
MsgBox ("No XL files found in folder")
GoTo bye
End If

' Early binding code
Dim obj_xlAPP As Excel.Application
Dim obj_xlTGTWORKBOOK As Excel.Workbook
Dim obj_xlSRCWORKBOOK As Excel.Workbook


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

obj_xlAPP.Application.Visible = True


obj_xlTGTWORKBOOK.Worksheets(1).Delete
obj_xlTGTWORKBOOK.Worksheets(1).Delete
obj_xlTGTWORKBOOK.Worksheets(1).Delete


obj_xlAPP.EnableEvents = True
obj_xlAPP.DisplayAlerts = False


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

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



For int_X = 0 To UBound(str_INPUTFILEARRAY)
Set obj_xlSRCWORKBOOK = GetObject(str_INPUTFILEARRAY(int_X))
obj_xlSRCWORKBOOK.Sheets(1).UsedRange.Copy
obj_xlTGTWORKBOOK.Sheets(int_X + 1).Paste Destination:=Sheets
(int_X + 1).Range("A1")

Next


Debug.Print "Yes"


bye:
On Error GoTo 0

obj_xlAPP.EnableEvents = True
obj_xlAPP.DisplayAlerts = True

Set obj_xlAPP = Nothing
Set obj_xlTGTWORKBOOK = Nothing
Set obj_xlSRCWORKBOOK = Nothing


End Sub

Public Function foldername()

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.UserName & "\Desktop"
.Title = "Please select the folder containing current files"

.Show

If .SelectedItems.Count = 0 Then
foldername = ""
Else
foldername = .SelectedItems(1)
End If
End With

End Function

Public Function getInputFiles(path) As Variant
str_ERRORSTRING = ""
Dim int_Z As Integer
Dim int_X As Integer
With Application.fileSearch
.NewSearch
.Filename = "*.xls"
.LookIn = path
If .Execute <> 0 Then
ReDim str_INPUTFILEARRAY(.FoundFiles.Count - 1)
For int_X = 0 To .FoundFiles.Count - 1
str_INPUTFILEARRAY(int_X) = .FoundFiles(int_X + 1)
Next int_X
End If
End With
With Application.fileSearch
.NewSearch
.Filename = "*.csv"
.LookIn = path
If .Execute <> 0 Then
ReDim Preserve str_INPUTFILEARRAY(int_X + .FoundFiles.Count
- 1)
For int_Z = 0 To .FoundFiles.Count - 1
str_INPUTFILEARRAY(int_X + int_Z) = .FoundFiles(int_Z +
1)
Next
End If
End With
' If IsArray(str_INPUTFILEARRAY) Then
getInputFiles = str_INPUTFILEARRAY()
' Else
' str_ERRORSTRING = "No Excel Files in Folder"
' End If
End Function

 

[Ken McLennan]

G'day there Bob,

Certainly, thanks for having a look for me. Here 'tis...

Public Sub XLProcessor()

Dim obj_ACTIVEFOLDER As String

I forgot to mention that I've flagged MS Excel as a reference,
although you probably figgered it anyway because I'm using early
binding.

See ya
Thanks for helping
Ken

 

[Ken McLennan]

G'day there Bob, and whoever else was following this thread.

After being able to sit and play with it for awhile away from
being dragged about Xmas shopping and trying to mow the lawns between
thunderstorms I think I've found & fixed the problem.

It was largely a result of me trying to rush things so that I
could mow the lawns as opportunity offered, and go Xmas shopping (under
extreme duress I might add!!)

I found that when the code finished and I'd shut the spreadsheet
window there were still instances of Excel running. A bit of observation
while running the code with the task manager open & I found it wasn't
shutting Excel down. I then added extra lines, and rearranged others so
that items lower in the XL object heirarchy were released first. I.e.
range objects, then worksheet objects, then workbook objects, and
finally the application object - something that should have been obvious
if I'd not been thinking of how much I hate Xmas shopping.

Lo, and behold, at the end of the Word code there were no Excel
instances left active and when next run it all went smoothly and could
be run again & again without the symptoms described. At lease, it's done
that 4 times so far. I'm hoping that's the usual behaviour.

Thanks for looking at it for me, but I think we can now return to
normal programming ...chucklechucklechuckle. What a sense of
humour!!!! <g>

Thanks once again,
Take care,
Happy Yule
See ya
Ken McLennan
Qld, Australia