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

EXCEL§ì¨úCSV¸ê®Æ¨Ã§Y®É§ó·s

¤£¶}±ÒÀɮפUŪ¨ú¸ê®Æªº¸ô®|/ÀɦW³]©w¬°ÅܼưÝÃD

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

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ jasonwu0114 ©ó 2013-12-17 16:29 ½s¿è

§ä¨ì¤ñ¸û²³æ¼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
¤p¤Hª«

TOP

¥»©«³Ì«á¥Ñ jasonwu0114 ©ó 2013-12-18 11:35 ½s¿è

¤£¦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
¤p¤Hª«

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-12-18 12:45 ½s¿è

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

TOP

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

   
¤p¤Hª«

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-12-18 18:30 ½s¿è

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

TOP

¦^´_ 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®¦!!
¤p¤Hª«

TOP

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

TOP

¦^´_ 9# stillfish00

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

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD