Trợ giúp về hàm auto insert picture (1 người xem)

chiphoi3 · 29/9/09

Chào tất cả mọi người,

Mình mới tham gia diễn đàn GPE, vấn đề của mình liên quan đến việc tự động insert hình ảnh cá nhân của nhân sự trong công ty. Mình đã dùng chức năng search của diễn dàn và thấy có 1 số topic cũng liên quan đến vấn đề này nhưng chưa hoàn thiện, nay mình xin mở topic nhờ các anh chị có kinh nghiệm trợ giúp.

Các topic liên quan:

http://www.giaiphapexcel.com/forum/showthread.php?t=25440
http://www.giaiphapexcel.com/forum/showthread.php?p=131046
http://www.giaiphapexcel.com/forum/showthread.php?t=4669

Mình tổng hợp lại vấn đề như sau:
- Chức năng insert picture sẽ được tự động dựa trên đường dẫn có sẵn
- Nếu thay đổi đường dẫn thì hình cũ sẽ được xóa đi, và hình mới sẽ được cập nhật
- Có thể dùng chức năng Fill handle (giống như fill down khi dùng chuột kéo xuống như trong hình). Vấn đề này mình chưa thấy đoạn mã nào trong GPE giải quyết được nếu như dữ liệu khá nhiều (bị duplicate hình), ko thể manual insert ID cho từng cell được. Đây cũng chính là vấn đề lớn nhất của mình

- Vị trí của hình khi insert linh động (trong ví dụ của mình thì mình muốn nó gắn vào column C)
- Hình ở vị trí center của cell (đối với cell quá lớn mà hình quá nhỏ)
hoặc tự động resize hình theo kích thước mặc định của cell
- Đường dẫn thư mục hình ảnh linh hoạt (không nhất thiết phải nằm cùng 1 thư mục với file excel)
- Sau khi insert hình thì có thể xóa đường dẫn đi mà ko bị mất hình
- Hình ảnh được attach thẳng vào file excel và có thể share cho người khác mà ko cần các file hình ảnh đi kèm (vì file hình ảnh nằm rải rác các folder khác nhau như ví dụ hình trên)

Mình cũng có search google hy vọng kiếm 1 cái function dạng user-defined giải quyết vấn đề này nhưng vẫn chưa tìm ra.
Mình đính kèm file data mẫu, hy vọng các cao thủ của GPE giúp mình.
Thân.

ptm0412 · 29/9/09

Giúp Chèn hình ảnh vào cột C theo đường dẫn ở cột B.

Có bạn nhắn tin nhờ tôi làm giúp như sau:

chiphoi3 đã viết:
Chèn hình ảnh tự động
Dear ptm0412,

Mình vừa gia nhập diễn đàn, mình đang cần tìm giải pháp để tự động cập nhật ảnh 3/4 cho tất cả nhân viên của mình. Đi dạo 1 vòng quanh các diễn đàn thì nghe mọi người nói là ptm có thể giúp đỡ được nên mạo muội pm. Hy vọng ko làm phiền ptm

Hiện tại mình có 1 spreadsheet trong đó column A là ID của nhân viên, và column B là đường dẫn đến ảnh 3/4 của nhân viên đó. Ko biết có cách nào để tự động chèn hình ảnh của nhân viên đó (dựa trên đường dẫn ở column B) vào column C hay ko. Nếu viết được thành công thức thì tốt quá.

Thêm 1 vấn đề nữa là hình ảnh insert vào sẽ tự động fit với kích thước cell định sẵn (vì có 1 số ảnh 2/3,3/4 và cả 4/6)

Rất mong được ptm giúp đỡ.
Thân.

Tôi đang mệt nên không giúp được, nhờ mọi người làm dùm. Xin cám ơn.

sealand · 29/9/09

Bạn xem có phải thế này không? (Lưu ý bạn phải giải nén ra máy mới chạy được vì lúc đó mới có địa chỉ của ảnh) Cái ví dụ của mình như vậy chắc bạn biết cách đưa nó vào nơi bạn muốn

khoi.tb · 21/7/11

Bạn tham khảo bài toán này tương tự của bạn và họ có solutions với đoạn code dưới đây

I have an image folder in my documents containing pictures of my entire inventory. The file names of the images are their style #'s. Now, in microsoft excel 2007, i have a list of style #'s in column A, and i want to automatically insert the associated images from my image folder into the respective cells in column B

“This macro hasn't been tested with Excel 2007, but does work with Excel 2003. Give it try. You will need to change the folder name in PicPath to the folder name you are using. Currently it is "Images". Row 1 is assumed to have headers, so the macro starts with cell "A2".

Sub InsertPics()

Dim Cell As Range
Dim PicPath As String
Dim Pic As Excel.Picture
Dim Rng As Range
Dim RngEnd As Range
Dim WSH As Object

Set Rng = Range("A2")
Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd))

Set WSH = CreateObject("WScript.Shell")
PicPath = WSH.SpecialFolders("MyDocuments") & "\Images\"

Application.ScreenUpdating = False

For Each Cell In Rng
If Dir(PicPath & Cell) <> "" Then
Set Pic = ActiveSheet.Pictures.Insert(PicPath & Cell)
With Pic
.Left = Cell.Offset(0, 1).Left
.Top = Cell.Offset(0, 1).Top
.Width = Cell.Offset(0, 1).Width
.Height = Cell.Offset(0, 1).Height
End With
End If
Next Cell

Application.ScreenUpdating = True

Set WSH = Nothing

End Sub

tobuvn · 20/6/14

Hay quá, cảm ơn pro!

Cho em hỏi một chút là em muốn thay đổi drop-drown mã số thành mình tự gõ thì làm như thế nào ạ? Em thử nhưng chưa được ạ. Khi em án Alt + f11 ra code nhưng không thấy phần định dạng ảnh (vị trí, kích thước) ở chỗ nào ạ. Em mới vào nghề mong bác @sealand và các bác giúp đỡ ạ!

sealand · 20/6/14

Bạn hoàn toàn có thể tự gõ được chứ. Chỉ có điều phải lưu ý: Đối với 1 ô Excel ở dạng General bạn gõ 0003 lập tức nó hiểu bạn nhập số và chuyển thành 3. Vậy là không đúng với Validation rồi. Có 2 cách sử lý trường hợp này:

1/ Gõ dấu nháy " ' " kèm theo 0003

2/Định dạng ô đó là Text

Riêng về kích cỡ bạn là như sau: Vì đây là Frame ảnh và ta định dạng nó là tự động giãn đầy nên dù ảnh to nhỏ hay vuông, chữ nhật tự động giãn đầy. Để tham khảo bạn làm như sau:

Vào Menu View--Toolbars---Control Toolbox (Chọn)
Trên Control Toolbox nhấn hình E ke
Nhấn chuột lên hình sẽ co giãn được hình. Nhấn chuột phải chọn Properties sẽ vào thiết lập cho hình.

Mạnh dạn vọc đi xem sao.

tobuvn · 20/6/14

Cháu cảm ơn chú @sealand, cháu quên mất format cell ạ!
Nói thật với chú cháu là giáo viên và chưa học VBA bao gờ ạ, mọi cái với cháu chỉ là xử lý với một số hàm đơn giản. Cháu mới mở cửa hàng nên rất cần ứng dụng này, cháu mong chú giúp cháu kết nối file excel tự động với ảnh như trên chú đã làm với ạ. Những thông tin khác thì cháu vọc, nghiên cứu trên mạng thì cháu đã làm xong hết rồi ạ. Cháu cảm ơn chú @sealand!

sealand · 20/6/14

Việc load ảnh dựa vào Code sau:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$21" Then
Sheet1.Image1.Picture = LoadPicture(ThisWorkbook.Path & "\" & Target.Value & ".jpg")
End If
End Sub

Bạn xem trong file Convert nha, mình dùng Excel 2003 thôi.

tobuvn · 20/6/14

Vâng, cháu dùng thấy tốt lắm rồi chú ạ. Cháu cảm ơn chú rất nhiều ạ!
Chú ơi, cháu chèn thêm một đối tượng ảnh nữa làm mã vạch thì bị lỗi chú ạ. Bình thường cháu làm thủ công phát cho học sinh là thẻ có mã vạch.
Cháu copy thêm một ảnh image1 và đổi tên thành image2, sau đó cháu paste tiếp code cho image2. Trong code cháu đổi image1 thành image2, .jpg thành .png nhưng bị lỗi chú ạ. Bình thường cháu cứ tưởng như mọi lần mọi người chỉ cần hướng dẫn cho một cái, cái còn lại cháu có thể hoàn thành, nhưng không có kiến thức về VBA cháu lúng túng quá ạ.
http://www.mediafire.com/download/kcxgda65ed8so2t/GUI_CHU.rar

siwtom · 20/6/14

tobuvn đã viết:
Vâng, cháu dùng thấy tốt lắm rồi chú ạ. Cháu cảm ơn chú rất nhiều ạ!
Chú ơi, cháu chèn thêm một đối tượng ảnh nữa làm mã vạch thì bị lỗi chú ạ. Bình thường cháu làm thủ công phát cho học sinh là thẻ có mã vạch.
Cháu copy thêm một ảnh image1 và đổi tên thành image2, sau đó cháu paste tiếp code cho image2. Trong code cháu đổi image1 thành image2, .jpg thành .png nhưng bị lỗi chú ạ. Bình thường cháu cứ tưởng như mọi lần mọi người chỉ cần hướng dẫn cho một cái, cái còn lại cháu có thể hoàn thành, nhưng không có kiến thức về VBA cháu lúng túng quá ạ.
http://www.mediafire.com/download/kcxgda65ed8so2t/GUI_CHU.rar

Nếu vì lý do nào đấy không thể dùng code (cơ quan cấm?) hoặc không muốn đính kèm thư mục ảnh khi chia sẻ thì bạn cũng có thể thêm sheet mới --> Insert bằng tay ảnh vào sheet mới --> liên kết ô mã/tên với ảnh trên sheet mới.

tobuvn · 20/6/14

Nhưng số lượng khác hàng của em cả hàng trăm người, có tương lai tới cả nghìn người nên nếu chèn vào trong một file sẽ tạo ra một file có dung lượng quá lớn bác @siwtom ạ! Vấn đề của em chỉ còn một chút nữa thôi mà em chưa giải quyết được. Mong các bác chỉ giáo ạ!

sealand · 20/6/14

Có mấy vấn đề lưu ý:

1/ Em lưu ý file *.png không phải dạng file ảnh có thể load lên Image được. Để load em dùng phần mềm nào đó (Snagit chẳng hạn) convert sang dạng file *.jpg để có thể load được.

2/Code load ảnh em viết như sau:

Mã:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$25" Then
Application.ScreenUpdating = False
Sheet1.Image1.Picture = LoadPicture(ThisWorkbook.Path & "\" & Target.Value & ".jpg")
Sheet1.Image2.Picture = LoadPicture(ThisWorkbook.Path & "\" & Target.Value & "a.jpg")
End If
End Sub

3/ Về mã vạch: Không mấy ai dùng dạng ảnh như em vì nó mờ nên đầu đọc mã vạch sẽ đọc không chính xác và báo lỗi loạn cả lên.
Em tải font mã vạch trên mạng về và format chuỗi mã HS theo font đó là có mã vạch nét như "SONY"
Sau này khi mượn thuê hay trả sách em quét 1 nhát là dữ liệu tự động cập nhật khỏi cần gõ tay mất công

Tải và cài đặt font mã vạch có thể tham khảo ở đây:http://www.cds.vn/index.aspx?action=LTK&IDCM=101&id_ntc=101&menusub=135&i=6&ic=5&lan=1

tobuvn · 21/6/14

Tuyệt vời quá ạ. Cháu cảm ơn chú đã giúp đỡ cháu rất nhiều ạ!

vuongtoituonglai · 21/6/14

Insert Picture Vào Excel

sealand đã viết:
Bạn xem có phải thế này không? (Lưu ý bạn phải giải nén ra máy mới chạy được vì lúc đó mới có địa chỉ của ảnh) Cái ví dụ của mình như vậy chắc bạn biết cách đưa nó vào nơi bạn muốn

Bài #3 của bạn Sealand hay quá mình cũng đang cần làm về vấn đề này nhờ bạn giúp dùm. File của mình hơi khác một tí bạn xem file giúp mình nhé. Về hình thì mình sử dụng 2 dạng đuôi jpg và bmp. sheet Data của mình rất nhiều và file hình cũng rất nhiều hình gần 10.000 hình nên mình đã bỏ bớt. Bạn xem file nhé.

ndu96081631 · 21/6/14

vuongtoituonglai đã viết:
Bài #3 của bạn Sealand hay quá mình cũng đang cần làm về vấn đề này nhờ bạn giúp dùm. File của mình hơi khác một tí bạn xem file giúp mình nhé. Về hình thì mình sử dụng 2 dạng đuôi jpg và bmp. sheet Data của mình rất nhiều và file hình cũng rất nhiều hình gần 10.000 hình nên mình đã bỏ bớt. Bạn xem file nhé.

Tặng bạn code này:

Mã:

Function CommPic(ByVal Pic As String, Optional ByVal Cel As Range) As String
  Dim mRng As Range, comm As Comment
  On Error Resume Next
  Application.Volatile
  If Cel Is Nothing Then Set Cel = Application.ThisCell
  Cel(1, 1).Comment.Delete
  If Not CreateObject("Scripting.FileSystemObject").FileExists(Pic) Then
    Pic = ThisWorkbook.Path & "\" & Pic
  End If
  If CreateObject("Scripting.FileSystemObject").FileExists(Pic) Then
    If Cel(1, 1).Comment Is Nothing Then Cel(1, 1).AddComment
    Cel(1, 1).Comment.Text vbLf
    Set mRng = Cel(1, 1).MergeArea
    If mRng Is Nothing Then Set mRng = Cel(1, 1)
    Set comm = mRng(1, 1).Comment
    comm.Visible = True
    With comm.Shape
      .LockAspectRatio = msoFalse
      .Placement = xlMoveAndSize
      .Shadow.Visible = msoFalse
      .Line.Visible = msoFalse
      .AutoShapeType = msoShapeRectangle
      .Left = mRng.Left: .Top = mRng.Top
      .Width = mRng.Width: .Height = mRng.Height
      .Fill.UserPicture Pic
    End With
  End If
End Function

Việc của bạn là:
- Cho code trên vào 1 Module
- Xong, ra ngoài bảng tính, gõ vào cell C28 công thức =Commpic(C27&".jpg")
Vậy là xong

vuongtoituonglai · 22/6/14

ndu96081631 đã viết:

Tặng bạn code này:

Mã:

Function CommPic(ByVal Pic As String, Optional ByVal Cel As Range) As String
  Dim mRng As Range, comm As Comment
  On Error Resume Next
  Application.Volatile
  If Cel Is Nothing Then Set Cel = Application.ThisCell
  Cel(1, 1).Comment.Delete
  If Not CreateObject("Scripting.FileSystemObject").FileExists(Pic) Then
    Pic = ThisWorkbook.Path & "\" & Pic
  End If
  If CreateObject("Scripting.FileSystemObject").FileExists(Pic) Then
    If Cel(1, 1).Comment Is Nothing Then Cel(1, 1).AddComment
    Cel(1, 1).Comment.Text vbLf
    Set mRng = Cel(1, 1).MergeArea
    If mRng Is Nothing Then Set mRng = Cel(1, 1)
    Set comm = mRng(1, 1).Comment
    comm.Visible = True
    With comm.Shape
      .LockAspectRatio = msoFalse
      .Placement = xlMoveAndSize
      .Shadow.Visible = msoFalse
      .Line.Visible = msoFalse
      .AutoShapeType = msoShapeRectangle
      .Left = mRng.Left: .Top = mRng.Top
      .Width = mRng.Width: .Height = mRng.Height
      .Fill.UserPicture Pic
    End With
  End If
End Function

Việc của bạn là:
- Cho code trên vào 1 Module
- Xong, ra ngoài bảng tính, gõ vào cell C28 công thức =Commpic(C27&".jpg")
Vậy là xong

Dạ, cảm ơn thầy ndu96081361 nhiều nhưng em có nêu vấn đề là file hình của em có 2 dạng đuôi là jpg và bmp với công thức thầy cho thì những hình có đuôi bmp sẽ không lấy vào được thầy à, thầy xem lại dùm em với. Thầy chỉnh cho hình nằm gọn trong ô nhưng vẫn còn nhìn thấy phần Border, khi nhập công thức vào và kéo các ô thì phần Border bị mất thầy ơi Thầy xem lại dùm em luôn nhé. Em cảm ơn Thầy nhiều.

ndu96081631 · 22/6/14

vuongtoituonglai đã viết:
Dkhi nhập công thức vào và kéo các ô thì phần Border bị mất thầy ơi Thầy xem lại dùm em luôn nhé

Mấy vụ format linh tinh gì đó thì tôi không biết đâu, bạn có thể tự làm bằng tay (vì dù là công thức nào thì vẫn phải làm vậy thôi)

vuongtoituonglai đã viết:
Dạ, cảm ơn thầy ndu96081361 nhiều nhưng em có nêu vấn đề là file hình của em có 2 dạng đuôi là jpg và bmp với công thức thầy cho thì những hình có đuôi bmp sẽ không lấy vào được thầy à.

Hàm tôi viết là để cho nhiều người xài, mang tính tổng quát. Nếu sửa lại theo ý bạn thì... mình bạn xài và chẳng ai xài được (vậy nên tôi không có hứng thú)
Bạn tìm cách khác vậy!

siwtom · 22/6/14

vuongtoituonglai đã viết:
Bài #3 của bạn Sealand hay quá mình cũng đang cần làm về vấn đề này nhờ bạn giúp dùm. File của mình hơi khác một tí bạn xem file giúp mình nhé. Về hình thì mình sử dụng 2 dạng đuôi jpg và bmp. sheet Data của mình rất nhiều và file hình cũng rất nhiều hình gần 10.000 hình nên mình đã bỏ bớt. Bạn xem file nhé.

Có nhiều cách tạo ảnh. Làm bằng tay thì vd. dùng Insert. Dùng code cũng có nhiều kiểu tạo. Vậy cái mà ta muốn không phải là tạo kiểu gì, vì tha hồ mà lựa chọn. Cái ta muốn là không phải làm bằng tay. Là làm sao để Excel tự gọi ta để ta làm cái việc tạo ảnh. Một trong những cách để Excel gọi ta là viết hàm tự tạo và dùng hàm đó trong công thức. Như vậy thì Excel phải gọi ta.
Hàm không chỉ làm việc tính toán, thậm chí code của hàm không bắt buộc phải tính toán. Code của hàm có thể chỉ mở tập tin trên đĩa rồi đóng lại để giết thời gian. Có thể kết nối mạng. Có thể làm bất cứ việc gì, tất nhiên là cả việc tạo ảnh và định vị nó.

Vậy thì ta viết hàm và code của hàm chả tính toán gì cả, trả về kết quả ở dạng chuỗi rỗng. Nhưng code sẽ làm việc tạo ảnh.

Tóm lại là ta viết hàm và dùng nó trong công thức. Lúc đó Excel sẽ tự gọi hàm của ta. Code hàm của ta chỉ chờ cơ hội đó để tạo và định vị ảnh. Thế thôi.

Mã:

Function InsertPic(ByVal picname As String) As String
Dim pic As Shape, cell_ As range
    Set cell_ = Application.ThisCell
    On Error Resume Next
    cell_.Parent.Shapes(cell_.Address).Delete
    On Error GoTo 0
    Set pic = cell_.Parent.Shapes.AddPicture(ThisWorkbook.Path & "\" & picname, _
        msoFalse, msoTrue, cell_.left, cell_.top, cell_.Width, cell_.Height)
    pic.LockAspectRatio = msoTrue
    pic.Name = cell_.Address
    Set cell_ = Nothing
End Function

Có hàm rồi thì phải dùng nó trong công thức để Excel tự gọi nó.

Vậy công thức có thể là

Mã:

=InsertPic(C27&".bmp")

và cũng có thể là

Mã:

="Đây là hoa hậu lớp 10A"&InsertPic(C27&".bmp")

Công thức thế nào cũng được nhưng phải dùng hàm trong công thức để Excel gọi nó.

vuongtoituonglai · 22/6/14

siwtom đã viết:
Có nhiều cách tạo ảnh. Làm bằng tay thì vd. dùng Insert. Dùng code cũng có nhiều kiểu tạo. Vậy cái mà ta muốn không phải là tạo kiểu gì, vì tha hồ mà lựa chọn. Cái ta muốn là không phải làm bằng tay. Là làm sao để Excel tự gọi ta để ta làm cái việc tạo ảnh. Một trong những cách để Excel gọi ta là viết hàm tự tạo và dùng hàm đó trong công thức. Như vậy thì Excel phải gọi ta.
Hàm không chỉ làm việc tính toán, thậm chí code của hàm không bắt buộc phải tính toán. Code của hàm có thể chỉ mở tập tin trên đĩa rồi đóng lại để giết thời gian. Có thể kết nối mạng. Có thể làm bất cứ việc gì, tất nhiên là cả việc tạo ảnh và định vị nó.

Vậy thì ta viết hàm và code của hàm chả tính toán gì cả, trả về kết quả ở dạng chuỗi rỗng. Nhưng code sẽ làm việc tạo ảnh.

Tóm lại là ta viết hàm và dùng nó trong công thức. Lúc đó Excel sẽ tự gọi hàm của ta. Code hàm của ta chỉ chờ cơ hội đó để tạo và định vị ảnh. Thế thôi.

Mã:

Function InsertPic(ByVal picname As String) As String Dim pic As Shape, cell_ As range Set cell_ = Application.ThisCell On Error Resume Next cell_.Parent.Shapes(cell_.Address).Delete On Error GoTo 0 Set pic = cell_.Parent.Shapes.AddPicture(ThisWorkbook.Path & "\" & picname, _ msoFalse, msoTrue, cell_.left, cell_.top, cell_.Width, cell_.Height) pic.LockAspectRatio = msoTrue pic.Name = cell_.Address Set cell_ = Nothing End Function

Có hàm rồi thì phải dùng nó trong công thức để Excel tự gọi nó.

Vậy công thức có thể là

Mã:

=InsertPic(C27&".bmp")

và cũng có thể là

Mã:

="Đây là hoa hậu lớp 10A"&InsertPic(C27&".bmp")

Công thức thế nào cũng được nhưng phải dùng hàm trong công thức để Excel gọi nó.

Cảm ơn bạn nhé, hình của mình có khi đuôi .jpg hoặc bmp có công thức nào phù hợp với cả 2 đuôi hình này không bạn. file hình của mình do một bộ phận khác cung cấp khi họ dùng đuôi này lúc thì họ dùng đuôi kia. mình đang bối rối quá.

ndu96081631 · 22/6/14

vuongtoituonglai đã viết:
Cảm ơn bạn nhé, hình của mình có khi đuôi .jpg hoặc bmp có công thức nào phù hợp với cả 2 đuôi hình này không bạn. file hình của mình do một bộ phận khác cung cấp khi họ dùng đuôi này lúc thì họ dùng đuôi kia. mình đang bối rối quá.

Cách đơn giản nhất: Convert toàn bộ bmp sang jpg (hoặc ngược lại)
Chuyện dễ như ăn khoai. Dùng phần mềm sẽ cho kết quả trong vài giây

Trợ giúp về hàm auto insert picture (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Bad Excel Member

Thành viên gạo cội

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên mới

File đính kèm

Thành viên gạo cội

File đính kèm

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên gạo cội

File đính kèm

Thành viên mới

Thành viên thường trực

File đính kèm

Huyền thoại GPE

Thành viên thường trực

Huyền thoại GPE

Thành viên gắn bó

Thành viên thường trực

Huyền thoại GPE

Đếm ngược thời gian