save data , images and hyperlink between two workbooks

[wiwi]

I have two file Order Confirmation.xls and Order Summary.xls
After fill-up the Order Confirmation sheet, click button (macro) will
add the data into Order Summary in last row. My problem is I don't know
how to add the image name and create a hyperlink to order confirmation
sheet in order summary (image & file column).
the purpose to keep the images is I want to view order summary,
the certain range of cell will display the image and I also can click
on hyperlink file to open the order confirmation file.I'll have a lot
of order confirmation file.

below is my macro
please help and give me some advice

Sub sketchOC()
Dim x As Variant
Dim picname
x = Application.GetOpenFilename("Pictures (*.jpg;*.bmp;*.gif),
*.jpg;*.bmp", , "Select Picture")
If x = False Then Exit Sub 'no file selected
Set mypic = ActiveSheet.Pictures.Insert(x)
With mypic
..Name = Range("AJ3")
..Left = Range("Az9").Left
..Top = Range("Az9").Top
..Height = 220
..Width = 200

End With
End Sub


Sub addto()
Dim WS_OrderConfirm As Worksheet
Dim WS_OrderSummary As Worksheet
Dim LastRow
Dim L As Long
Dim Pics As Shape
Dim shp As Shape

Workbooks.Open Filename:= _
"C:\Documents and Settings\default\Desktop\Order Summary.xls"

Set WS_OrderConfirm = Workbooks("order
confirmation.xls").Worksheets("Order Confirmations (2)")
Set WS_OrderSummaryPICS = Workbooks("Order
Summary").Worksheets("PICS")
Set WS_OrderSummary = Workbooks("Order Summary").Worksheets("Order
Summary")

WS_OrderSummary.Activate
LastRow = Application.CountA(ActiveSheet.Range("B:B")) + 1 'Find
last cell/row plus one
Cells(LastRow, 2).Value = WS_OrderConfirm.Range("$AJ$3").Value


WS_OrderConfirm.Activate
ActiveSheet.mypic.Select
Selection.Copy

WS_OrderSummaryPICS.Activate
ActiveSheet.Paste
Selection.Name = "abc"

End Sub

Attachment filename: desktop.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=392274

 

[wiwi]

Thanks you very much Dave...It's great..That's exactly what I want.

I have another question is how to arrange the picture in my sheet
(pics) ? When I add new record into the Order Summary, the picture will
overlap to each other.
Is it can check the picture position like we check last row ? Then can
paste the picture to the right position (row & column).

Regards,
wiwi

 

[Dave Peterson]

You can cycle through all the pictures and look for the bottomleftcell.row and
use the maximum that you find.

But maybe you could just use an adjacent cell with text in it and use the stuff
you use to find the lastrow.

with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

and then add something to it to get by height of the picture (+10???).