|
Paste problem from HTA
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 |
Paste problem from HTA
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. "Craig Williams" wrote in message ... 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 |
Paste problem from HTA
Sorry bout that, should be CStr function not CText. Had text on my mind.
"Craig Williams" wrote in message ... 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 |
Paste problem from HTA
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. |
Paste problem from HTA
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 |
Paste problem from HTA
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. |
| All times are GMT +1. The time now is 03:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com