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

¦p¦ó¨ú±o¬Y­ÓÀɮתºÀx¦s®æ¸ê®Æ¡H¡]ÀÉ®×ÀɦW¡A¼g¦b¦sÀx¦s®æ¤º¡^

¦p¦ó¨ú±o¬Y­ÓÀɮתºÀx¦s®æ¸ê®Æ¡H¡]ÀÉ®×ÀɦW¡A¼g¦b¦sÀx¦s®æ¤º¡^

¥»©«³Ì«á¥Ñ eigen ©ó 2013-11-6 14:52 ½s¿è

¦p¦ó¨ú±o¬Y­ÓÀɮתºÀx¦s®æ¸ê®Æ¡H¡]ÀÉ®×ÀɦW¡A¼g¦b¦sÀx¦s®æ¤º¡^

¥Ø«e°µ¥X¨Óªº¡G

https://dl.dropboxusercontent.com/u/12575824/Book3.xls

§Ú°Ñ¦Ò http://forum.twbts.com/thread-6224-1-1.html §¹¦¨¤F ¶}±ÒÀÉ®×Ū¨úÀɦWªº¥\¯à¡C

²{¦b§Ú·Q­nªº¬O°Ñ¦Ò³o­ÓÀx¦s®æ¡A°ÊºA§ì¨úÀɮפºªº¸ê®Æ¡C

­ì¥»¼g¦ºªº¬O³o¼Ë A2 = 'D:\report\[20131106 ¾P³f.xls]Sheet4'!$E7

Setup!B1=D:\report\20131106 ¾P³f.xls

²{¦b§Ú§Æ±æ¬O A2 = Setup!B1   !Sheet4'!$E7

³o¼Ë¸Ó¥Î¤°»ò»yªk¨Ó°µ¡H

³Â·Ð¤j¼w«üÂI¡AÁÂÁÂ~~

¦^´_ 4# eigen

¬Ý¨Ó§A¬O­nÅý¿é¥X­¶ªºÀx¦s®æ¡A¨Ì¾ÚSetupªºB1»PB8©Ò²Õ¦¨ªº¥~³¡°Ñ·Ó¦r¦ê°µ³sµ²
¨Ò¦p:¿é¥X­¶A4­n±o¨ìD:\report\[20131106 ¾P³f.xls]Sheet4!A4ªº­È
©Ò¥H¿é¥X­¶A4¥²¶·¬O¤½¦¡='D:\report\[20131106 ¾P³f.xls]Sheet4'!A4
    ©Ò¥Hµ{¦¡½X´Nª½±µ¼g¤J¤½¦¡§Y¥i
Sheet2.[A4] = "='" & [B1] & [B8] & "'!A4"
  1. Private Sub CommandButton1_Click()
  2.         Dim file_Input As String
  3.         file_Input = Application.GetOpenFilename("EXCELÀÉ(*.XLS),*xls")
  4.         If file_Input <> "" Then
  5.                 MsgBox "Reference " & file_Input
  6.                 Range("B1").Value = Replace(file_Input, Dir(file_Input), "[" & Dir(file_Input) & "]") 'Âন°Ñ·Ó§Î¦¡ªº¦r¦ê
  7.                 Sheet2.[A4] = "='" & [B1] & [B8] & "'!A4" '¿é¥X­¶A4¼g¤J¤½¦¡
  8.         End If
  9. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2013-11-7 21:57 ½s¿è
...
²{¦b¦³¨â­Ó°ÝÃD
¤@¡Bindirect(ref_point&"A7",1) ·|Åã¥Ü #REF! ¡A°£«D±N D:\report\20131106 ¾P³f.xls ¶}±Ò
¤G¡Bindirect(ref_point&"A7",1) copy ¨ì¨ä¥¦Àx¦s®æ, a7¤£·|»¼¼W¦¨ a8 a9 a10 a11....
½Ð°ª¤âÀ°À°¦£¡A³o¨â­Ó°ÝÃD«ç»ò§JªA¡AÁÂÁÂ:'(
eigen µoªí©ó 2013-11-7 12:09

¤@¡B ¼ÐªºÀÉ®×¥¼¶}±Òªºª¬ºA¤U§ì¤£¨ìÀx¦s®æ¤º®e¬O¥¿±`ªº.
­Y·Q­nÁקK¨q¥X¿ù»~°T®§ªº¸Ü,
§Ú¸û±`¥Îªº¤è¦¡¬O¦b·|¥X²{ #REF! ªº¤½¦¡¤W®M¥Î :
=IF(ISERROR(­ì¤½¦¡),"",­ì¤½¦¡)
ªº§Î¦¡,
³o¼Ë­Y¦³¿ù»~µo¥Í«hÅܦ¨¤£Åã¥Ü¥ô¦ó¤å¦r.

¤G¡B¥i¥H¥Î indirect(ref_point&"A"&ROW()+1,1)
¥H¤W°²³]¤½¦¡©ñ¦b ²Ä 6 ¦C¥ô¤@Àx¦s®æ¤W,
ROW() = 6 ¦A + 1 ´N¬O 7 ¤F,
¦Ü©ó¨ä¤U¤@¦C : ROW() = 7 ¦A + 1 ´N¬O 8 ¤F. (¥[´î¦h¤Ö¨Ì¤½¦¡©Ò¦bÀx¦s®æªº¦C¸¹¨Ó¨M©w)

TOP

¥»©«³Ì«á¥Ñ eigen ©ó 2013-11-7 12:11 ½s¿è

§Ú§ï¼g http://forum.twbts.com/thread-6224-1-1.html  §ìÀɮתº¤è¦¡
  1. Private Sub CommandButton1_Click()
  2.         Dim file_Input As String
  3.         Dim file_path As String
  4.         Dim file_name As String
  5.         Dim file_index As Integer
  6.         file_Input = Application.GetOpenFilename("EXCEÀÉ(*.XLS),*xls")
  7.         

  8.         If file_Input <> "" Then
  9.                 file_index = InStrRev(file_Input, "\")
  10.                
  11.                 file_path = Left(file_Input, InStrRev(file_Input, "\"))
  12.                 file_name = Right(file_Input, Len(file_Input) - InStrRev(file_Input, "\"))
  13.                
  14.                 MsgBox "Reference " & file_Input
  15.                 'Cells(1, 2) = file_path
  16.                 Range("B1").Value = file_path & "[" & file_name & "]"
  17.                
  18. '                Range("B2").Value = file_path
  19. '               Range("B3").Value = file_name
  20.   '              Range("B4").Value = file_Input
  21.         End If

  22. End Sub
½Æ»s¥N½X
Àx¦s®æªºÅã¥Ü´N§ï¦¨³o¼Ë
Setup!B1= D:\report\[20131106 ¾P³f.xls]

§Ú¤S¦b ¦WºÙ ©w¸q¤F ref_point ="'"&Setup!$B$1&Setup!$B$8&"'!"

¦pªG¦bÀx¦s®æª½±µ¶ñ¤J ref_point ='D:\report\[20131106 ¾P³f.xls]Sheet4'!

±µµÛ§Ú°Ñ¦Òªº luhpro ªº¼gªk  Àx¦s®æ=indirect(ref_point&"A7",1)  ²×©ó¥i¥H§ì¨ì¥t¤@­ÓÀɮתº¸ê®Æ¤F

¥Ø«eªº¶i«×

https://dl.dropboxusercontent.com/u/12575824/Book3.xls

²{¦b¦³¨â­Ó°ÝÃD

¤@¡Bindirect(ref_point&"A7",1) ·|Åã¥Ü #REF! ¡A°£«D±N D:\report\20131106 ¾P³f.xls ¶}±Ò

¤G¡Bindirect(ref_point&"A7",1) copy ¨ì¨ä¥¦Àx¦s®æ, a7¤£·|»¼¼W¦¨ a8 a9 a10 a11....

½Ð°ª¤âÀ°À°¦£¡A³o¨â­Ó°ÝÃD«ç»ò§JªA¡AÁÂÁÂ:'(

TOP

A2=INDIRECT(CONCATENATE("'",LEFTB(CELL("filename",Setup!B1),FINDB("]",CELL("filename",Setup!B1), ...
luhpro µoªí©ó 2013-11-6 23:15



   ÁÂÁÂ~~¤£¹L....cell §ì¨ìªº¥Ø«eÀɮתºfilename ¡A¦Ó§Ú­nªºÀÉ®× filename ¤w¸g¼g¦b Setup!B1

TOP

¦p¦ó¨ú±o¬Y­ÓÀɮתºÀx¦s®æ¸ê®Æ¡H¡]ÀÉ®×ÀɦW¡A¼g¦b¦sÀx¦s®æ¤º¡^

¥Ø«e°µ¥X¨Óªº¡G

https://dl.dropboxus ...
eigen µoªí©ó 2013-11-6 14:50


A2=INDIRECT(CONCATENATE("'",LEFTB(CELL("filename",Setup!B1),FINDB("]",CELL("filename",Setup!B1),1)),"Sheet4","'","!E7"),1)

TOP

        ÀR«ä¦Û¦b : ¹ï¤÷¥À­nª¾®¦¡A·P®¦¡B³ø®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD