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

[µo°Ý] °}¦C¯Á¤Þ¶W¥X½d³ò½Ð±Ð

[µo°Ý] °}¦C¯Á¤Þ¶W¥X½d³ò½Ð±Ð

¦U¦ì«e½ú¦n

¥Ø«e¥¿¦b¼g¤@­Óµ{¦¡¡A­n§ì¨ú­ÓªÑªº°]³ø¸ê®Æ¡A¨Ã¾ã²z¨ì¦P¤@ªí®æ
°õ¦æªºexcel¬°°]³ø²Î­pªí.xlsx
A1 = 1101, A2 = 1102, A3 = 1103...µ¥­ÓªÑ¥N½X
­ÓªÑ°]³ø¬° 1101©u³ø¡B1102©u³ø...µ¥¡Aexcel­¶­±¦p¤U¹Ï


µ{¦¡½X¦p¤U
°õ¦æ«á·|²£¥Í "°}¦C¯Á¤Þ¶W¥X½d³ò"ªºµøµ¡

Sub Getdata()
    For i = 2 To 5
        Dim WB1 As Workbook
            Workbooks.Open Filename:="C:\Users\user\Documents\KT\§ë¸ê²z°]\Excel¤ÀªR¼Ò²Õ\" & Range("A" & i) & "©u³ø.xlsx", UpdateLinks:=False, ReadOnly:=True
            Set WB1 = ActiveWorkbook

            With Workbooks(°]³ø²Î­pªí).Worksheets("sheet1")
                Range("E" & i).FormulaR1C1 = "=[" & Range("A" & i) & "©u³ø.xlsx]ISQ!R4C2"
                Range("F" & i).FormulaR1C1 = "=[" & Range("A" & i) & "©u³ø.xlsx]ISQ!R5C2"
            End With

        WB1.Close
    Next i   
End Sub

­Y¨Ï¥Î"On Error Resume Next"
µ{¦¡°õ¦æ·|Ū¨ú¥¿½T¸ê®Æ¡A¦ý§ÚÁÙ¬O·Qª¾¹D¯u¥¿°ÝÃD¥X¦b­þ...

1. ½Ð±Ð¿ù»~¥X²{¦b­þ­Ó¦a¤è¡H
         ¤@¶}©l´ú¸Õ¥i°õ¦æ                                   Range("E" & i).FormulaR1C1 = "=[1101©u³ø.xlsx]ISQ!R4C2"
         ­×§ï«á, ¥[¤JÅܼƫK²£¥Í¿ù»~                Range("E" & i).FormulaR1C1 = "=[" & Range("A" & i) & "©u³ø.xlsx]ISQ!R4C2

2. With sheet1ªº±¡ªp¤U¡A¤£¬OÀ³¸Ó³£­n¼g¦¨ " .range(....) "¶Ü¡H¬°¦ó¦b¦¹¤£¥²¼W¥[ "."©O¡H

ÁÂÁ¦U¦ì«e½ú«ü±Ð~

°]³ø²Î­pªí¬OÅܼơH
¥["."¬O«üWith ¤ºªºWorkbooks¡A¤£¥["."«üactiveworkbook

TOP

¥i¯à§Ú¨S¦³»¡©ú²M·¡
¦b"°]³ø²Î­pªí.xlsx"¤º°õ¦æµ{¦¡
§ì¨ú¦P¤@­Ó¸ê®Æ§¨¤ºªº "1101©u³ø.xlsx", "1102©u³ø.xlsx"...µ¥

¥[ "."ªº·N«ä§ÚÀ´
§Ú¥H¬°¶}±Òworkbook´N·|§â¶}±Òªº¨º¤@­ÓÅܦ¨activeworkbook.....
¦hÁÂÄÀºÃ~

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-2-3 22:40 ½s¿è

¦^´_ 3# bulletin
©ñ¦b'Workbooks("°]³ø²Î­pªí.xlsm")ªº¼Ò²Õ
  1. Sub Getdata()
  2.     For i = 2 To 5
  3.         Dim WB1 As Workbook
  4.             Set WB1 = Workbooks.Open (Filename:="C:\Users\user\Documents\KT\§ë¸ê²z°]\Excel¤ÀªR¼Ò²Õ\" & Range("A" & i) & "©u³ø.xlsx", UpdateLinks:=False, ReadOnly:=True)
  5.              With Thisworkbook.Worksheets("sheet1")   'Workbooks("°]³ø²Î­pªí.xlsm")
  6.                 Range("E" & i).FormulaR1C1 = "=[" & Range("A" & i) & "©u³ø.xlsx]ISQ!R4C2"
  7.                 Range("F" & i).FormulaR1C1 = "=[" & Range("A" & i) & "©u³ø.xlsx]ISQ!R5C2"
  8.             End With
  9.         WB1.Close
  10.     Next i   
  11. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦hÁ¶W¯Åª©¥D¦^ÂÐ!!
§Úª¾¹D°ÝÃD¦b­þ¤F~
·P®¦~

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD