0

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()`
Tug01519
  • 13
  • 4
  • 1
    Do you mean replacing the sheet will also replace formula?It seems that the answer is yes. And you can try to use `openpyxl` and `xlrd` to copy the part data instead of the whole sheet. – Jeffreys Nov 13 '19 at 09:10
  • I would also recommend using xlrd for this. Its much less cumbersome and better documented than win32com. Also its more accessible for Python users, because you dont have to dip into VBA. http://www.lexicon.net/sjmachin/xlrd.html I also like to combine xlrd and win32com for when I want to read data in the background, but print them into an open excel sheet in front of the user's eyes. – tst Nov 13 '19 at 14:04

0 Answers0