Paste problem from HTA

  • Thread starter Thread starter Craig Williams
  • Start date Start date
C

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
 
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.
 
Sorry bout that, should be CStr function not CText.  Had text on my mind.
Click to expand...

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.
 
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
 
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
Click to expand...

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.
 
Back
Top