[µo°Ý] §Ú¦³Ãö©óVlookup ªº°ÝÃD·Q½Ð°Ý

peellau

§Ú¦³¤@­Óa.xlsx ¸Ì­±¦³Àx¦s®æ¬O¥ÎVlookup¥h°Ñ·Ób.xlsx ªº¸ê®Æ ¦ý¤@¯ë¨Ó»¡ b.xlsx¬O¥t¤@¥x¹q¸£¶}µÛ
¥»¾÷°£¤F¶}a.xlsx¤§¥~¤£·|¦A¶}b.xlsx ¦ý³o¼Ë§Úµo²{¤@­Ó°ÝÃD ¦pªGb.xlsx¸g¥Ñ¥t¤@¥x¹q¸£¥h§ó·s¸ê®Æ
¥»¾÷ºÝ¶}µÛa.xlsx¨ÃµLªk§Y®É§ó·s ½Ð°Ý¦U¦ì¤j¤j ¦³¿ìªk¥i¥H§Y®É§ó·s ©ÎªÌ¬O»s§@vba«ö¶s §ó·s¶Ü?

huijuang

¥i¥H¨Ï¥Î¡y½s¿è¡z¡÷¡y³sµ²¡z¡÷¡y§ó·s¼Æ­È¡z
³o¼ËÀ³¸Ó´N¥i¥H§ó·s¤F
·íµM¦pªG­n°µ¦¨VBA
¥u­n¿ý»s¥¨¶°¡AÀ³¸Ó´N¥i¥H¤F

¤£ª¾¹D¨ä¥L«e½ú¦³¨S¦³§ó¤è«Kªº¤èªk?

owen06

¦^´_ 1# peellau

¥Îapplication.ontime¥h¦Û°Ê°õ¦æ§ó·s

¦ba¤u§@ªí¸Ìªºthisworkbook¿é¤J

1. Private Sub Workbook_Open()
   
2. Call §ó·s
   
3. End Sub

½Æ»s¥N½X

¦ba¤u§@ªí¼Ò²Õ¸Ì·s¼W

1. Sub §ó·s()
   
2. T1 = Now + TimeSerial(0, 0, 1)
   
3. range("xxxxx").QueryTable.Refresh BackgroundQuery:=False    '=>³o¸Ì¥i¥H§ï¦¨§A¦Û¤v­nªº¤è¦¡
   
4. Application.OnTime T1, "§ó·s"
   
5. End Sub

½Æ»s¥N½X

TimeSerial(0, 0, 1)¬Oªí¥Ü¤@¬í§ó·s¤@¦¸
              ®É ¤À ¬í

peellau

¦^´_ 3# owen06

owen06  ±z¦n §Ú¦³¹Á¸Õ§â±zªºµ{¦¡½X¶K¤W¥h
THISWORKBOOK:
Private Sub Workbook_Open()
Call "§ó·s"
End Sub

MODULE1:

Sub §ó·s()
T1 = Now + TimeSerial(0, 0, 1)
Range("l2:l20000").QueryTable.Refresh BackgroundQuery:=False
Application.OnTime T1, "§ó·s"
End Sub

¦ý·|¥X²{À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~@@ ¤£ª¾¹D³o¬O¤°»ò°ÝÃD ¹Á¸Õ¥Î©_¥L¤èªk¤]·|³o¼Ë
°»¿ù¬O«ü¦V³o¦æ Range("l2:l20000").QueryTable.Refresh BackgroundQuery:=False

owen06

¦^´_ 4# peellau


   ¦pªG¨S¿ùªº¸Ü¡AÀ³¸Ó¬O¦]¬°¦b¼Ò²Õ¸Ìªºµ{¦¡¡A¥²»Ýµ¹¥L«ü©w¤@­Ó©ú½Tªº¸ô®|¡A¤~¦³¿ìªk¥¿½T°õ¦æ¡A
   ¤£µM¥L¤£ª¾¹D¸Ó¥h­þ°õ¦æ³o­Ó«ü¥O¡A©Ò¥H§A¥²»Ý¦A¥[­Ósheet("x").range("i2:i20000")¡AÀ³¸Ó´N¯à¥¿±`°õ¦æ¤F¡A
   ¦]¬°§Ú¤£½T©w§AªºÀɮפº®e¡A¦ý§Ú¥Î¶×¤Jweb¸ê®Æªº®É­Ô¡A³£¥u­n¦b¸ê®Æªº²Ä¤@®æ¥h«ö¥kÁä´N¯àÂI¿ï§ó·s¸ê®Æ¡A
   ©Ò¥H¦pªG§A¨º¼Ë°õ¦æ¦³°ÝÃDªº¸Ü¡A¸ÕµÛ¥Îrange("i2")´N¦n¤F¬Ý¬Ý

huijuang

¦^´_ 4# peellau

THISWORKBOOK:
Private Sub Workbook_Open()
Call "§ó·s"
End Sub

MODULE1:

Sub §ó·s()
T1 = Now + TimeSerial(0, 0, 1)
Range("l2:l20000").QueryTable.Refresh BackgroundQuery:=False
Application.OnTime T1, "§ó·s"
End Sub

±NRange("l2:l20000").QueryTable.Refresh BackgroundQuery:=False
§ï¬°    ActiveWorkbook.UpdateLink Type:=xlExcelLinks
¦A¸Õ¸Õ¬Ý