On Oct 9, 8:43*am, Jason wrote:
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 anHTAto 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
theHTAcode. 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 theHTA
<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

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.