OfficeTutor.com
¹è¿òÅÍ ³ª´®ÅÍ ±³À°¼¾ÅÍ ¿ÀÆ©ÀåÅÍ
 ÅëÇÕ°Ë»ö  
¡¡ Home > ³ª´®ÅÍ > ¿¢¼¿ > ¹¯°í´äÇϱâ
¿¢¼¿
¿öµå
¾×¼¼½º
ÆÄ¿öÆ÷ÀÎÆ®
¾Æ¿ô·è
ÇÁ·±Æ®ÆäÀÌÁö
ÀÎÆ÷ÆÐ½º
¿ø³ëÆ®
ºñÁö¿À
½¦¾îÆ÷ÀÎÆ®
Æ÷Åäµå·Î¿ì
ÆÛºí¸®¼Å
¿ÀÇǽº°øÅë
MVP ¾È³»
MVP ¸í¿¹ÀÇ Àü´ç
MVP Ä«Æä

¡¡

¡¡

¡¡

¡¡
¿¢¼¿¿¡ ´ëÇÑ Áú¹®°ú ´äº¯À» ¿Ã·ÁÁÖ¼¼¿ä. ´Ü, ÃëÁö¿¡ ¸ÂÁö ¾Ê´Â ±ÛÀº ¿î¿µÀÚ°¡ »èÁ¦ÇÕ´Ï´Ù.
 "000 ´Ô, µµ¿ÍÁÖ¼¼¿ä", "ºÎŹ µå¸³´Ï´Ù.", "±ÞÇÕ´Ï´Ù!" µî°ú °°ÀÌ ¸·¿¬ÇÑ Á¦¸ñÀ» ´ÞÁö ¸»¾ÆÁÖ¼¼¿ä.
[Çʵ¶] ºü¸£°í Á¤È®ÇÑ ´äº¯À» ¾ò´Â 16°¡Áö Tip !
[Çʵ¶] ÀúÀ۱ǹý °³Á¤¿¡ µû¸¥ ÀÌ¿ë¾È³»
ÀÛ¼ºÀÚ:  

 ÀÌÁ¤Çý (id1916)

Ãßõ:  1
ÆÄÀÏ:     Á¶È¸:  134
Á¦¸ñ:   [RE]À̹ÌÁö ÀÚµ¿ ºÒ·¯¿À±â VBA ½ÇÇà ¹®Á¦
¡¡ ¡¡ ¡¡
¡¡
¡¡ * ´äº¯ÇϽô ºÐµé²² µµ¿òÀÌ µÇµµ·Ï ÀÚ½ÅÀǠȯ°æÀ» ¾Æ·¡ Ç׸ñ ¿·¿¡ ±âÀçÇØ ÁÖ¼¼¿ä.

  - ¿¢¼¿ ¹öÀü(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)
¡¡


 ¡Ý °ü·Ã±Û

  Á¦ ¸ñ   ÀÛ¼ºÀÚ   ³¯Â¥
¡Ý [RE]À̹ÌÁö ÀÚµ¿ ºÒ·¯¿À±â VBA ½ÇÇà ¹®Á¦ ÀÌÁ¤Çý 2018-10-11
[RE][RE]À̹ÌÁö ÀÚµ¿ ºÒ·¯¿À±â VBA ½ÇÇà ¹®Á¦ dew 2018-10-11

 
 
ȸ»ç¼Ò°³ | ÀÌ¿ë¾à°ü | °³ÀÎÁ¤º¸ 󸮹æÄ§ | ȸ¿øÁ¤º¸¼öÁ¤ | ±³À°½Åû ¹× ¹®ÀÇ | À̸ÞÀϹ«´Ü¼öÁý°ÅºÎ À§·Î

¿ÀÇǽºÆ©ÅÍ ºí·Î±× ¿ÀÇǽºÆ©ÅÍÆäÀ̽ººÏ ¿ÀÇǽºÆ©ÅÍÀ¯Åõºê
110-722 ¼­¿ï Á¾·Î±¸ ¼¼Á¾´ë·Î23±æ 47, 601-74 ¢ß¿ÀÇǽºÆ©ÅÍ ¢Ï1544-4102 ´ëÇ¥ÀÌ»ç : Àü°æ¼ö
°³ÀÎÁ¤º¸°ü¸®Ã¥ÀÓÀÚ : ÀÌÈñÁø(privacy@officetutor.com) »ç¾÷ÀÚµî·Ï¹øÈ£ 120-86-14501
Åë½ÅÆÇ¸Å¾÷½Å°í 2013-¼­¿ïÁ¾·Î-0547
norton
Copyright ¨Ï OfficeTutor.com ÀÌ ÆäÀÌÁöÀÇ ÀúÀÛ±ÇÀº ¢ß¿ÀÇǽºÆ©ÅÍ¿Í ÄÜÅÙÃ÷ Á¦°øÀÚ¿¡°Ô ÀÖ½À´Ï´Ù. ¹«´Ü º¹Á¦¸¦ ±ÝÇÕ´Ï´Ù.
Microsoft ¹× Office ·Î°í´Â ¹Ì±¹, ´ëÇѹα¹ ¹×/¶Ç´Â ±âŸ ±¹°¡¿¡¼­ÀÇ Microsoft Corporation µî·Ï »óÇ¥ ¶Ç´Â »óÇ¥ÀÔ´Ï´Ù.