Replace All Text in Workbook

[jgoodman00]

I am using VBA to open an Excel Workbook. This workbook contains a number of worksheets created by a series of dts packages which export data from SQL Server.

The data coming from SQL Server has a lot of True/False columns. I need to convert the True/False entries to Yes/No respectively.

Here is the code I am trying:

'Open the excel file
Set objExcelDoc = objExcel.Workbooks.Open(strTarget)
Dim i As Integer
On Error Resume Next
With objExcel
    .Cells.Replace What:="FALSE", Replacement:="No", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .Cells.Replace What:="TRUE", Replacement:="Yes", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With

It never works, always stating the search string could not be found...

Any suggestions?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[PHV]

Perhaps are the value 0 for False and -1 (or 1) for True ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jgoodman00]

I dont think so:

I copied the

Cells.Replace

code from a macro I recorded in the workbook, which worked.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[TropicalFred]

I believe that PHV is right and that you are searching for the text "True" and Excel is finding the value of the result of a True/False Boolean expression.

Try this: Before you do your search/replace, copy the range of True/False cells and paste them back in the same cells as values. (Highlight range; Edit > Copy; Edit > Pastespecial > Values > OK) Then run your code.

Good Luck.

 

[jgoodman00]

If I copy them into a clean sheet using PasteSpecial-Values, they are pasted as TRUE/FALSE. The datatype of these columns is General.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures