Win32COM code wont replace excel strings in a formula reference

38
November 13, 2019, at 5:00 PM

This code copies the first sheet of an excel workbook that already has formulas into a new book. For some reason, when you move the sheet, the formulas still reference the old book (in the example below-- Book1).

To get around this, you can find and replace "Book1" with "Book2". I am trying to do the same with the win32Com python code. But it returns that nothing was found.

Can someone Help me Understand why it wont find the string in the formula like the manual Find and Replace Does?

from ayx import Alteryx
from win32com.client import Dispatch
path1 = (r'C:\Users\Name\Desktop\testing\Test Env\Book1.xlsx')
path2 = (r'C:\Users\Name\Desktop\testing\Test Env\Book2.xlsx')
xl = Dispatch("Excel.Application")
xl.Visible = True  
wb1 = xl.Workbooks.Open(Filename=path1)
wb2 = xl.Workbooks.Open(Filename=path2)
ws1 = wb1.Worksheets(1)
ws2 = wb2.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))
ws2.Cells.Replace('Book2','Book1') 
wb1.Close(SaveChanges=True)
wb2.Close(SaveChanges=True)
xl.Quit()`
READ ALSO
Python text_content() returns bound method

Python text_content() returns bound method

Can't figure out why this is returning as bound method HtmlMixintext_content, trying to create a web-scraper and display the data in a dataframe

49
Python Sum Second Element of Tuple by First Element

Python Sum Second Element of Tuple by First Element

I have the following two lists in Python3:

45
Executing MapReduce MRJob code in Python and getting IndexError: list index out of range message

Executing MapReduce MRJob code in Python and getting IndexError: list index out of range message

Well, I am trying the mrjob library and the code I wrote is not working properlyI looked at it and it seems fine from the little that I know about MapReduce library

16
Is it possible to check if a string is in a class nested in a class in Python?

Is it possible to check if a string is in a class nested in a class in Python?

I have created a class in Python with classes nested in it:

41