3

I have the following text stored in a cell in a worksheet

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">

Now when I copy and paste it to notepad from that I get the extra quote sign after every quote so it becomes like the following string

<tr onmouseover=""this.style.backgroundColor='#74eb61';"" onmouseout=""this.style.backgroundColor='#e8e8e8';"">

enter image description here

You can recreate this by:

Copy this text:

<!-- CSS goes in the document HEAD or added to your external stylesheet -->
<style type="text/css">
table.hovertable {
 font-family: verdana,arial,sans-serif;
 font-size:11px;
 color:#333333;
 border-width: 1px;
 border-color: #999999;
 border-collapse: collapse;
}
table.hovertable th {
 background-color:#009fe3;
  color:#FFFFFF;
 border-width: 1px;
 padding: 8px;
 border-style: solid;
 border-color: #a9c6c9;
}
table.hovertable tr {
 background-color:#e8e8e8;

}
table.hovertable td {
 border-width: 1px;
 padding: 8px;
 border-style: solid;
 border-color: #a9c6c9;
}
</style>
<!-- Table goes in the document BODY -->
<table class="hovertable">
 <th colspan="4">Info Header</th>

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">
 <td>Item 1A</td><td>Item 1B</td><td>Item 1C</td>
</tr>

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">
 <td>Item 2A</td><td>Item 2B</td><td>Item 2C</td>
</tr>

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">
 <td>Item 3A</td><td>Item 3B</td><td>Item 3C</td>
</tr>

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">
 <td>Item 4A</td><td>Item 4B</td><td>Item 4C</td>
</tr>

<tr onmouseover="this.style.backgroundColor='#74eb61';" onmouseout="this.style.backgroundColor='#e8e8e8';">
 <td>Item 5A</td><td>Item 5B</td><td>Item 5C</td>
</tr>

</table>

and paste to any cell in Excel

And then Copy the cell contents and paste on notepad and you will find extra quotes

Stupid_Intern
  • 3,382
  • 8
  • 37
  • 74
  • Remove the existing quotes in the string `Stystr = Replace(Trim(wh.Range("A2").Text),"""","")` – Scott Holtzman Jan 22 '16 at 18:05
  • Also in the statement `Dim Stystr, endHTb, StRTb, tbstr, tbTag, ctb As String` only **ctb** is dimensioned as a `String` type. All the others are variants. To dimension them all as `String` type, write `Dim Stystr as String, endHTb as String, StRTb as String ...` – Scott Holtzman Jan 22 '16 at 18:06
  • @ScottHoltzman nope I don't think that will work since Every `""` is replaced by `""""` and `"` is replaced by `""` so can't differentiate between which needs to be replaced – Stupid_Intern Jan 22 '16 at 18:10
  • I am not sure what you mean by every `""` is replaced by `""""` and `"` is replaced by `""`? Are you talking about in my formula? Or the results of your code? – Scott Holtzman Jan 22 '16 at 18:18
  • can you do a debug.print on `Stystr` right after you set the variable and let me know if the extra quotes appear from the start? Because I just tested it and cannot replicate your issue (at the moment). – Scott Holtzman Jan 22 '16 at 18:20
  • I have added workbook you can download it – Stupid_Intern Jan 22 '16 at 18:22
  • will not and cannot download macro laden workbooks. idea is to step through code and find out where it's adding the additional `""` and then correct it at that point. – Scott Holtzman Jan 22 '16 at 18:28
  • okay so in the immediate window I get the result as expected but when I copy the same result from the cell I get the extra quotes I think you should really look at that file to understand what I am saying – Stupid_Intern Jan 22 '16 at 18:42

1 Answers1

4

This is 'behavior by design'. The clipboard's involvement in the process of transferring the cell value from XL to Notepad++ mean that the text string is evaluated. There are a lot of scenarios that are tested for (I do not have a complete list) but it seems that your paste operation is being interpreted as creating a comma separated or tab separated TXT export file. The quote characters are being doubled up because your system is using the quote character as the Text Qualifier special character in an XL-to-TXT export.

Unfortunately, you do not want this transitional behavior but many others do. If you need to bypass it, copy the text string from the formula bar or in-cell while in edit mode. This is not Excel's doing or even the Office Clipboard (OCB). It is the Windows clipboard's behavior which is trying to interpret a transfer from XL to Notepad++.