replace method using ReplaceFormat .....

[Guest]

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:


Code:
With MySheet.Range("C1500")
.Select
.Replace What:="'", Replacement:="", ReplaceFormat:=?????
End Withi tried adding ReplaceFormat:="Text" and it didn't work. anybody
know how this works??

please help,

Thanks,

Sam

 

[Dave Peterson]

What do you get when you record a macro using the settings you want.

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:

Code:
With MySheet.Range("C1500")
.Select
.Replace What:="'", Replacement:="", ReplaceFormat:=?????
End Withi tried adding ReplaceFormat:="Text" and it didn't work. anybody
know how this works??

please help,

Thanks,

Sam

 

[Guest]

hey dave,

sorry bro for not responding quicker. i thought you had no clue, now i got
it. so here it is. i just recorded a macro and this is what i got:

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

now i am not sure what it is that will cause the text to be changed. is it
the first line?

thanks,

sam

What do you get when you record a macro using the settings you want.

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:

Code:
With MySheet.Range("C1500")
.Select
.Replace What:="'", Replacement:="", ReplaceFormat:=?????
End Withi tried adding ReplaceFormat:="Text" and it didn't work. anybody
know how this works??

please help,

Thanks,

Sam

 

[Dave Peterson]

A number format of text is represented in code as "@".

hey dave,

sorry bro for not responding quicker. i thought you had no clue, now i got
it. so here it is. i just recorded a macro and this is what i got:

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

now i am not sure what it is that will cause the text to be changed. is it
the first line?

thanks,

sam

What do you get when you record a macro using the settings you want.

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:

Code:
With MySheet.Range("C1500")
.Select
.Replace What:="'", Replacement:="", ReplaceFormat:=?????
End Withi tried adding ReplaceFormat:="Text" and it didn't work. anybody
know how this works??

please help,

Thanks,

Sam

 

[Guest]

thanks,

it worked.

sam

hey dave,

sorry bro for not responding quicker. i thought you had no clue, now i got
it. so here it is. i just recorded a macro and this is what i got:

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

now i am not sure what it is that will cause the text to be changed. is it
the first line?

thanks,

sam

What do you get when you record a macro using the settings you want.

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:

Code:
With MySheet.Range("C1500")
.Select
.Replace What:="'", Replacement:="", ReplaceFormat:=?????
End Withi tried adding ReplaceFormat:="Text" and it didn't work. anybody
know how this works??

please help,

Thanks,

Sam