1

How do you copy worksheets with DispatchEx? I can't copy from one worksheet from one workbook to another worksheet on another workbook. I am using DispatchEx to open them in two separate excels. The line: ws1.Copy(Before=wb2.Worksheets(1)) is giving an error. This same line works for dispatch, but I need to open the files separately in excel.

import time, os.path, os
from win32com.client import DispatchEx

path1 = 'C:\\example1.xlsx'
path2 = 'C:\\example2.xlsx'

xla = DispatchEx("Excel.Application")
xla.DisplayAlerts = False
xla.Visible = True

xl = DispatchEx("Excel.Application")
xl.DisplayAlerts = False
xl.Visible = True

curTime = os.path.getmtime('C:\\example1.xlsx')

while True:

    modTime = os.path.getmtime('C:\\example1.xlsx')

    if(modTime > curTime):

        wb1= xla.Workbooks.Open(Filename=path1)
        wb2= xl.Workbooks.Open(Filename=path2)
        ws1 = wb1.Worksheets(1)
        ws2 = xl.ActiveSheet
        ws1.Copy(Before=wb2.Worksheets(1))  # problem code here. Works in
                                            # dispatch

Here is what is below 'ws1.Copy(Before=wb2.Worksheets(1))' statement. The only difference is I am using Dispatch to open two workbooks in once instance. Doing it this way allows me to copy ws1 to wb2. But but the master file is closed, it will throw msg 'file available for editing', even though I specifiy wb1.close().

        wb1.Close()                         #close wb1 but still get msg
        #xla.Quit()                         #Quit() is the only way I can 
                                            #close out wb1 completely and 
                                            #not receive the  'file now 
                                            #available for edit' msg.  
                                            #But it close both books. 

        xla = Dispatch("Excel.Application") #starts up wb1 again if file 
                                            #is modified (path 1)
        xla.DisplayAlerts = False
        xla.Visible = True

This is the traceback I am getting: Ignore line 35 as some commented lines were not inserted here.

Traceback (most recent call last):
  File "C:/Python34/updateExcel2Dispatch.py", line 35, in <module>
  ws1.Copy(Before=wb2.Worksheets(1))
  File "<COMObject <unknown>>", line 3, in Copy
  pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Office Excel', 'Copy method of Worksheet class failed', 'C:\\Program
  Files (x86)\\Microsoft Office\\Office12\\1033\\XLMAIN11.CHM', 0, -2146827284), None)"
superx
  • 157
  • 13
  • https://stackoverflow.com/questions/24075427/copy-entire-worksheet-from-one-instance-of-excel-to-another Why do you need two instances of Excel? Could you not open the other file temporarily in the same instance so you can perform the copy? – Tim Williams May 01 '18 at 17:37
  • @TimWilliams I need two instances because i needed to close out one of the workbooks after the copy. If i open both in one instance, It closes the workbook fine but a popup msg saying 'file now available for editing' still comes up to that workbook which was supposedly closed, – superx May 01 '18 at 18:29
  • 1
    @martineau hey thanks for the correction. Now I know better :) – superx May 01 '18 at 18:31
  • Can you post the code which ends up with "file now available for editing" ? Seems like you might be able to get around that by opening the file specifically as read-only. – Tim Williams May 01 '18 at 18:38
  • @TimWilliams I just added the code in the original post, please see above. Sorry what is the proper etiquette adding code (edit original or add in comment)? I believe I have tried opening path1 as read only but maybe i'm doing it wrong. – superx May 01 '18 at 19:11
  • Sorry I meant the code you'd tried using only the one Excel instance - you can add it below the existing code so as not to affect your original Q – Tim Williams May 01 '18 at 19:20
  • @TimWilliams Ok i just added it below the original code. Just so that you know, the whole purpose of the code is if the original file (path1) is modified, it is then refreshed and updated on file2 (path 2) that will always have it open on a large screen monitor. – superx May 01 '18 at 19:34
  • @TimWilliams I found a work around this problem by first saving the path1 file as a different name. This way it won't get the "file is now available for editing" msg if the original file is closed. Then the path2 file will just copy off of the newly saved file. And it will continue in this cycle. But I am open to more efficient coding ideas if you have other ideas. Thanks. – superx May 01 '18 at 21:26

0 Answers0