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

 

[Dave Peterson]

I'm slightly confused about where the hyperlink should go--with the picture or
with the value in with the text (Pics or order summary sheet?)

But this may give you some ideas:

Option Explicit
Sub sketchOC()
Dim myPict As Picture
Dim x As Variant
Dim picName As String

x = Application.GetOpenFilename _
(filefilter:="Pictures , *.jpg;*.bmp;*.gif", Title:="Select Picture")
If x = False Then Exit Sub 'no file selected

Set myPict = ActiveSheet.Pictures.Insert(x)
With myPict
.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 WS_OrderSummaryPics As Worksheet
Dim Wkbk_Summary As Workbook
Dim wkbk_OrderConfirm As Workbook

Dim LastRow
Dim myPict As Picture
Dim newPict As Picture

'I like variables so I only have to change them in one spot

Set wkbk_OrderConfirm = ThisWorkbook 'workbooks("order confirmation.xls")

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


Set WS_OrderConfirm = wkbk_OrderConfirm.Worksheets("Order Confirmations (2)")

Set WS_OrderSummaryPics = Wkbk_Summary.Worksheets("PICS")
Set WS_OrderSummary = Wkbk_Summary.Worksheets("Order Summary")

With WS_OrderSummary
LastRow = Application.CountA(.Range("B:B")) + 1
.Cells(LastRow, 2).Value = WS_OrderConfirm.Range("$AJ$3").Value
'hyperlink in column C???
.Cells(LastRow, 3).Formula = "=hyperlink(" & Chr(34) _
& wkbk_OrderConfirm.FullName & Chr(34) & ")"
End With

With WS_OrderConfirm
Set myPict = .Pictures(1) 'first/only picture on sheet
'or
Set myPict = .Pictures(.Pictures.Count) 'last picture added??
End With

myPict.Copy
With WS_OrderSummaryPics
.Paste
Set newPict = .Pictures(.Pictures.Count)
End With

With newPict
.Name = "abc"
'or hyperlink with the picture????
.Parent.Hyperlinks.Add Anchor:=.ShapeRange.Item(1), _
Address:=wkbk_OrderConfirm.FullName
End With

Wkbk_Summary.Close savechanges:=True

End Sub

 

[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???).