ªð¦^¦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 (716.07 KB)

¸óÀɧì¸ê®Æ¬ö¿ý.jpg

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

¦^´_ 1# ABK


    ¬Û«H¥H§Aªºµ{«×À³¸Ó³o¼Ë¦^´N¬Ý±oÀ´¤F¡C
½×¾Â¤º¦³¬ÛÃö½d¨Ò¥i¥H¥Î¡C
Open Excel Workbook¡A
  1. Set Wb = Workbooks.Open("\\Test\B.xlsx")
½Æ»s¥N½X
¶}±Ò²Ä¤G­ÓExcel¤è¦¡½d¨Ò
CreateObject("Scripting.Dictionary")«Ø¥ß¦r¨å¤ñ¹ï¦^¶Çµ²ªG¡C
Ãþ¦ü½d¨Ò¥i¥H°Ñ¦Ò#3
¥ý¸ÕµÛ¼g¼g¬Ý¡A¦³°ÝÃD¦AÀ°¦£­×§ï¡C

µM¦Ó§Úı±o¥ÎAccess«Ø¥ß¯Á¤Þ§ó§Ö¡A¥ÎExcel¥¨¶°µyºC¨Ç¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-9-3 02:14 ½s¿è

¦^´_ 1# ABK

§A¤W¶ÇªºÀÉ®×Ãa¤F¡A¥´¤£¶}!

³o¤£Ãø¡A¦p¤W¤@½g©Ò»¡¡A³Ð³y¦r¨åª«¥ó

§â¨ä¥¦¤u§@ªíªº½s¸¹&¼Æ¶q¿é¤J¶i¦r¨å

¦A¥Î¦r¨å¬d¸ß¥t¤@­ÓÀɮתº½s¸¹¡A¿é¤J¦r¨å¤¤ªº¼Æ¶q(item)§Y¥i

¥ý­«¤W¶Çªþ¥ó§a¡AÀ³¸Ó¤£·|¦³¤H¨º»ò¶Ô³ÒÀ°§A¥´¤@¤j¦ê¸ê®Æªº.....(µ{¦¡¬O»Ý­nÀÉ®×´ú¸Õªº)

©ÎªÌ§A¥ý¦X¨Ö¼Æ­Ó¤u§@ªí¦A¥Î VLookup ¨ç¼Æ§Y¥i!

¸Ü»¡§AÀ³¸Ó¬O¥ÍºÞ§a!!

:D
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# faye59


    ÁÂÁÂfaye59¤j!
  §Ú¬ã¨s¤@¤U±z´£¨Ñªº¤èªk!

TOP

¥»©«³Ì«á¥Ñ ABK ©ó 2018-9-3 21:52 ½s¿è

¦^´_ 3# n7822123

ÁÂÁÂ n7822123¤j
§Ú§âÀɮפÀ¶}À£ÁY¸Õ¸Õ¬Ý !
§Ú¬O¦b²£½u¤u§@¡A²£½u²£¥X«á¦U¾÷¥x¦@¥Î¤@±iExcelªí¡A¥Ñ¦U¾÷¥x­t³d¤HKey¤J²£¥X¸ê®Æ¡A ¥DºÞ­n¬Ýªº¬O³¡¤Àªº¶×Á`¸ê®Æ¡C

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

¤T¼t¦U¾÷¥Í²£¬ö¿ý.rar (17.05 KB)

TOP

¦^´_ 5# ABK


   

§Ú¤£½T©w¬O§_¬O§ÚRARª©¥»¤ÓµLªk¥´¶}

§Ú¥i¥HÀH«K¥´­Ó½d¨Ò¡AÅý§A®M¥Î¬Ý¬Ý
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-9-3 23:50 ½s¿è

¦^´_ 6# n7822123


¥´¦n¤F¡A¥´§Aªºªí®æªá¤F¤£¤Ö®É¶¡(»P§Ú·Q­nÀH«K¥´¥´ªºªì°J¦³ÂI¤£²Å......)
ÀÉ®×&µ{¦¡½X¦p¤U¡A½Ð¦Û¦æ®M¥Î¡A¬JµM³£ªá®É¶¡¤F¡Aµù¸Ñ¤]À°§A¼g¦n(¦n¤H°µ¨ì©³?)
ªí®æ¦ì¸m»P¤u§@ªí¦WºÙ¤w¸gºÉ¶q»P§A¬Û¦P¡A
¦ý¬O¥i¯àÁÙ¬O¦³¤£¦Pªº¦a¤è¡A½Ð¦Û¦æ§ó§ï®M¥Î¡C
ªþ¥ó2­ÓÀɮשñ¦b¦P¤@¸ô®|¤U°õ¦æ§Y¥i(¤£¥i¦³¨ä¥LexcelÀÉ®×)
©ÎªÌ¿é¤J¸ê®Æªº¸ô®|&ÀɦWÅýµ{¦¡¥h§ì!

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

TOP

¥»©«³Ì«á¥Ñ ABK ©ó 2018-9-4 02:27 ½s¿è

¦^´_ 7# n7822123


    ·PÁªüÀs¤jªº¼ö¤ß¦^´_!  Åý§A¶O³o»ò¦h®É¶¡ !  ¯u¬O©êºp!
    §Ú¨Ó«ôŪ¬ã¨s¤@¤U !   ·PÁÂ!  ·PÁÂ!

TOP

¦^´_ 8# ABK


¦A½Ð±Ð¤@¤U¦U¦ì«e½ú!
·í§Ú¨Ï¥ÎªüÀs¤jªº½d¨Ò§ì¨ú¸ê®Æ®É¡A Excel ·|­n¨D§ÚÃö³¬³Q§ì¨ú¸ê®Æ¨º­ÓÀɤ~¯à§ì¨ú¸ê®Æ¡A
¦³¿ìªk°µ¨ì¤£Ãö³¬¸ê®ÆÀɤ]¥i¥H§ì¨ú¸ê®Æ¶Ü?

¸ê®ÆÀɮ׶}±Ò¤¤-½ÐÃö³¬.jpg (172.67 KB)

¸ê®ÆÀɮ׶}±Ò¤¤-½ÐÃö³¬.jpg

TOP

'Àˬd¸ê®ÆÀɮ׬O§_¤w¶}±Ò, ­Y¥¼¶}±Ò«h¥H[°ßŪ]¶}±Ò, ¨Ã¥HuChk¼Ð¥Ü¬°1
On Error Resume Next
uChk = 0: Set ¸ê®ÆÀÉ = Workbooks(ÀɦW)
On Error GoTo 0
If ¸ê®ÆÀÉ Is Nothing Then uChk = 1: Set ¸ê®ÆÀÉ = Workbooks.Open(¸ô®|ÀɦW, ReadOnly:=True)


'Ãö³¬ÀÉ®×_¤£¦sÀÉ (­Y¸ê®ÆÀɤ£¬Oµ{¦¡©Ò¶}±Ò, «h¤£Ãö³¬)
If uChk = 1 Then ¸ê®ÆÀÉ.Close 0

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD