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