none
Interaction between Excel and Word OLE RRS feed

  • Question

  • I am using Office 2007 to write procedures but a problem is apparent only when run on Office 2013.

    A procedure in a workbook that records Sales information runs to transfer data to another workbook that stores Invoice information, and then opens Word to generate a mail merge document from a template (dotm) which has an event procedure (on Open) to merge data from the Invoice spreadsheet into a document to produce an invoice.  This procedure works without problem on the Office 2007 (Vista) machine and until recently likewise ran without problem on the Office 2013 (Windows 8.1) machine.  However, apparently following an automatic update to the Office 2013 machine, when the Word document is generated the procedure "hangs" at the point of reading the data from the Invoice workbook, and after a longish delay a message is generated along the lines of the program awaiting an OLE action.  The only way to stop the whole process is via task manager.

    After much research I have found that on the Office 2013 machine, the mail merge of data from the Invoice workbook will not now work if the Invoice workbook is open, bot works OK if it is closed.

    I have therefore programmatically closed the Invoice workbook, and the procedure now works on the Office 2013 machine (and on the Office 2007 machine).  However, I would like the Invoice workbook to be open when the Word document has been generated.  I therefore inserted a few seconds wait into the procedure to allow the mail merge to be completed, then tried to reopen the Invoice workbook.  I now get the message that the Invoice workbook is locked for editing, and asking if I want to open a read only copy (which I don't).  The Invoice workbook does not appear on the task bar, nor does it appear in task manager.  I have tried code to make it active and I then get "subscript out of range", and I have tried code to make it visible and I again get "subscript out of range".  However, if I close down the word documents generated with the data from the Invoice workbook, I can then open it;  presumably the merge process is treated as still being in progress while the documents are open, and the Invoice workbook is "released" when the documents are closed.

    Has anyone any advice to offer on how to deal with this problem of not being able to open the Invoice workbook while the mail merge documents are open.  The problem is apparent in both Office 2007 and 2013.

    Thursday, March 5, 2015 11:47 AM

All replies

  • However, apparently following an automatic update to the Office 2013 machine, when the Word document is generated the procedure "hangs" at the point of reading the data from the Invoice workbook, and after a longish delay a message is generated along the lines of the program awaiting an OLE action.  The only way to stop the whole process is via task manager.

    After much research I have found that on the Office 2013 machine, the mail merge of data from the Invoice workbook will not now work if the Invoice workbook is open, bot works OK if it is closed.

    ......

    The Invoice workbook does not appear on the task bar, nor does it appear in task manager.  I have tried code to make it active and I then get "subscript out of range", and I have tried code to make it visible and I again get "subscript out of range". 

    ......

    Hi Andy,

    Before answering the question, I want to firstly confirm these things with you:

    1. Which Office update did you install for Office 2013?

    2. What error messages did you get after installing the update and run the code?

    3. You mean VBA code by "procedure", right? Could you please post the code snippet that threw the errors?

    4. For the second problem, that you couldn't reopen the Invoice workbook, how did you programmatically close the workbook and how did you reopen it?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 6, 2015 2:54 AM
    Moderator
  • Hi Caillen

    Thanks for looking at this.  I should explain that I do all my work on an Office 2007 machine for a family member who lives elsewhere, so I don't have direct access to the Office 2013 machine.  However, they are reasonably computer literate and report faults accurately.  To deal with your queries:

    1.  Their Office (and Windows) updates are installed automatically.  Basically, their systems are kept up to date.  It is only a guess that an update may have caused the problem, for the procedure worked as expected for some months and no other changes have been made.  The problem does not occur on my Office 2007 machine.

    2.  For the initial problem there was no error message initially;  the little circle just kept going round for a minute or so, then a message appeared on the Office 2013 machine saying that Excel was waiting for another program to complete an OLE action.  That message no longer appears because I have cured the problem by closing the workbook that is the data source for the mail merge.

    3.  I do indeed mean VBA code by procedure (Sub xxx()).  The code in the Word Template on which the procedure originally hung up was the third line below - i,e, the line starting ActiveDocument.MailMerge.OpenDataSource Name:= _

    Sub ctPrintInvoice()
       
        ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
       
        ActiveDocument.MailMerge.OpenDataSource Name:= _
            ThisDocument.Path & "\ColTarInvoices.xlsm", _
            LinkToSource:=True, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:="DETAILS", _
            SQLStatement:="SELECT * FROM `'Print Invoice$'`", SQLStatement1:=""

    "DETAILS" is a named range within the workbook that is the data source for the mail merge.  "Print Invoice" is the name of the worksheet that holds the data.  This code runs as an event on opening the document, which is done automatically within the code in the workbook.  The code in the Sales workbook to open the document is:

        Set objWord = CreateObject("Word.Application")
        If strSourceSheet = "Sales" Then
            Set objctMergeDoc = objWord.Documents.Add(ThisWorkbook.Path & "\ctInvoice1.dotm")
        ElseIf strSourceSheet = "Euro Sales" Then
            Set objctMergeDoc = objWord.Documents.Add(ThisWorkbook.Path & "\ctEuroInvoice1.dotm")
        End If
        objWord.Visible = True
        Set objWord = Nothing
        Set objctMergeDoc = Nothing

    However, the procedure also hung in the same place on the Office 2013 machine if the document was opened manually.

    4.  The code in the Sales workbook to close the Invoice workbook is:

       Workbooks("ColTarInvoices.xlsm").Close

    The code to reopen it is:

       Workbooks.Open (ThisWorkbook.Path & "\ColtarInvoices.xlsm")

    This generates the message that the workbook is locked for editing.  The same message is generated if I try to open the workbook manually while the merged documents are still open.

    (The files are all in the same folder)

    With thanks

    Andy C

    Friday, March 6, 2015 8:43 AM
  • After this problem first appeared (program hanging while awaiting an OLE action) I found that by closing the data source (an Excel file) for the Mail Merge that problem was solved, but that it was then impossible to open the excel file again except in "read only" mode unless I first closed the Mail Merge documents.  Now working on the Windows 8 / Office 2013 machine some months later I have found that the original problem has gone away (possibly following another update??) so I no longer need to close the Excel file.  This is good news;  but out of interest I persued the second problem of why I could not reopen the excel file (except as "read only") if I actually did close it programmatically.  I can still find no answer to this and I cannot find a way to find why the excel file is treated as being "open for editing" when task manager tells me it is not.  Has anyone any ideas?

    Thanks in advance

    Andy C

    Tuesday, April 21, 2015 8:49 AM
  • but out of interest I persued the second problem of why I could not reopen the excel file (except as "read only") if I actually did close it programmatically.  I can still find no answer to this and I cannot find a way to find why the excel file is treated as being "open for editing" when task manager tells me it is not.  Has anyone any ideas?

    Thanks in advance

    Andy C

    Hi Andy,

    Try to use Process Explorer to see which process is locking the excel file when this problem occurs again. And try to close the whole Excel application to see if the excel file can be reopened.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 22, 2015 7:37 AM
    Moderator
  • Hi Caillen

    Thanks for the suggestion about Process Explorer.  It shows that there is no instance of Excel running when I try to open the excel file used as the data source for the mail merge.  This is true in both Office 2007 and Office 2013 in Windows Vista, Windows 7 and Windows 8.1.  So to summarise finally:

    I carry out a mail merge with data from an excel file that is closed.

    When the mail merge is complete and the document(s) are still open, if I try to open the data source excel file I get a message that the file is locked for editing by 'another user' and I can open only a read-only copy.  Process Explorer / Task Manager show the excel application as closed before trying to open the excel file.

    When I close the merged documents I can open the data source excel file normally.

    I suspect that this is just the way that the system works and I shall not pursue it any further.

    With thanks for your help

    Andy C

    Thursday, April 23, 2015 5:03 PM