On Sep 29, 5:13*pm, "JLGWhiz" wrote:
Sorry bout that, should be CStr function not CText. *Had text on my mind.
"Craig Williams" wrote in message
...
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
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.