Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Getting email Hyperlinks from sahpes into cells
Each month I web access a customer list and put in an Excel workbook. Each
customer cell - Row 3 of the sheet - contains up to 3 shapes - one of which can contain a hyperlink containing an email address. I have tried all I can think of to try to grab the email address from every shape that contains one and place it in the cell to its right. So far totally unsuccessful. I have recorded many macros, looked at all parts of the code in Help and looked at all relaated properties and methods??? Any ideas?? Thanks, -- Gus Evans |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Getting email Hyperlinks from sahpes into cells
Gus,
Here is my idea... '-------------------------------------- Sub GetShapeHyperlinkAddress() 'Loops thru all shapes on active sheet. 'Adds 4 properties of each shape to columns E:H on active sheet. 'Jim Cone December 24, 2004 Dim shpCustomer As Excel.Shape Dim arrString(1 To 4) As String Dim lngNum As Long arrString(1) = "Name is: " arrString(2) = "Address is: " arrString(3) = "Sub Address is: " arrString(4) = "Location is: " Range(Cells(1, 5), Cells(1, 8)).Value = arrString Range(Cells(1, 5), Cells(1, 8)).Font.Bold = True For lngNum = 1 To ActiveSheet.Shapes.Count Set shpCustomer = ActiveSheet.Shapes(lngNum) arrString(1) = shpCustomer.Name arrString(2) = shpCustomer.Hyperlink.Address arrString(3) = shpCustomer.Hyperlink.SubAddress arrString(4) = shpCustomer.TopLeftCell.Address(False, False) Range(Cells(lngNum + 1, 5), Cells(lngNum + 1, 8)).Value = arrString Next 'lngNum Columns("E:H").AutoFit Set shpCustomer = Nothing End Sub '-------------------------------------- Regards, Jim Cone San Francisco, USA "GusEvans" wrote in message ... Each month I web access a customer list and put in an Excel workbook. Each customer cell - Row 3 of the sheet - contains up to 3 shapes - one of which can contain a hyperlink containing an email address. I have tried all I can think of to try to grab the email address from every shape that contains one and place it in the cell to its right. So far totally unsuccessful. I have recorded many macros, looked at all parts of the code in Help and looked at all relaated properties and methods??? Any ideas?? Thanks, Gus Evans |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Getting email Hyperlinks from sahpes into cells
Thanks it worked like a charm! I couldn't figure out to get back to the
newsgroup to read your answer after getting notified that their was a response until now - Microsoft certainly doesn't make it easy ;-) Thanks again Gus "Jim Cone" wrote: Gus, Here is my idea... '-------------------------------------- Sub GetShapeHyperlinkAddress() 'Loops thru all shapes on active sheet. 'Adds 4 properties of each shape to columns E:H on active sheet. 'Jim Cone December 24, 2004 Dim shpCustomer As Excel.Shape Dim arrString(1 To 4) As String Dim lngNum As Long arrString(1) = "Name is: " arrString(2) = "Address is: " arrString(3) = "Sub Address is: " arrString(4) = "Location is: " Range(Cells(1, 5), Cells(1, 8)).Value = arrString Range(Cells(1, 5), Cells(1, 8)).Font.Bold = True For lngNum = 1 To ActiveSheet.Shapes.Count Set shpCustomer = ActiveSheet.Shapes(lngNum) arrString(1) = shpCustomer.Name arrString(2) = shpCustomer.Hyperlink.Address arrString(3) = shpCustomer.Hyperlink.SubAddress arrString(4) = shpCustomer.TopLeftCell.Address(False, False) Range(Cells(lngNum + 1, 5), Cells(lngNum + 1, 8)).Value = arrString Next 'lngNum Columns("E:H").AutoFit Set shpCustomer = Nothing End Sub '-------------------------------------- Regards, Jim Cone San Francisco, USA "GusEvans" wrote in message ... Each month I web access a customer list and put in an Excel workbook. Each customer cell - Row 3 of the sheet - contains up to 3 shapes - one of which can contain a hyperlink containing an email address. I have tried all I can think of to try to grab the email address from every shape that contains one and place it in the cell to its right. So far totally unsuccessful. I have recorded many macros, looked at all parts of the code in Help and looked at all relaated properties and methods??? Any ideas?? Thanks, Gus Evans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deactivate email hyperlinks in Excel 2007 | Excel Discussion (Misc queries) | |||
Hyperlinks and Email Addresses in Excel | Excel Worksheet Functions | |||
How toi turn-off hyperlinks [excel]? Email hyperlinks pop up ! | Excel Discussion (Misc queries) | |||
email excel document with hyperlinks to mutliple documents | Excel Discussion (Misc queries) | |||
Microsoft Excel 2003- Email hyperlinks | Excel Discussion (Misc queries) |