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

loyyee

½Ð°Ý§Ú¦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)

¤U¸ü¦¸¼Æ: 13

2013-5-7 23:03

stillfish00

¦^´_ 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

loyyee

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

stillfish00

¦^´_ 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

loyyee

·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

stillfish00

¦^´_ 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

GBKEE

¦^´_ 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. ³Æµù
   
8. ¤U¦C´XÃþ Microsoft Excel ¦WºÙ¥i¥H¨Ï¥Î¦¹¤èªk¡G
   
9. 
   
10. A1 ¼Ë¦¡ªº°Ñ·Ó¡C¥i¥H A1 ¼Ë¦¡ªº°O¸¹¦C¥X¹ï¥ô·Nªº³æ­ÓÀx¦s®æªº°Ñ·Ó¡C©Ò¦³ªº°Ñ·Ó³£·í¦¨µ´¹ï°Ñ·Ó¡C
    
11. ½d³ò¡C±z¥i¥H¨Ï¥Î½d³ò¡B¥æ¤e½d³ò©M¦h­«½d³ò¡A¨Ï¥Îªº¹Bºâ¤l¤À§O¬°«_¸¹¡BªÅ¥Õ¤Î³r¸¹¡C
    
12. ¤w©w¸qªº¦WºÙ¡C±z¥i¥H«ü©w¥¨¶°»y¨¥¤¤¥ô¦ó¦WºÙ¡C
    
13. 
    
14. ¥~³¡°Ñ·Ó¡C¥i¥H¨Ï¥Î ! ¹Bºâ¤l°Ñ·Ó¥t¤@¬¡­¶Ã¯¤WªºÀx¦s®æ©Î¤w©w¸qªº¦WºÙ¡C¨Ò¦p¡AEvaluate("[BOOK1.XLS]Sheet1!A1")¡C
    
15. 
    
16. PS: ***--¥~³¡°Ñ·Ó¡C [BOOK1.XLS] ¶·¬O¶}±Òªº -***-'¥~³¡°Ñ·Ó¨ì ¥¼¶}±ÒªºÀɮ׶Ǧ^¿ù»~
    
17. 
    
18. ªþµù  ¨Ï¥Î¤è¬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

stillfish00

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

stillfish00

¦^´_ 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

loyyee

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