0

New to Python--

My code wont execute Find and Replace after moving the Sheet.

The goal is to bring a new sheet with formulas, then Find and Replace the reference, in the formulas from the 1st book. This will allow the formulas to be live in the second book.

Here is what I have so far. It returns "No Values were found", But they are there.

Any Point in the right Direction will help!

Various Functions

from win32com.client import Dispatch

path1 = (r'C:Full Path\Book1.xlsx')
path2 = (r'C:\Full Path\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))

wb1.Close(SaveChanges=True)

#Cant get this part to work
ws2.Cells.Replace('C:Full Path\[Book1.xlsx]','')

Replace.Execute(ReplaceAll=1, Forward=True)

wb2.Close(SaveChanges=True)

xl.Quit()

I think the issue is letting excel know where to execute the Find and Replace.

Roope
  • 4,469
  • 2
  • 27
  • 51
Tug01519
  • 13
  • 4

1 Answers1

0

Find and Replace are coupled in VBA. Do not be fooled by Python. This is a VBA problem, not a python problem! Once you involve win32com, you have to use VBA Methods and some of the syntax. Replace cannot work on its own, first, you need to mark a Range with Find that you want to Replace.

Usually it goes

[object].Range.Find
   .[Text or Feature to find]
   .Replace.[what to replace with]
   .Replace.Execute

How to do this in python depends on exactly what you want to find. I do not understand from your Question what that actually would be.

tst
  • 371
  • 1
  • 11
  • Thank you so much for this, I will try to get this to work! – Tug01519 Nov 13 '19 at 12:38
  • Thank you so much for this, I will try to get this to work! So when I copied the sheet is copied into the second sheet. My goal is to have the formulas automatically start pulling when dropped into the new workbook.The issue is that the formula references the old book. So the find and replace is to remove the reference out of the formula. For example the =sum("tab2"A1:B1) Becomes =sum(C:Full Path\old file\[FileName]"tab2"A1:B1). the goal is to find and replace the full path in the formula! Let me know if you think this is possible. – Tug01519 Nov 13 '19 at 13:35
  • I'm not sure what you are trying to do here will accomplish this. Sounds to me more like you want to read data from one workbook and paste it into another. Find.Replace doesn't help you there (except for when you want to overwrite data in the second Workbook). If you just want to read data from one file and paste it into another, you have to read it in, store it in a variable in python, then write to the second book – tst Nov 13 '19 at 13:56
  • as per your other question, try xlrd: http://www.lexicon.net/sjmachin/xlrd.html – tst Nov 13 '19 at 14:05