Re: Find/Replace in Excel by Tim
Tim
Fri Jul 14 16:35:37 CDT 2006
Try
objXL.DisplayAlerts=False
....'your replace code
objXL.DisplayAlerts=True
--
Tim Williams
Palo Alto, CA
"RogueIT" <RogueIT@discussions.microsoft.com> wrote in message news:4ABF3FA8-2440-41E1-AB17-88AC6B7FD2E3@microsoft.com...
> Thank you that was a tremendous help, however another problem has presented
> itself
> In the event that there is no unch, an error pops up saying how my there was
> no data to replace. I think I need an if then statement how would I write an
> IF then statement to avoid the "no data" error message?
>
> Thanks,
> RogueIT
>
>
> "Tim Williams" wrote:
>
> >
> > "RogueIT" <RogueIT@discussions.microsoft.com> wrote in message news:8113B608-32B6-4B44-8518-505B2291E55A@microsoft.com...
> > > Well sorry to be so dense but your suggestion poses a couple of questions
> > > 1. What is a named argument?
> >
> > What:="unch" is a named argument: when scripting excel you can only
> > use the "unch" part
> >
> > > 2. What would an Excel constant be?
> >
> > xlPart is an excel constant: it's just a handy name for a numeric
> > value, in this case 2
> >
> > The code recorded in Excel:
> >
> > Cells.Replace What:="unch", Replacement:="0", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False, _
> > SearchFormat:=False, ReplaceFormat:=False
> >
> > from script would look something like this:
> >
> > objExcel.Activesheet.Cells.Replace "unch", "0", 2, 1, False, False, False
> >
> > Hope that helps.
> >
> > Tim
> >
> >
> >
> >
> >
> > > 3. and I just feel like I will have a question about the numeric equivalents?
> > >
> > > thanks for the responces.
> > > RogueIT
> > >
> > > "Tim Williams" wrote:
> > >
> > > > Jennifer's suggestion is a good one - perfrom the action in Excel while recording a macro and transfer the resulting code to
> > your
> > > > script.
> > > >
> > > > Remember though that you can't use named arguments when automating Excel from ascript, and you can't use any excel constants
> > unless
> > > > you've defined them in your code. To find the numeric equivalents look up the constants in the Excel object browser (F2
from
> > the VB
> > > > editor).
> > > >
> > > > --
> > > > Tim Williams
> > > > Palo Alto, CA
> > > >
> > > >
> > > > "Jennifer" <J.Evans.1970@gmail.com> wrote in message news:1152803178.959213.171240@m73g2000cwd.googlegroups.com...
> > > > > There is a replace method in Excel. I did a create macro, and the
> > > > > following code was produced.
> > > > >
> > > > > Cells.Replace What:="unch", Replacement:="0", LookAt:=xlPart,
> > > > > SearchOrder _
> > > > > :=xlByRows, MatchCase:=False, SearchFormat:=False,
> > > > > ReplaceFormat:=False
> > > > >
> > > > > I'm sure you could figure out how to work it in your own code.
> > > > >
> > > > > HTH,
> > > > > Jennifer
> > > > >
> > > > > RogueIT wrote:
> > > > > > Hello,
> > > > > > I have a couple of files that do a web query. I need to add a function to do
> > > > > > a find and replace before the .SaveAs
> > > > > > I want to find all the "unch" and replace it with the number 0. I have added
> > > > > > the working code and the new attempt.
> > > > > > There could be multiple instances of the unch but I don't know how to setup
> > > > > > the loop. Any help would be greatly appreciated.
> > > > > > thanks,
> > > > > > Rogue
> > > > > >
> > > > > >
> > > > > > *********************WORKING CODE*************************
> > > > > > Set objExcel = CreateObject("Excel.Application")
> > > > > > objExcel.Visible = True
> > > > > >
> > > > > > Set objWorkbook = objExcel.Workbooks.Open("C:\base\high.xls")
> > > > > > Set objWorksheet = objWorkbook.Worksheets(1)
> > > > > > objWorkbook.Sheets("sheet1").QueryTables(1).Refresh
> > > > > >
> > > > > > WScript.Sleep 10000
> > > > > >
> > > > > > Dim strNOW
> > > > > > strNOW = Now
> > > > > > Dim strYMD
> > > > > > strYMD = Right(100+DatePart("m",strNOW),2) &"-"
> > > > > > strYMD = strYMD & Right(100+DatePart("d",strNOW),2) & "-"
> > > > > > strYMD = strYMD & DatePart("yyyy",strNOW)
> > > > > >
> > > > > >
> > > > > > objWorkbook.SaveAs("C:\data\high" & strYMD & ".xls")
> > > > > >
> > > > > >
> > > > > >
> > > > > > Set objWorkbook = objExcel.Workbooks.Open("C:\base\low.xls")
> > > > > > Set objWorksheet = objWorkbook.Worksheets(1)
> > > > > > objWorkbook.Sheets("sheet1").QueryTables(1).Refresh
> > > > > >
> > > > > > WScript.Sleep 10000
> > > > > >
> > > > > >
> > > > > >
> > > > > > objWorkbook.SaveAs("C:\data\low" & strYMD & ".xls")
> > > > > >
> > > > > >
> > > > > > objExcel.Quit
> > > > > > *********************END WORKING CODE************************
> > > > > >
> > > > > > *********************TEMPORARY CODE*************************
> > > > > > Set objExcel = CreateObject("Excel.Application")
> > > > > > objExcel.Visible = True
> > > > > > dim txt
> > > > > > txt = "unch"
> > > > > >
> > > > > > Set objWorkbook = objExcel.Workbooks.Open("C:\base\high.xls")
> > > > > > Set objWorksheet = objWorkbook.Worksheets(1)
> > > > > > objWorkbook.Sheets("sheet1").QueryTables(1).Refresh
> > > > > >
> > > > > > WScript.Sleep 10000
> > > > > >
> > > > > > Dim strNOW
> > > > > > strNOW = Now
> > > > > > Dim strYMD
> > > > > > strYMD = Right(100+DatePart("m",strNOW),2) &"-"
> > > > > > strYMD = strYMD & Right(100+DatePart("d",strNOW),2) & "-"
> > > > > > strYMD = strYMD & DatePart("yyyy",strNOW)
> > > > > >
> > > > > > objWorksheet.write(Replace(txt, "unch", "0"))
> > > > > > objWorkbook.SaveAs("C:\data\high" & strYMD & ".xls")
> > > > > >
> > > > > >
> > > > > >
> > > > > > Set objWorkbook = objExcel.Workbooks.Open("C:\base\low.xls")
> > > > > > Set objWorksheet = objWorkbook.Worksheets(1)
> > > > > > objWorkbook.Sheets("sheet1").QueryTables(1).Refresh
> > > > > >
> > > > > > WScript.Sleep 10000
> > > > > >
> > > > > >
> > > > > > objWorksheet.write(Replace(txt, "unch", "0"))
> > > > > > objWorkbook.SaveAs("C:\data\low" & strYMD & ".xls")
> > > > > >
> > > > > >
> > > > > > objExcel.Quit
> > > > > > *********************END TEMPORARY CODE*********************
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >