replace method using ReplaceFormat .....

[SamDeMan]

Hi again,

wow. has been a long time since i last wrote to these boards.

here is my issue (read it on MSDN: http://support.microsoft.com/kb/823222)

i have an access report to export to excel. i am doing it a bit different than whats described in the article above. basically i have a report, my code exports it to Word, and then copy/paste it to excel and then it's modified in excel. ok, so i have one field that is text and comes in as a number when its pasted into excel. the way around it is to have that cell in access equal to: ="'" & [fieldName], so i concatenate the ' to the field. if you do the following in excel it will not work. find/replace ' with nothing, the cell will automatically format itself to a number.

so i have a way around it. when i extend the replace dialog screen i can add the replaceformat. so in excel (w/o using vba) its simple. i add the format as text and " " in the replace box and it works wonderfully. however, here is my dilema, i don't know how to do this in VBA. i tried looking for online help, and nothing. here is my code:

    With MySheet.Range("C1:D500")
        .Select
        .Replace What:="'", Replacement:="", ReplaceFormat:=?????
    End With

i tried adding ReplaceFormat:="Text" and it didn't work. anybody know how this works??

please help,

Thanks,

Sam

 

[SamDeMan]

found the answer....

after running the macro i saw that i missed out one part. here is the code.

Application.ReplaceFormat.NumberFormat = "@"
    Cells.Replace What:="'", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

and the explanation is that once you set "ReplaceFormat:=True" then it will follow the previous instructions of

Application.ReplaceFormat.NumberFormat = "@"

so the missing link was setting to true and the first line.

good luck guys,

sam