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

[µo°Ý] ¨ú±o³¡¤À¬Û¦PÀɦW¤§¤º®e

[µo°Ý] ¨ú±o³¡¤À¬Û¦PÀɦW¤§¤º®e

½Ð°Ý§Ú¦b"A1"Àx¦s®æ¿é¤J¨ä¥LÀɮ׫e6½XÀɦW®É¡A´N¥i¥H¨ú±o¸ÓÀɮפº¸ê®Æ¡A
¥i§_¦³¨ç¼Æ©Î¥¨¶°¥i¥H¹F¦¨¡C

EX:
¿é¤J:34212A  ¨ú±o 34121A-ACER-45678 Àɮפº¸ê®Æ
¿é¤J:34185A  ¨ú±o 34185A-SAMSUNG-1234567 Àɮפº¸ê®Æ

³¡¤À¬Û¦PÀɦW.rar (3.9 KB)

¦^´_ 1# loyyee
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim s
  3.     If Target.Address = "$A$1" Then
  4.         s = Dir(ThisWorkbook.Path & "\" & [A1] & "*")
  5.         If s <> "" Then
  6.             [A2] = Evaluate("SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)")
  7.         Else
  8.             MsgBox "File Not Found!"
  9.         End If
  10.     End If
  11. End Sub
½Æ»s¥N½X

TOP

·PÁÂstillfish00 ¦^´_¡A¤£¹L¥¨¶°¦n¹³¤£¦æ¥Î¡C

TOP

¦^´_ 3# loyyee
¦]¬°¥Î Worksheet_Change Event¡A©Ò¥H­n©ñ¦b¤u§@ªí¦Ó¤£¬O¼Ò²Õ:

Alt + F11¡A±M®×µøµ¡¡A®i¶} Microsoft Excelª«¥ó
Sheet1ÂI¨â¤U¡A¶K¤W¤W­±ªº code

TOP

·PÁ±z¡A¤£¹L¦³ÂI°ÝÃD:
1."A1"Àx¦s®æ=""®É¤~·|¬Û¥[¡A¦³¼Æ­È®É´N¥X²{¿ù»~¡C
2.¦pªG¬Û¹ïÀ³Àɮפ£¶}±Ò®É¡A¿é¤JA1Àx¦s®æ:34121A¥i§_¹F¦¨
='D:\[34121A-ACER-45678.xls]RR'!$B$10+'D:\[34121A-ACER-45678.xls]BB'!$B$10

¿é¤J:34185A  ¨ú±o 34185A-SAMSUNG-1234567 Àɮפº¸ê®Æ
='D:\[34185A-SAMSUNG-1234567 .xls]RR'!$B$10+'D:\[34185A-SAMSUNG-1234567 .xls]BB'!$B$10

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-5-8 20:48 ½s¿è

¦^´_ 5# loyyee
Evaluate¦³®É­Ô¦n¹³¤£Å¥¨Ï³ê¡A
§ï³o¼Ë©O?  «O¯d¤½¦¡
µM«á­pºâ¬ORR~BB¦U¤u§@ªíªºB10¬Û¥[
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim s
  3.     If Target.Address = "$A$1" Then
  4.         s = Dir(ThisWorkbook.Path & "\" & [A1] & "*")
  5.         If s <> "" Then
  6.             [A2] = "=SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)"
  7.         Else
  8.             [A2]=""
  9.             MsgBox "File Not Found!"
  10.         End If
  11.     End If
  12. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-8 21:31 ½s¿è

¦^´_ 6# stillfish00
  1. Evaluate ¤èªk
  2. ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w±N Microsoft Excel ¦WºÙÂà´«¦¨ª«¥ó©ÎªÌ­È¡C
  3. expression.Evaluate (Name)
  4. expression      ¹ï©ó¿ï¾Ü©Êªº Application ª«¥ó¡A¹ï©ó¥²¿ïªº Chart ª«¥ó¡ADialogSheet ª«¥ó¡A©M Worksheet ª«¥ó¡C¶Ç¦^ [®M¥Î©ó] ²M³æ¤¤ªºª«¥óªº¹Bºâ¦¡¡C
  5. Name     ¥²¿ïªº String¡Cª«¥ó¦WºÙ¡A¨Ï¥Î Microsoft Excel ªº©R¦WÂà´«¡C

  6. ³Æµù
  7. ¤U¦C´XÃþ Microsoft Excel ¦WºÙ¥i¥H¨Ï¥Î¦¹¤èªk¡G

  8. A1 ¼Ë¦¡ªº°Ñ·Ó¡C¥i¥H A1 ¼Ë¦¡ªº°O¸¹¦C¥X¹ï¥ô·Nªº³æ­ÓÀx¦s®æªº°Ñ·Ó¡C©Ò¦³ªº°Ñ·Ó³£·í¦¨µ´¹ï°Ñ·Ó¡C
  9. ½d³ò¡C±z¥i¥H¨Ï¥Î½d³ò¡B¥æ¤e½d³ò©M¦h­«½d³ò¡A¨Ï¥Îªº¹Bºâ¤l¤À§O¬°«_¸¹¡BªÅ¥Õ¤Î³r¸¹¡C
  10. ¤w©w¸qªº¦WºÙ¡C±z¥i¥H«ü©w¥¨¶°»y¨¥¤¤¥ô¦ó¦WºÙ¡C

  11. ¥~³¡°Ñ·Ó¡C¥i¥H¨Ï¥Î ! ¹Bºâ¤l°Ñ·Ó¥t¤@¬¡­¶Ã¯¤WªºÀx¦s®æ©Î¤w©w¸qªº¦WºÙ¡C¨Ò¦p¡AEvaluate("[BOOK1.XLS]Sheet1!A1")¡C

  12. PS: ***--¥~³¡°Ñ·Ó¡C [BOOK1.XLS] ¶·¬O¶}±Òªº -***-'¥~³¡°Ñ·Ó¨ì ¥¼¶}±ÒªºÀɮ׶Ǧ^¿ù»~

  13. ªþµù  ¨Ï¥Î¤è¬A¸¹ (¨Ò¦p, A1:C5) »P¥Î¦r¦ê¤Þ¼Æ©I¥s Evaluate ¤èªk¬Oµ¥®Äªº¡C¨Ò¦p¡A¤U¦C¹Bºâ¦¡¹ï¬Oµ¥»ùªº¡C
½Æ»s¥N½X
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim s
  3.     If Target.Address = "$A$1" Then
  4.         s = Dir(ThisWorkbook.Path & "\" & [A1] & "*")
  5.         If s <> "" Then
  6.             Application.EnableEvents = False
  7.             
  8.             '[A2] ¤½¦¡ 1
  9.             [A2] = "='" & ThisWorkbook.Path & "\[" & s & "]RR'!$B$10 *2"
  10.             '[A2] = "='" & ThisWorkbook.Path & "\[" & s & "]RR'!$B$10+'" & ThisWorkbook.Path & "\[" & s & "]RR'!$B$10"
  11.             
  12.             '[A2] ¤½¦¡ 2
  13.             'Names.Add Name:="AAA", RefersTo:="='" & ThisWorkbook.Path & "\[" & s & "]RR'!$B$10"
  14.             '[A2] = "=AAA*2"             '"=AAA+AAA"
  15.             '[A4] = Evaluate("AAA")     '¶Ç¦^¿ù»~­È
  16.             [A2] = [A2].Value           '¤½¦¡Âର¼Æ­È
  17.             Application.EnableEvents = True
  18.         Else
  19.             MsgBox "File Not Found!"
  20.         End If
  21.     End If
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

6#­×¥¿¦p¤U¡A¦]A1ªÅ¥Õ®É¡As·|¶Ç¦^¥ô·NÀÉ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim s As String
  3.    
  4.     If Target.Address = "$A$1" Then
  5.         If [A1] = "" Then [A2] = "": Exit Sub       'ÁקKDir¦^¶Ç¥ô·NÀÉ®×
  6.         
  7.         s = Dir(ThisWorkbook.Path & "\" & [A1] & "*")
  8.         
  9.         If s <> "" Then
  10.             [A2] = "=SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)"
  11.             [A2] = [A2].Value
  12.         Else
  13.             [A2] = ""
  14.             MsgBox "File Not Found!"
  15.         End If
  16.     End If
  17. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-5-9 01:01 ½s¿è

¦^´_ 7# GBKEE
PS: ***--¥~³¡°Ñ·Ó¡C [BOOK1.XLS] ¶·¬O¶}±Òªº -***-'¥~³¡°Ñ·Ó¨ì ¥¼¶}±ÒªºÀɮ׶Ǧ^¿ù»~

ª©¤j¡A§Ú¤S¸Õ¤F¤@¤U
¥ý¨Ï¥Î¤U­±ªºcode¥h¸Õ¡AA2 ·|Åã¥Ü #REF!
µM«á§â  [A3] = "=SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)"  ªºµù¸Ñ®³±¼
¦AÀH«K¦h§ï´X¦¸A1«á¡AA2¤S·|Åã¥Ü¥¿½T­È¤F
¯u¬O·Q¤£³zªü..
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim s As String
  3.    
  4.     If Target.Address = "$A$1" Then
  5.         If [A1] = "" Then [A2] = "": Exit Sub       'ÁקKDir¦^¶Ç¥ô·NÀÉ®×
  6.         
  7.         s = Dir(ThisWorkbook.Path & "\" & [A1] & "*")
  8.         
  9.         If s <> "" Then
  10.             [A2] = Evaluate("=SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)")
  11.             '[A3] = "=SUM('" & ThisWorkbook.Path & "\[" & s & "]RR:BB'!$B$10)"
  12.         Else
  13.             [A2] = ""
  14.             MsgBox "File Not Found!"
  15.         End If
  16.     End If
  17. End Sub
½Æ»s¥N½X

TOP

ÁÂÁ¤G¦ì¸Ñµª¡A¤£¹L¸g­pºâ«á¼Æ­È¦³ÂI©_©Ç¡A¬ã¨s¤¤¡C

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD