ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¨Ï¥ÎVBA¸óÀɮקì¨ú¦h­Ó¤u§@ªí¸Ìªº¸ê®Æ

[µo°Ý] ¨Ï¥ÎVBA¸óÀɮקì¨ú¦h­Ó¤u§@ªí¸Ìªº¸ê®Æ

¥»©«³Ì«á¥Ñ ABK ©ó 2018-9-2 18:16 ½s¿è

½Ð±Ð¦U¦ì¤j¤j:

§Ú­n¦b²Ä¤@­ÓExcelªº¤u§@ªí¤W §ì¨ú²Ä¤G­ÓExcelÀɮ׸̦h­Ó¤£¦P¤u§@ªí¸Ì­±ªº¸ê®Æ¡A
²Ä¤@­ÓÀÉ"¤T¼t¥Í²£¤é³ø" ©ñ¦bC:/Users/Golden/Desktop/
²Ä¤G­ÓÀÉ"¤T¼t¦U¾÷¥Í²£¬ö¿ý" ©ñ¦bD:/¥Í²£¤é³ø/

¥Í²£¥N½X¬O°ß¤@ªº¡A¤£·|­«½Æ¡C
·Q¥ÎVBAÅý²Ä¤@­ÓÀÉ"¤T¼t¥Í²£¤é³ø"¥H ¥Í²£¥N½X ·j´M ²Ä¤G­ÓÀÉ"¤T¼t¦U¾÷¥Í²£¬ö¿ý" A1¡BA2¡BA3¡BB1¡BB2¡BC1¡BC2 ¤u§@ªí¸Ì¬Û¦P¥Í²£¥N½Xªº §ë²£¼Æ¶q ¬ö¿ý¨ì²Ä¤@­ÓÀÉ"¤T¼t¥Í²£¤é³ø"¸Ì¡C
¸óÀɧì¸ê®Æ¬ö¿ý.jpg

¥Í²£¤é³ø-¸óÀɧì¸ê®Æ.rar (25.77 KB)

¦^´_ 18# n7822123


©Ó¤W¤@¼Ó¡AÀɮצp¤U
¥Í²£ºÞ²z½d¨Ò20180929.rar (30.48 KB)

¦pªG¤ß¦å¨Ó¼é·Q­n§ï¬°Ãö³¬¸ê®ÆÀɮ׮ɡA½Ð±Ò¥Î¤U¤@¦æµ{¦¡½X
¸ê®ÆÀÉ.Close Not read


Á`µ{¦¡½X¦p¤U:
  1. Sub ¬d¸ß§ë²£¼Æ¶q()
  2. '«Å§iÅܼÆ
  3. Dim ÀɦW$, ¸ô®|ÀɦW$, tt$, R&
  4. Dim read As Boolean
  5. Application.ScreenUpdating = False '¿Ã¹õ§Y®É§ó·sÃö³¬
  6. Set Dy = CreateObject("scripting.dictionary")  '³]Dy¬°¦r¨åª«¥ó
  7. Path = ThisWorkbook.Path  '§ì¨ú¥»Àɮ׸ô®|
  8. '©R¦W¦¹¤u§@ªí¬° "­n¶ñªºªí"
  9. Set ­n¶ñªºªí = ThisWorkbook.Sheets("2018¤T¼t¾÷¥x¥Í²£°lÂÜ")
  10. '¦pªG[G5]¦³¸ê®Æ´N¨Ì[G5]¸ô®|ªºÀɮסA¦pªG¨S¨ì´N§ä¦P¸ô®|¤Uªº¥t¤@­ÓexcelÀÉ
  11. If [G5] <> "" Then
  12.   ¸ô®|ÀɦW = [G5]
  13.   ÀɦW = Right(¸ô®|ÀɦW, Len(¸ô®|ÀɦW) - InStrRev(¸ô®|ÀɦW, "\"))
  14.   If Dir(¸ô®|ÀɦW) = "" Then MsgBox "¨Ì[G5]¿é¤Jªº¸ô®|»PÀɦW§ä¤£¨ìÀɮסA½ÐÀˬd¦³µL¿ù»~": Exit Sub
  15. Else
  16.   ÀɦW = Dir(Path & "\*.xls*")
  17.   If ÀɦW = ThisWorkbook.Name Then ÀɦW = Dir
  18.   ¸ô®|ÀɦW = Path & "\" & ÀɦW
  19. End If
  20. 'Àˬd¸ê®ÆÀɮ׬O§_¤w¶}±Ò
  21. For Each wb In Workbooks
  22.   If wb.Name = ÀɦW Then read = True
  23. Next
  24. 'Ãö³¬Åã¥Ü°T®§
  25. Application.DisplayAlerts = False
  26. '¥´¶}¸ê®ÆÀɮסA¨Ã¥B©R¦W¬°"¸ê®ÆÀÉ"
  27. Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW, , read)
  28. '³v¤@§â¤u§@ªíªº¥Í²£¥N½X»PÀY²£¼Æ¶q¿é¤J¨ì¦r¨åª«¥óDy¸Ì­±
  29. For Each ws In ¸ê®ÆÀÉ.Sheets
  30.   ws.Activate
  31.   If ws.[D1] <> "§ë²£¼Æ¶q" Then GoTo ¸õ¹L 'Àˬd¬O§_¬°­nªº¤u§@ªí
  32.   For R = 2 To ws.[A1].End(xlDown).Row
  33.     tt = Cells(R, 3): Dy(tt) = Cells(R, 4)
  34.   Next R
  35. ¸õ¹L:
  36. Next
  37. '±Ò¥Î­n¶ñªºªí
  38. ­n¶ñªºªí.Activate
  39. '³v¤@§â¦r¨åª«¥óDy¸Ì­±ªº­È¿é¤J¨ì¦¹¤u§@ªí(­n¶ñªºªí)
  40. For R = 2 To [A1].End(xlDown).Row
  41.   tt = Cells(R, 4)
  42.   Cells(R, 5) = Dy(tt)
  43. Next R
  44. '¤£¸õ¥X½T»{°T®§
  45. Application.DisplayAlerts = False
  46. '¦pªG¤ß¦å¨Ó¼é·Q­n§ï¬°Ãö³¬¸ê®ÆÀɮ׮ɡA½Ð±Ò¥Î¤U¤@¦æµ{¦¡½X
  47. '¸ê®ÆÀÉ.Close Not read
  48. '¦sÀÉÃö³¬+ÄÀ©ñ°O¾ÐÅé
  49. Set ¸ê®ÆÀÉ = Nothing
  50. Set Dy = Nothing
  51. '¿Ã¹õ§Y®É§ó·s¥´¶}
  52. Application.ScreenUpdating = True
  53. End Sub
½Æ»s¥N½X
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-9-29 15:03 ½s¿è

¦^´_ 17# ABK




«e¤@¬q®É¶¡¥X®t¡A¼ç¤ô¤F¤@¬q®É¶¡.........
¨ì²{¦b®É®tÁÙ¨S½Õ¾ã¦^¨Ó....
¤£¹L­ã¤j¤wÀ°§Ú¸Ñ¨M :D


»Ý¨D:
1.·í¸ê®ÆÀɵL¥ô¦ó¤H¶}±Ò®É¡A Åý¥DºÞ¥u¶}±Ò§ì¸ê®ÆªºÀÉ  °õ¦æ§ì¸ê®Æµ{¦¡®É¡A¸ê®ÆÀÉ·|¦Û¦æ¶}±Ò¨Ã°õ¦æ§ì¨ú¸ê®Æ¡A§¹¦¨«á¸ê®ÆÀɤ£·|¦Û¦æÃö³¬ (¥Ñ¥DºÞ¦Û¦æ¤â°ÊÃö³¬)

2.·í¦³¨ä¥L¥x¹q¸£¦b¨Ï¥Î¸ê®ÆÀÉ®É, ¥DºÞ¥u¶}±Ò§ì¸ê®ÆªºÀÉ  °õ¦æ§ì¸ê®Æµ{¦¡®É¡A¸ê®ÆÀɬO¥H°ßŪ¼Ò¦¡¶}±Ò«á§ì¨ú¸ê®Æ¡A¸ê®Æ§ì¨ú§¹¦¨«á¸ê®ÆÀÉ(°ßŪ¼Ò¦¡)¤£·|¦Û¦æÃö³¬ (¥Ñ¥DºÞ¦Û¦æ¤â°ÊÃö³¬)

¨ä¹ê¥u­n§ï§Ú­ì¥»ªº3¦æµ{¦¡½X§Y¥i(­Y­n«Å§i¡A«h¦h¤@¦æ)

«Å§i:
Dim read As Boolean

§ó§ï¦p¤U:
If wb.Name = ÀɦW Then MsgBox "¸ê®ÆÀɮ׶}±Ò¤¤¡A½ÐÃö³¬": Exit Sub
§ï¬°
If wb.Name = ÀɦW Then read=true

Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW)
§ï¬°
Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW,,read)

¸ê®ÆÀÉ.Close True > ¦¹¦æ§R°£
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 16# ­ã´£³¡ªL


    ÁÂÁ­ã³ö¤j !
   §Ú¨Ó¦n¦n¬ã¨s¤@¤U!   ·P®¦!  ·P®¦!

TOP

¤T¼t¥Í²£°O¿ý01.rar (18.49 KB)

xN = xN & ".xls"  °O±o§ï¦¨ xN = xN & ".xlsx"

TOP

¦^´_ 14# ­ã´£³¡ªL

ÁÂÁ­ã³ö¤j

1. ¼t§O/¾÷¥x/¤é´Á/¥Í²£¥N½X ³£¬O¨Æ¥ý¿é¤J
2. ¥u¨Ì¾Ú¥Í²£¥N½X §ì§ë²£¼Æ¶q
3. ¤W¦¸§ìÀɸê®Æ­n²M°£ (²£½u¦³¥i¯à­×¥¿Key¿ùªº¸ê®Æ¡A­«·s§ì¤@¦¸¤ñ¸û«OÀI)

¡° ¥Í²£¥N½X¬O°ß¤@ªº¡A ¤£·|­«½Æ¡C
¥Í²£¥N½X¬O°ß¤@ªº.jpg

¤T¼t¥Í²£¤é³ø.rar (13.98 KB)

¤T¼t¥Í²£°O¿ý.rar (20.62 KB)

TOP

¦^´_ 13# ABK

12¼Óªº°ÝÃDÁÙ¬O¨S¦³»¡©ú,
§ì¸ê®Æªº¨Ì¾Ú¬O¤°»ò??
®Ú¾Ú¨Æ¥ý¿é¤Jªº±ø¥ó§ì? ÁÙ¬O¦³¸ê®Æ¥þ§ì¶i¨Ó?
¤W¦¸ªº§ìÀɸê®Æ­n¤£­n²M°£?

TOP

¦^´_ 10# ­ã´£³¡ªL

ÁÂÁ­㴣¤j

³o¨â­ÓÀÉ®×¹ê»Ú¨Ï¥Î±¡§Î¬O:
1. ¸ê®ÆÀÉ(¥Í²£¬ö¿ý) ©M ­n§ì¸ê®ÆªºÀÉ(¥Í²£¤é³ø)¬O¦s©ñ¦b¦P¤@­Ó¸ê®Æ§¨¸Ì¨Ã¶}©ñ¦@¥Î¡C
2. ¸ê®ÆÀɬO²£½u¤@ª½¶}µÛ¡A ¤@¦ý¦³²£¥X´N¥Ñ²£½u§Y®É¿é¤J²£¥X¸ê®Æ¡A¨ä¥L¹q¸£¥u¯à¥Î°ßŪ¼Ò¦¡¶}±Ò   ³o­ÓÀɮסC
3. §ì¸ê®ÆªºÀɬO¥DºÞ¦b¥t¥~¤@¥x¹q¸£¶}±Ò¨Ï¥Îªº¡C


§Ú±N­ã´£¤jªº½X ¸m¤JªüÀs¤jµ{¦¡½Xªº³o­Ó¦ì¸m¡A
¦pªG¸ê®ÆÀÉ©M§ì¸ê®ÆªºÀɦb¦P¤@¥x¹q¸£¦P®É¶}µÛ¡A¥i¥H§ì¨ú¸ê®Æ¥B¸ê®ÆÀɤ£·|Ãö³¬¡C
¦ý­Y¬O ¸ê®ÆÀɬOÃö³¬®É¡A °õ¦æ§ì¸ê®Æµ{¦¡´N·|¥X²{¿ù»~°T®§¡C


¥i§_:
1.·í¸ê®ÆÀɵL¥ô¦ó¤H¶}±Ò®É¡A Åý¥DºÞ¥u¶}±Ò§ì¸ê®ÆªºÀÉ  °õ¦æ§ì¸ê®Æµ{¦¡®É¡A¸ê®ÆÀÉ·|¦Û¦æ¶}±Ò¨Ã°õ¦æ§ì¨ú¸ê®Æ¡A§¹¦¨«á¸ê®ÆÀɤ£·|¦Û¦æÃö³¬ (¥Ñ¥DºÞ¦Û¦æ¤â°ÊÃö³¬)

2.·í¦³¨ä¥L¥x¹q¸£¦b¨Ï¥Î¸ê®ÆÀÉ®É, ¥DºÞ¥u¶}±Ò§ì¸ê®ÆªºÀÉ  °õ¦æ§ì¸ê®Æµ{¦¡®É¡A¸ê®ÆÀɬO¥H°ßŪ¼Ò¦¡¶}±Ò«á§ì¨ú¸ê®Æ¡A¸ê®Æ§ì¨ú§¹¦¨«á¸ê®ÆÀÉ(°ßŪ¼Ò¦¡)¤£·|¦Û¦æÃö³¬ (¥Ñ¥DºÞ¦Û¦æ¤â°ÊÃö³¬)


¥H¤U¬O­ã´£¤jªºµ{¦¡½X¸m¤JªüÀs¤jªºµ{¦¡½X:
  1. Sub ¬d¸ß§ë²£¼Æ¶q()

  2. '«Å§iÅܼÆ
  3. Dim ÀɦW$, ¸ô®|ÀɦW$, tt$, R&

  4. Application.ScreenUpdating = False '¿Ã¹õ§Y®É§ó·sÃö³¬

  5. Set Dy = CreateObject("scripting.dictionary")  '³]Dy¬°¦r¨åª«¥ó
  6. Path = ThisWorkbook.Path  '§ì¨ú¥»Àɮ׸ô®|

  7. '©R¦W¦¹¤u§@ªí¬° "­n¶ñªºªí"
  8. Set ­n¶ñªºªí = ThisWorkbook.Sheets("2018¤T¼t¾÷¥x¥Í²£°lÂÜ")

  9. '¦pªG[G5]¦³¸ê®Æ´N¨Ì[G5]¸ô®|ªºÀɮסA¦pªG¨S¨ì´N§ä¦P¸ô®|¤Uªº¥t¤@­ÓexcelÀÉ
  10. If [G5] <> "" Then
  11.   ¸ô®|ÀɦW = [G5]
  12.   ÀɦW = Right(¸ô®|ÀɦW, Len(¸ô®|ÀɦW) - InStrRev(¸ô®|ÀɦW, "\"))
  13.   If Dir(¸ô®|ÀɦW) = "" Then MsgBox "¨Ì[G5]¿é¤Jªº¸ô®|»PÀɦW§ä¤£¨ìÀɮסA½ÐÀˬd¦³µL¿ù»~": Exit Sub
  14. Else
  15.   ÀɦW = Dir(Path & "\*.xls*")
  16.   If ÀɦW = ThisWorkbook.Name Then ÀɦW = Dir
  17.   ¸ô®|ÀɦW = Path & "\" & ÀɦW
  18. End If
  19.   
  20. 'Àˬd¸ê®ÆÀɮ׬O§_¤w¶}±Ò
  21. For Each wb In Workbooks
  22.   'If wb.Name = ÀɦW Then MsgBox "¸ê®ÆÀɮ׶}±Ò¤¤¡A½ÐÃö³¬": Exit Sub
  23.   
  24.   
  25. 'Àˬd¸ê®ÆÀɮ׬O§_¤w¶}±Ò, ­Y¥¼¶}±Ò«h¥H[°ßŪ]¶}±Ò, ¨Ã¥HuChk¼Ð¥Ü¬°1
  26. On Error Resume Next
  27. uChk = 0: Set ¸ê®ÆÀÉ = Workbooks(ÀɦW)
  28. On Error GoTo 0
  29. If ¸ê®ÆÀÉ Is Nothing Then uChk = 1: Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW, ReadOnly:=True)
  30. 'Ãö³¬ÀÉ®×_¤£¦sÀÉ (­Y¸ê®ÆÀɤ£¬Oµ{¦¡©Ò¶}±Ò, «h¤£Ãö³¬)
  31. If uChk = 1 Then ¸ê®ÆÀÉ.Close 0

  32. Next

  33. '¥´¶}¸ê®ÆÀɮסA¨Ã¥B©R¦W¬°"¸ê®ÆÀÉ"
  34. Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW)

  35. '³v¤@§â¤u§@ªíªº¥Í²£¥N½X»PÀY²£¼Æ¶q¿é¤J¨ì¦r¨åª«¥óDy¸Ì­±
  36. For Each ws In ¸ê®ÆÀÉ.Sheets
  37.   ws.Activate
  38.   If ws.[D1] <> "§ë²£¼Æ¶q" Then GoTo ¸õ¹L 'Àˬd¬O§_¬°­nªº¤u§@ªí
  39.   For R = 2 To ws.[A1].End(xlDown).Row
  40.     tt = Cells(R, 3): Dy(tt) = Cells(R, 4)
  41.   Next R
  42. ¸õ¹L:
  43. Next

  44. '±Ò¥Î­n¶ñªºªí
  45. ­n¶ñªºªí.Activate

  46. '³v¤@§â¦r¨åª«¥óDy¸Ì­±ªº­È¿é¤J¨ì¦¹¤u§@ªí(­n¶ñªºªí)
  47. For R = 2 To [A1].End(xlDown).Row
  48.   tt = Cells(R, 4)
  49.   Cells(R, 5) = Dy(tt)
  50. Next R

  51. '¤£¸õ¥X½T»{°T®§
  52. Application.DisplayAlerts = False

  53. '¦sÀÉÃö³¬+ÄÀ©ñ°O¾ÐÅé
  54. '¸ê®ÆÀÉ.Close True: Set ¸ê®ÆÀÉ = Nothing
  55. 'Set Dy = Nothing

  56. '¿Ã¹õ§Y®É§ó·s¥´¶}
  57. Application.ScreenUpdating = True
  58. End Sub
½Æ»s¥N½X
¥¼¶}±Ò¸ê®ÆÀÉ ª½±µ°õ¦æ§ì¨ú¸ê®Æªº¿ù»~°T®§.jpg

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-9-11 10:19 ½s¿è

¥Í²£¤é³øªí¤¤ªº¡e¾÷¥x/¤é´Á/¥Í²£¥N½X¡f, ¬O¨Æ¥ý¿é¤Jªº?
¶·¤T­Ó³£²Å¦X¤~§ì¡e§ë²£¼Æ¶q¡f¡H¡H

TOP

­Y­n§ìªºÀÉ®×¥u¦³¤@­Ó,
³Ì¦n±j¨î¦bG5«ü©w[¸ô®|¤ÎÀɦW], ¥HÁקK»~§ì~~
¤]¬Ù¥h¦h¾lªºµ{¦¡½X~~

TOP

        ÀR«ä¦Û¦b : ¦Y­W¤F­W¡B­WºÉ¤Ü¨Ó¡A¨ÉºÖ¤FºÖ¡BºÖºÉ´d¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD