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" wrote:

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" </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