Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks.Delete without losing formatting?
I have javascript function that copies a table from a webpage to an
Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count 0){ for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){ LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent; NumFmt = LinkCell.NumberFormat; IsBold = LinkCell.Font.Bold; IsItalic = LinkCell.Font.Italic; IsWrap = LinkCell.WrapText; hAlign = LinkCell.HorizontalAlignment; vAlign = LinkCell.VerticalAlignment; LinkCell.Hyperlinks.Delete(); LinkCell.NumberFormat = NumFmt; LinkCell.Font.Bold = IsBold; LinkCell.Font.Italic = IsItalic; LinkCell.WrapText = IsWrap; LinkCell.HorizontalAlignment = hAlign; LinkCell.VerticalAlignment = vAlign; } } And it works. It doesn't do the borders, because I haven't done that yet, but the basic concept works. And it takes forever and a day. So I was hoping that someone might know some arcane method of removing hyperlinks *without* killing the formatting in the process. Thanks, Lisa |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks.Delete without losing formatting?
I don't have a better way, but something that you might want to test.
Find a sacrificial cell. copy the hyperlink cell to that cell remove the hyperlink copy|paste special|formats back Clean up that sacrificial cell later. (still a giant loop though.) Or untested... Insert a new worksheet select all of the existing worksheet copy paste special|formats (to the new worksheet) remove the links (all at once) copy and paste|special back. That might actually work. (I didn't try it though. No sense both of us being disappointed!) Lisa wrote: I have javascript function that copies a table from a webpage to an Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count 0){ for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){ LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent; NumFmt = LinkCell.NumberFormat; IsBold = LinkCell.Font.Bold; IsItalic = LinkCell.Font.Italic; IsWrap = LinkCell.WrapText; hAlign = LinkCell.HorizontalAlignment; vAlign = LinkCell.VerticalAlignment; LinkCell.Hyperlinks.Delete(); LinkCell.NumberFormat = NumFmt; LinkCell.Font.Bold = IsBold; LinkCell.Font.Italic = IsItalic; LinkCell.WrapText = IsWrap; LinkCell.HorizontalAlignment = hAlign; LinkCell.VerticalAlignment = vAlign; } } And it works. It doesn't do the borders, because I haven't done that yet, but the basic concept works. And it takes forever and a day. So I was hoping that someone might know some arcane method of removing hyperlinks *without* killing the formatting in the process. Thanks, Lisa -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks.Delete without losing formatting?
Seems following trick is working: when you insert a dummy HYPERLINK FORMULA the style of the cell remains "Hyperlink" and is not reset to Normal. you loose the text formatting but the CELL formatting remains intact. Sub Redo() With ActiveCell .Clear .Hyperlinks.Add ActiveCell, "", "A1", , "CLICK ME" .Font.Bold = True .HorizontalAlignment = xlRight .VerticalAlignment = xlTop .Interior.ColorIndex = 3 .BorderAround , xlThick, xlColorIndexAutomatic, 7 End With End Sub Sub Remo() Dim s$, b As Boolean, i As Boolean With ActiveCell s = .Hyperlinks(1).TextToDisplay b = .Font.Bold i = .Font.Italic .Formula = "=HYPERLINK("""")" .Value = s .Font.Bold = b .Font.Italic = i End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : I don't have a better way, but something that you might want to test. Find a sacrificial cell. copy the hyperlink cell to that cell remove the hyperlink copy|paste special|formats back Clean up that sacrificial cell later. (still a giant loop though.) Or untested... Insert a new worksheet select all of the existing worksheet copy paste special|formats (to the new worksheet) remove the links (all at once) copy and paste|special back. That might actually work. (I didn't try it though. No sense both of us being disappointed!) Lisa wrote: I have javascript function that copies a table from a webpage to an Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count 0){ for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){ LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent; NumFmt = LinkCell.NumberFormat; IsBold = LinkCell.Font.Bold; IsItalic = LinkCell.Font.Italic; IsWrap = LinkCell.WrapText; hAlign = LinkCell.HorizontalAlignment; vAlign = LinkCell.VerticalAlignment; LinkCell.Hyperlinks.Delete(); LinkCell.NumberFormat = NumFmt; LinkCell.Font.Bold = IsBold; LinkCell.Font.Italic = IsItalic; LinkCell.WrapText = IsWrap; LinkCell.HorizontalAlignment = hAlign; LinkCell.VerticalAlignment = vAlign; } } And it works. It doesn't do the borders, because I haven't done that yet, but the basic concept works. And it takes forever and a day. So I was hoping that someone might know some arcane method of removing hyperlinks without killing the formatting in the process. Thanks, Lisa |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks.Delete without losing formatting?
Thanks, Dave. Actually, I did something similar, and it worked. The
PasteSpecial from the browser into Excel has to be a Worksheet PasteSpecial. I'm not sure why. So I paste that in, then copy what I've just pasted. I PasteSpecial it right underneath the first copy -- twice. Once xlValues and once xlFormats. Then I delete the rows that the first copy is in. What I'm left with has no hyperlinks, but retains the values and formatting (including borders) of what I started with. It works perfectly, and it's fast. The only possible downside I can see is that if I have a report that's longer than 32767 rows long, I might run into trouble. But then, copying 32767 rows into the clipboard will probably bring my computer to a crashing halt anyway, so I probably don't have to worry about that. <grin Thanks for your help, Lisa Dave Peterson wrote in message ... I don't have a better way, but something that you might want to test. Find a sacrificial cell. copy the hyperlink cell to that cell remove the hyperlink copy|paste special|formats back Clean up that sacrificial cell later. (still a giant loop though.) Or untested... Insert a new worksheet select all of the existing worksheet copy paste special|formats (to the new worksheet) remove the links (all at once) copy and paste|special back. That might actually work. (I didn't try it though. No sense both of us being disappointed!) Lisa wrote: I have javascript function that copies a table from a webpage to an Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count 0){ for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){ LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent; NumFmt = LinkCell.NumberFormat; IsBold = LinkCell.Font.Bold; IsItalic = LinkCell.Font.Italic; IsWrap = LinkCell.WrapText; hAlign = LinkCell.HorizontalAlignment; vAlign = LinkCell.VerticalAlignment; LinkCell.Hyperlinks.Delete(); LinkCell.NumberFormat = NumFmt; LinkCell.Font.Bold = IsBold; LinkCell.Font.Italic = IsItalic; LinkCell.WrapText = IsWrap; LinkCell.HorizontalAlignment = hAlign; LinkCell.VerticalAlignment = vAlign; } } And it works. It doesn't do the borders, because I haven't done that yet, but the basic concept works. And it takes forever and a day. So I was hoping that someone might know some arcane method of removing hyperlinks *without* killing the formatting in the process. Thanks, Lisa |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Hyperlinks.Delete without losing formatting?
I didn't compare speed, but this is kind of what I thought would work ok:
Option Explicit Sub testme1() Dim fWks As Worksheet Dim tWks As Worksheet Set fWks = Worksheets("sheet1") Set tWks = Worksheets.Add Application.ScreenUpdating = False fWks.Cells.Copy tWks.Range("A1").PasteSpecial Paste:=xlPasteFormats fWks.Hyperlinks.Delete tWks.Cells.Copy fWks.Range("a1").PasteSpecial Paste:=xlPasteFormats With Application .DisplayAlerts = False tWks.Delete .DisplayAlerts = True .ScreenUpdating = True End With End Sub I put about 30k hyperlinks in A1:A60000 and changed the format for the top few. The routine worked ok for me, but it slowed down when it was deleting all 30k hyperlinks. Maybe a short test would be in order??? Lisa wrote: Thanks, Dave. Actually, I did something similar, and it worked. The PasteSpecial from the browser into Excel has to be a Worksheet PasteSpecial. I'm not sure why. So I paste that in, then copy what I've just pasted. I PasteSpecial it right underneath the first copy -- twice. Once xlValues and once xlFormats. Then I delete the rows that the first copy is in. What I'm left with has no hyperlinks, but retains the values and formatting (including borders) of what I started with. It works perfectly, and it's fast. The only possible downside I can see is that if I have a report that's longer than 32767 rows long, I might run into trouble. But then, copying 32767 rows into the clipboard will probably bring my computer to a crashing halt anyway, so I probably don't have to worry about that. <grin Thanks for your help, Lisa Dave Peterson wrote in message ... I don't have a better way, but something that you might want to test. Find a sacrificial cell. copy the hyperlink cell to that cell remove the hyperlink copy|paste special|formats back Clean up that sacrificial cell later. (still a giant loop though.) Or untested... Insert a new worksheet select all of the existing worksheet copy paste special|formats (to the new worksheet) remove the links (all at once) copy and paste|special back. That might actually work. (I didn't try it though. No sense both of us being disappointed!) Lisa wrote: I have javascript function that copies a table from a webpage to an Excel worksheet. This is supposed to be a multi-purpose function that works on various different tables, and sometimes the tables have hyperlinks in them. I have my script doing Hyperlinks.Delete(), and that strips all the hyperlinks out of the sheet quite well. The problem is that when the hyperlinks are removed, any formatting that existed in those cells is removed as well. Borders, colors, font info, wrapping, number formatting: all gone. I tried doing this: if (objExcelApp.Selection.Hyperlinks.Count 0){ for (i = objExcelApp.Selection.Hyperlinks.Count; i 0; i--){ LinkCell = objExcelApp.Selection.Hyperlinks(i).Parent; NumFmt = LinkCell.NumberFormat; IsBold = LinkCell.Font.Bold; IsItalic = LinkCell.Font.Italic; IsWrap = LinkCell.WrapText; hAlign = LinkCell.HorizontalAlignment; vAlign = LinkCell.VerticalAlignment; LinkCell.Hyperlinks.Delete(); LinkCell.NumberFormat = NumFmt; LinkCell.Font.Bold = IsBold; LinkCell.Font.Italic = IsItalic; LinkCell.WrapText = IsWrap; LinkCell.HorizontalAlignment = hAlign; LinkCell.VerticalAlignment = vAlign; } } And it works. It doesn't do the borders, because I haven't done that yet, but the basic concept works. And it takes forever and a day. So I was hoping that someone might know some arcane method of removing hyperlinks *without* killing the formatting in the process. Thanks, Lisa -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Losing hyperlinks, lock Save As so it cannot be saved in a lower format | Excel Discussion (Misc queries) | |||
Excel losing hyperlinks when I sort the worksheet | Links and Linking in Excel | |||
Losing hyperlinks | Excel Discussion (Misc queries) | |||
Copying HTML into Excel Losing Hyperlinks | Excel Discussion (Misc queries) | |||
Delete without losing references? | Excel Programming |