Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation stumble
[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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation stumble
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) "Ken McLennan" wrote in message ... [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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation stumble
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.Wo rksheets.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation stumble
G'day there Bob,
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation stumble - Got it!!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automation | Excel Discussion (Misc queries) | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming | |||
Automation | Excel Programming | |||
Automation | Excel Programming | |||
about OLE automation | Excel Programming |