Paste problem from HTA

[Craig Williams]

I'm writing an HTA to allow browsing of user/group membership in a
table. Users here like to be able to save this info, forward to
others, etc, so I have an onclick action to copy the table to excel
so they can do that. Some of the groups names are department numbers
that contain only digits, sometimes with a leading zero.

The default cell format in excel is "general" which treats these a
numeric and truncates the leading zeros. I do not want those
truncated, so I change the cell format to text with "@" in vbscript. I
have verified that this numberformat command is successful by
commenting out the paste action and then reviewing the format set by
the HTA code. But as soon as I add the paste command, the format is
lost or overwritten. Reviewing the format at that time shows the
format list but none of the formats are selected. I also tried
pastespecial -4163 for xlPasteValues, but that had no effect. Why does
doing the paste overwrite the format that was applied?

Thanks


Snippet of the HTML part of the HTA
<div id="htaResults">&nbsp;</div>
</CENTER>
</BODY>
</HTML>

Sub to copy the table to excel
Sub CopyExcel
strCopy = htaResults.InnerHTML
document.parentwindow.clipboardData.SetData "text", strCopy
oExcel.Visible = True
If iSheet <> 0 Then
oworkBook.Sheets.Add
End if
iSheet = iSheet + 1

' New sheets are always added to the left in position 1 to avoid any
errors
Set oworkSheet = oworkBook.Worksheets(1)
oworkBook.Worksheets(1).Activate
oworkSheet.Name = Left(sSheetName, 30)

' Format cells for text (default is general that truncates leading
zeros on group names)
oworkSheet.Columns("A").NumberFormat = "@"

oworkSheet.Paste
Set oRange = oworkSheet.UsedRange
oRange.WrapText = False ' Turn off wrap text for canonicalName
field in user results
oRange.EntireColumn.Autofit()
oRange.EntireRow.Autofit()
End Sub

Sample data within the table being copied:
0801 Enterprise Support/Chicago/Groups/0801
0802 Enterprise Support/Chicago/Groups/0802
0803 Enterprise Support/Chicago/Groups/0803
0804 Enterprise Support/Chicago/Groups/0804

Data as it appears in excel
801 Enterprise Support/Chicago/Groups/0801
802 Enterprise Support/Chicago/Groups/0802
803 Enterprise Support/Chicago/Groups/0803
804 Enterprise Support/Chicago/Groups/0804

 

[JLGWhiz]

While it was not specified where the data was coming from, I assume it is
not another Excel file. That means that if it looks like a number, Excel
will see it as a number (Sorry about that), so it will use that data type to
post to the Excel worksheet. You can probably use the CText function to
coerce the data to a Text data type before pasting to the worksheet.

 

[JLGWhiz]

Sorry bout that, should be CStr function not CText. Had text on my mind.

 

[Craig Williams]

Sorry bout that, should be CStr function not CText.  Had text on my mind.

The data is coming from active directory, with ADODB and
Provider=ADsDSOObject.
I forgot to mention that I had already tried CStr'ing the data to no
avail.
I think this is a format problem. If I type "0123" into a general type
column with no vb, it shows "123" without the leading 0. It only
retains the 0 if I first format the column to text before entering the
number.

 

[Jason]

Have you tried using PasteSpecial instead of Paste? (ie. <obj>.PasteSpecial
xlPasteValuesAndNumberFormats)

-or-

This is probably not the most eloquent solution. But, before you copy,
prepend a single apostrophe ( ' ) to the data that you want to retain leading
zeros. This should force Excel to treat the values literally.

J

 

[Craig Williams]

Have you tried using PasteSpecial instead of Paste? (ie. <obj>.PasteSpecial
xlPasteValuesAndNumberFormats)

-or-

This is probably not the most eloquent solution. But, before you copy,
prepend a single apostrophe ( ' ) to the data that you want to retain leading
zeros. This should force Excel to treat the values literally.

J

Yes I did. If you look back, in original post I said:
I also tried pastespecial -4163 for xlPasteValues, but that had no
effect.
Was not able to use any of the xlPaste constants because this is HTA
not VBA within excel.