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

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

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