jasonwu0114

±ýŪ¨úªºEXCELÀɦW¤¤³£¦³·í¤Ñ¤é´Á(ex:ooxxx1021216.xls)
·Q°µ¤@±i·JÁ`ªí¦s©ñ¦b¥t¤@­Ó¸ê®Æ§¨
·JÁ`ªíAÄæ¦ì¬O§Ú­nªº¤é´Á

¤U¸ü (129.56 KB)

2013-12-16 14:29

¥H¤£¶}±ÒÀɮפ覡¨ÓŪ¨ú¸ê®Æ
¦³¸Õ¹Lª½±µ¥Î     "µu´ÁªÑÅv§ë¸ê³¡¦ì¤Îµû»ù±±ºÞªí1021216.xls"  ¬O§ì¨ì¸ê®Æ
¦ý§ï¦¨" µu´ÁªÑÅv§ë¸ê³¡¦ì¤Îµû»ù±±ºÞªí" &  dd  & ".xls"
¥ÎÅܼƤ覡´N¤£¦æ
¥X²{­n§Ú¿ï¨úÀɮתºµe­±
  

¤U¸ü (298.47 KB)

2013-12-16 14:28

À³¸Ó¬OÅܼƳ]©w¿ù»~
½Ð°Ý
1.Åܼƭn¦p¦ó³]©w??
2.ÁÙ¦³¨ä¥L§ó¤è«Kªº¤è¦¡¨ÓŪ¨ú¥¼¶}±ÒÀɮ׸ê®Æ??


Sub checkclosedfile1()
Dim strpath As String, strfile As String
Dim strsheet As String, strresult As String
    strpath = "X:\­·ºÞ³øªí\µu´ÁªÑÅv§ë¸ê³¡¦ì¤Îµû»ù±±ºÞªí"
    strfile = "µu´ÁªÑÅv§ë¸ê³¡¦ì¤Îµû»ù±±ºÞªí1021202.xls"
    strsheet = "¥þ¤½¥qI"
    strresult = getcellvalue(strpath, strfile, strsheet, "i1")
Cells(2, 2) = strresult
End Sub

Public Function getcellvalue(strpath As String, strfile As String, strsheet As String, stra1 As String)
    getcellvalue = ExecuteExcel4Macro("'" & "[" & strfile & "]" & strsheet & "'!" & Range(stra1).Address(, , xlR1C1))
End Function

stillfish00

¦^´_ 1# jasonwu0114
ªþ¤W¤£¦æªºµ{¦¡½X¬Ý¬Ý

jasonwu0114

§ä¨ì¤ñ¸û²³æ¼gªk
·Q½Ð±Ð
°²¦psheet1AÄ榳¤@¨Ç¤é´Á
¬O§Ú­n§ìªºÀɮצWºÙ¤@³¡¤À(¬õ¦r³¡¤À)
[IRS¥æ©ö³¡¦ìµû»ùªí1021216.xls]
¦pªG­n§â¸ê®Æ§ì¦^©ñ¦bBÄæ­n¦p¦ó¼g¦p¦ó³]©wÅܼÆ??

¥t¥~
¤@¦¸¥u¯à§ì¤@­ÓÄæ¦ì¶Ü??

Sub nn()
[A1] = ExecuteExcel4Macro("'X:\­·ºÞ³øªí\§Q²v¥æ´«³¡¦ìµû»ùªí\[IRS¥æ©ö³¡¦ìµû»ùªí1021216.xls]³¡¦ìµû»ùªí'!R1C13")
End Sub

jasonwu0114

¤£¦n·N«ä¤w¸Ñ¨M¦p¤U
½Ð°ÝÁÙ¥i¥H¼gªº§óSMART¶Ü??
¥t¥~yy = "'X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & kk & ".xls]³¡¦ìµû»ùªí'!R1C13"
R1C13³o¸Ì¥u¯à¬O³æ¤@Äæ¦ì¶Ü??

Sub nn4()
Dim yy As String
Dim kk As Range
Set kk = Sheets("¤u§@ªí1").Range("A3")
Do While kk <> ""
    yy = "'X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & kk & ".xls]³¡¦ìµû»ùªí'!R1C13"
    kk.Offset(0, 3) = ExecuteExcel4Macro(yy)
    Set kk = kk.Offset(1)
Loop
End Sub

stillfish00

¦^´_ 4# jasonwu0114

1. Sub nn4()
   
2.   With Sheets("¤u§@ªí1")
   
3.     With .Range(.[D3], .Cells(.Rows.Count, "D").End(xlUp))
   
4.       .FormulaR1C1 = "='X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & .Parent.[A3].Value & ".xls]³¡¦ìµû»ùªí'!R1C13"
   
5.       .Value = .Value
   
6.     End With
   
7.   End With
   
8. End Sub

½Æ»s¥N½X

jasonwu0114

¦^´_ 5# stillfish00

¼F®`¼F®`
¤S¾Ç¨ì¦n´X©Û
¥i¬OÁÙ¦³¤@ÂI°ÝÃD
D3~8Äæ¦pªG­ì¥»¬OªÅ®æ°õ¦æ«áD1~D3·|¶ñ¥HA3¬°°Ñ¼Æ§ä¨ìªº¸ê®Æ
               ¦pªG¦³¼Æ¦r°õ¦æ«áD3~D8·|¶ñ¥HA3¬°°Ñ¼Æ§ä¨ìªº¸ê®Æ
­n§ï­þ??
ÁÙ¦³    With .Range(.[D3], .Cells(.Rows.Count, "D").End(xlUp))  ªº·N«ä??

   

¤U¸ü (198.5 KB)

2013-12-18 16:18

stillfish00

¦^´_ 6# jasonwu0114

1. Sub nn4()
   
2.   With Sheets("¤u§@ªí1")
   
3.     With .Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).offset(,3)
   
4.       .FormulaR1C1 = "='X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & .Parent.[A3].Value & ".xls]³¡¦ìµû»ùªí'!R1C13"
   
5.       .Value = .Value
   
6.     End With
   
7.   End With
   
8. End Sub

½Æ»s¥N½X

.Rows.Count  :  Excel³Ì¤j¦æ¼Æ
.Cells(.Rows.Count, "A")  :  AÄæ³Ì¤U­±¤@®æ
.Cells(.Rows.Count, "A").End(xlUp)  :  ±qAÄæ³Ì¤U­±¤@®æ©¹¤W§ä¨ì¦³¸ê®ÆªºÀx¦s®æ
.Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp))  :  ±qA3¨ìAÄæ³Ì«á¤@µ§¸ê®Æ§Î¦¨ªº½d³ò
.Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).offset(,3)  :  ±qA3¨ìAÄæ³Ì«á¤@µ§¸ê®Æ§Î¦¨ªº½d³ò¡A³o½d³ò¦A©¹¥k²¾°Ê¤TÄæ

jasonwu0114

¦^´_ 7# stillfish00
¤£¦n·N«äÁÙ­n¦A³Â·Ð
°õ¦æ«áÁÙ¬O¥HA3¬°°Ñ¼Æ¥h§ì¸ê®Æ

1. .FormulaR1C1 = "='X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & .Parent.[A3].Value & ".xls]³¡¦ìµû»ùªí'!R1C13"

½Æ»s¥N½X

À³¸Ó¬O­n§ï³o.Parent.[A3]¶Ü
¦p¦ó§ï??

·P®¦!!

stillfish00

¦^´_ 8# jasonwu0114
§Ú¦n¹³»~·|§Aªº·N«ä¤F¡A¬O³o¼Ë¶Ü?

1. Sub nn5()
   
2.   Dim x
   
3.   With Sheets("¤u§@ªí1")
   
4.     For Each x In .Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 3)
   
5.       x.Value = ExecuteExcel4Macro("'X:\­·ºÞ³øªí\¥æ´«³¡¦ìµû»ùªí\[IRS³¡¦ìµû»ùªí" & x.Offset(, -3).Value & ".xls]³¡¦ìµû»ùªí'!R1C13")
   
6.     Next
   
7.   End With
   
8. End Sub

½Æ»s¥N½X

jasonwu0114

¦^´_ 9# stillfish00

stillfish00¤j¤j
¬Oªº¨S¿ù
«D±`·PÁÂ
¾Ç¨ì¤£¤ÖªF¦è