I changed a PasteSpecial operation to Paste (with Link:=True) to accomplish
pasting a link. I know about fiddling with the object cited to get it to
work, and it does - sort of.

My question is why, in the results of the Paste link, does a space ("") in
the source cell become a zero (0) in the destination when the the
PasteSpecial faithfully copies the space ? Is there additional syntax in the
<iActivesheet.Paste Link:=True</i stetement that will cause the correct
copying of spaces ?

Thanks in advance.

-dj

You have to use xlPasteValuesAndNumberFormats to carry the format over to the
new location also. Otherwise the Excel default takes precedence and treats
"" as zero for a value.

"DonJ_Austin" wrote:

I changed a PasteSpecial operation to Paste (with Link:=True) to accomplish
pasting a link. I know about fiddling with the object cited to get it to
work, and it does - sort of.

My question is why, in the results of the Paste link, does a space ("") in
the source cell become a zero (0) in the destination when the the
PasteSpecial faithfully copies the space ? Is there additional syntax in the
<iActivesheet.Paste Link:=True</i stetement that will cause the correct
copying of spaces ?

Thanks in advance.

-dj

Thanks, Whiz

I'm not finding any guidance on such an option in Paste method. I have used
Paste:=xlPasteValues in a PasteSpecial, but the following guessed-at syntax
is not accepted.

ActiveSheet.Paste Link:=True Paste:=xlPasteValuesAndNumberFormats

Could you be more specific ?

BTW, developing in XL11 but deploying for use in XL11 and xl12. Special
issues ?

-dj

"JLGWhiz" wrote:

You have to use xlPasteValuesAndNumberFormats to carry the format over to the
new location also. Otherwise the Excel default takes precedence and treats
"" as zero for a value.

"DonJ_Austin" wrote:

I changed a PasteSpecial operation to Paste (with Link:=True) to accomplish
pasting a link. I know about fiddling with the object cited to get it to
work, and it does - sort of.

My question is why, in the results of the Paste link, does a space ("") in
the source cell become a zero (0) in the destination when the the
PasteSpecial faithfully copies the space ? Is there additional syntax in the
<iActivesheet.Paste Link:=True</i stetement that will cause the correct
copying of spaces ?

Thanks in advance.

-dj

Should have said that using the Link argument will carry over exactly what
you have in the cell but Excel will use the default number format to display
the value. The way to get the number format to remain intact is to not use
the Link argument, but do use the Paste:=xlPasteValuesAndNumberFormat
argument. Or use the Link argument and then include a line of code that
formats the cell to not show the zero.
You are up against a built in Excel anomaly which you have to outsmart.

"DonJ_Austin" wrote:

Thanks, Whiz

I'm not finding any guidance on such an option in Paste method. I have used
Paste:=xlPasteValues in a PasteSpecial, but the following guessed-at syntax
is not accepted.

ActiveSheet.Paste Link:=True Paste:=xlPasteValuesAndNumberFormats

Could you be more specific ?

BTW, developing in XL11 but deploying for use in XL11 and xl12. Special
issues ?

-dj

"JLGWhiz" wrote:

You have to use xlPasteValuesAndNumberFormats to carry the format over to the
new location also. Otherwise the Excel default takes precedence and treats
"" as zero for a value.

"DonJ_Austin" wrote:

I changed a PasteSpecial operation to Paste (with Link:=True) to accomplish
pasting a link. I know about fiddling with the object cited to get it to
work, and it does - sort of.

My question is why, in the results of the Paste link, does a space ("") in
the source cell become a zero (0) in the destination when the the
PasteSpecial faithfully copies the space ? Is there additional syntax in the
<iActivesheet.Paste Link:=True</i stetement that will cause the correct
copying of spaces ?

Thanks in advance.

-dj

"JLGWhiz" wrote:

snip
You are up against a built in Excel anomaly which you have to outsmart.

Not the first time. :-)

Thanks.

-dj

"DonJ_Austin" wrote:

Thanks, Whiz

I'm not finding any guidance on such an option in Paste method. I have used
Paste:=xlPasteValues in a PasteSpecial, but the following guessed-at syntax
is not accepted.

ActiveSheet.Paste Link:=True Paste:=xlPasteValuesAndNumberFormats

Could you be more specific ?

BTW, developing in XL11 but deploying for use in XL11 and xl12. Special
issues ?

-dj

"JLGWhiz" wrote:

You have to use xlPasteValuesAndNumberFormats to carry the format over to the
new location also. Otherwise the Excel default takes precedence and treats
"" as zero for a value.

"DonJ_Austin" wrote:

I changed a PasteSpecial operation to Paste (with Link:=True) to accomplish
pasting a link. I know about fiddling with the object cited to get it to
work, and it does - sort of.

My question is why, in the results of the Paste link, does a space ("") in
the source cell become a zero (0) in the destination when the the
PasteSpecial faithfully copies the space ? Is there additional syntax in the
<iActivesheet.Paste Link:=True</i stetement that will cause the correct
copying of spaces ?

Thanks in advance.

-dj