¡¡
¡¡
¡¡
|
 |
 |
|
 |
 |
|
¡¡ |
¡¡ |
¡¡ |
¡¡ |
¡¡ * ´äº¯ÇϽô ºÐµé²² µµ¿òÀÌ µÇµµ·Ï ÀÚ½ÅÀÇ È¯°æÀ» ¾Æ·¡ Ç׸ñ ¿·¿¡ ±âÀçÇØ ÁÖ¼¼¿ä.
- ¿¢¼¿ ¹öÀü(95,97,2000,xp,2003,2007):
* ¾Æ·¡ÁÙ¿¡ Áú¹®À» ÀÛ¼ºÇϼ¼¿ä >>
À̸®Àú¸® ÇØºÃ´Âµ¥µµ ¾È µÅ¼ ´Ù½Ã ÇÑ ¹ø Áú¹® µå¸³´Ï´Ù¤Ð¤Ð
8ÀÏ ÁÖ±â·Î ¹Ýº¹µÇ´Â ÀÏÁ¤("¾È")Àº
¼öÁ¤ÇØÁֽŠVBA·Î À̹ÌÁö("¾È.jpg") ºÒ·¯¿À±â°¡ Àß µË´Ï´Ù.
±×·±µ¥ ¸íÀý°ú °°ÀÌ ºñÁÖ±âÀûÀÎ ±â³äÀÏÀ» Ç¥½ÃÇϱâ À§ÇØ
=IF(COUNTIF(±â³äÀÏ!D100:D111,DATE(YEAR(AA22),MONTH(AA22),DAY(AA22)))>0,"¸í","")
ÀÌ·¯ÇÑ ÇÔ¼ö¸¦ ÅëÇØ "¸í"ÀÌ ¶ßµµ·Ï ¼³Á¤Çسõ¾Ò´Âµ¥,
Æú´õ¿¡ "¸í.jpg" ÆÄÀÏÀÌ À־,
"¸í.bmp" ¶Ç´Â "¸í.png" ÆÄÀÏÀÌ À־
À̹ÌÁö°¡ ºÒ·¯¿ÍÁöÁö ¾Ê°í,
"¸í" ÀÌ ¾Æ´Ñ ´Ù¸¥ ÅØ½ºÆ®·Î ½ÃµµÇغÁµµ ¾È µÇ´õ¶ó±¸¿ä...
±×·±µ¥ ÇÔ¼ö¸¦ ´Ù Áö¿ì°í
"¸í"ÀÚ¸¸ ½èÀ» °æ¿ì¿¡´Â VBA ¸¦ ½ÇÇàÇßÀ» ¶§,
"¸í.jpg" ¸¸ ºÒ·¯¿ÍÁö°í ("¾È.jpg"´Â ºÒ·¯¿ÀÁö ¾ÊÀ½)
91 ·±Å¸ÀÓ ¿À·ù ¸Þ½ÃÁö°¡ ¶å´Ï´Ù.
"°³Ã¼ º¯¼ö ¶Ç´Â With ¹®ÀÇ º¯¼ö°¡ ¼³Á¤µÇ¾î ÀÖÁö ¾Ê½À´Ï´Ù"
±×¸®°í µð¹ö±×¸¦ ´©¸£¸é
Loop While Not C Is Nothing And C.Address <> sAddr
ÀÌ ºÎºÐÀÌ ³ë¶õ»öÀ¸·Î Ç¥½Ã°¡ µÅ¿ä.
Ȥ½Ã Áö±Ý »ç¿ëÇϰí ÀÖ´Â VBA°¡
ÇϳªÀÇ ÆÄÀϸ¸ ºÒ·¯¿Àµµ·Ï ¼³Á¤µÇ¾îÀÖ´Â °Ç°¡¿ä?
(¿¹¸¦ µé¾î, "¾È"°ú "¸í" ÀÌ ÀÖÀ» ¶§, À̸§ ±âÁØ ¿À¸§Â÷¼ø Á¤·Ä½Ã
¾Õ¿¡ ¿À´Â "¸í" À̹ÌÁö¸¸ ºÒ·¯¿Àµµ·Ï ÇÏ´Â °æ¿ì)
ÀÎÅÍ³Ý Ã£¾Æº¸¸é¼ ¿©·¯¸ð·Î ½ÃµµÇغôµ¥,
µµÀúÈ÷ ÇØ´äÀ» ¾òÀ» ¼ö°¡ ¾ø¾î¼ ´Ù½Ã Áú¹®µå¸³´Ï´Ù¤Ð¤Ð
´Ù½Ã ÇÑ ¹ø µµ¿òÀ» ºÎʵ右´Ï´Ù...¤Ð¤Ð
¼öÁ¤ÇØÁ̴ּø VBA ÀÔ´Ï´Ù.
==============================================
Sub insert_Pictures_Matching_Name()
Dim fileName As String '°¢ ÆÄÀÏ À̸§À» ³ÖÀ» º¯¼ö
Dim strPath As String 'Æú´õÀÇ °æ·Î¸¦ ³ÖÀ» º¯¼ö
Dim C As Range '°Ë»ö¿¡ ÀÏÄ¡ÇÑ ¼¿À» ³ÖÀ» º¯¼ö
Dim strName As String 'ÆÄÀÏ È®ÀåÀÚ Á¦¿ÜÇÑ À̸§À» ³ÖÀ» º¯¼ö
' ------- ÀÓ½ÃÁÖ¼Ò¸¦ ³ÖÀ» º¯¼ö
Dim sAddr As String
Application.ScreenUpdating = False 'È¸é ¾÷µ¥ÀÌÆ® (ÀϽÃ) Á¤Áö
With Application.FileDialog(msoFileDialogFolderPicker) 'Æú´õ¼±Åà â¿¡¼
.Show 'Æú´õ ¼±ÅÃâ ¶ç¿ì±â
If .SelectedItems.Count = 0 Then 'Ãë¼Ò ¼±Åà ½Ã
Exit Sub '¸ÅÅ©·Î Áß´Ü
Else
strPath = .SelectedItems(1) & "\" 'Æú´õ °æ·Î¸¦ º¯¼ö¿¡ ³ÖÀ½
End If
End With
ActiveSheet.Pictures.Delete '±âÁ¸ »çÁøµé »èÁ¦
fileName = Dir(strPath) '(Æú´õ³»)°¢ ±×¸²ÆÄÀÏ À̸§À» º¯¼ö¿¡ ³ÖÀ½
If fileName = "" Then 'Æú´õ¿¡ ÆÄÀÏÀÌ ¾øÀ¸¸é
MsgBox "Æú´õ¿¡ ÆÄÀÏÀÌ ¾ø½À´Ï´Ù." '¸Þ½ÃÁö Ãâ·Â
Exit Sub '¸ÅÅ©·Î Áß´Ü
End If
Do While fileName <> "" 'À̸§ÀÌ ¾øÁö ¾Ê´Ù¸é, Áï, ÆÄÀÏÀÌ Á¸ÀçÇϸé
strName = Split(fileName, ".")(0) 'ÆÄÀÏ È®ÀåÀÚ Á¦°ÅÇÑ À̸§ ÃßÃâ
' ------- Find ¸Þ¼Òµå º¯¼ö Ãß°¡ ¹× ±¸¹® ¼öÁ¤
Set C = ActiveSheet.UsedRange.Find(strName, , xlValues, xlWhole) '±×¸²ÆÄÀϰú ÀÏÄ¡ÇÏ´Â ¼¿À» ãÀ½
If Not C Is Nothing Then '±×¸²ÆÄÀϰú µ¿ÀÏÇÑ À̸§ÀÌ ¼¿¿¡ Á¸ÀçÇϸé
sAddr = C.Address
Do
ActiveSheet.Pictures.Insert(strPath & fileName).Select '°¢ ±×¸²ÆÄÀÏ »ðÀÔ
'Set C = C.Next.MergeArea '¼¿º´ÇÕ ¼¿À» C¿¡ ³ÖÀ½
With Selection '¼±ÅÃµÈ ±×¸²ÆÄÀÏ
.Name = "Temp" 'º¹»çµÈ »çÁøÀÇ À̸§À» º¯°æ
.ShapeRange.LockAspectRatio = msoFalse '±×¸²ÀÇ °¡·Î/¼¼·ÎºñÀ² °íÁ¤ ÇØÁ¦
.Height = C.Resize(2, 2).Height '±×¸²ÀÇ °¡·ÎÅ©±â ÁöÁ¤
.Width = C.Resize(2, 2).Width '±×¸²ÀÇ ¼¼·ÎÅ©±â ÁöÁ¤
.Copy '±×¸²À» º¹»ç
ActiveSheet.PasteSpecial Link:=False
'±×¸² ¸µÅ©±ú°í ºÙ¿©³Ö±â
ActiveSheet.Pictures("Temp").Delete '¿øº» ±×¸²ÆÄÀÏ »èÁ¦
End With
With Selection '(º¹»çµÇ¾î)¼±ÅÃµÈ ±×¸²ÆÄÀÏ
.Left = C.Left '±×¸²ÀÇ ¿ÞÂÊÀ§Ä¡ ÁöÁ¤
.Top = C.Top '±×¸²ÀÇ ÀÂÊÀ§Ä¡ ÁöÁ¤
End With
Set C = ActiveSheet.UsedRange.FindNext(C)
Loop While Not C Is Nothing And C.Address <> sAddr
End If
fileName = Dir '´ÙÀ½ ÆÄÀÏÀ» ÆÄÀÏÀ̸§¿¡ ³ÖÀ½
Loop '¹«ÇÑ ¹Ýº¹
End Sub
|
¡¡ |
[ºÒ·® °Ô½Ã¹° ½Å°í]
|
|
|
 |
ÀÛ¼ºÀÏ : 2018-10-11(13:58)
ÃÖÁ¾¼öÁ¤ÀÏ : 2018-10-11(13:58) |
¡¡ |
|
 |
|
|
 |