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

[µo°Ý] ·j´M¬Y¸ê®Æ§¨¤¤Àɮתº¼Æ¶q¡A¨C¼W¥[¤@µ§¸ê®Æ´N¦¬¿ý¶i¨Ó

[µo°Ý] ·j´M¬Y¸ê®Æ§¨¤¤Àɮתº¼Æ¶q¡A¨C¼W¥[¤@µ§¸ê®Æ´N¦¬¿ý¶i¨Ó

¬Y­Ó³nÅé¨C¬í¦b"D:\raw data\"¸ê®Æ§¨¤¤Àx¦s¤@­ÓExcelÀÉ®×
ÀɮצWºÙ¨Ì§Ç±q ResultsFile1¡BResultsFile2¡BResultsFile3¡B....¡BResultsFile10¡BResultsFile11¡B...
§Ú·Q­n«Ø¤@­Ó"Record.xls"  ¥s¥L¦Û°Ê°»´ú³o­Ó¸ê®Æ§¨¬O§_¦³·sªºExcelÀɶi¨Ó
¨C·í¦³·sÀÉ®×´N§â¥L¥´¶}¦s¨úCells(2,3)ªº­È¶i¤J"Record.xls" ¤¤
½Ð°Ý¸Ó¦p¦ó¼gVBA¥s¥L¦Û°Ê°»´ú?
·PÁÂ

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-7-20 09:20 ½s¿è

¦^´_ 1# sweetkitty
©Î³\ÁÙ¦³¨ä¥L¿ìªk(¦p¶}³qDDE³q¹D)
¤ñ¸û¤£ªá¸£µ¬ªº§@ªk
§Q¥ÎONTIME¤èªk¡A¨C¬í¥h¶]¸ê®Æ§¨¤ºªºÀɮצWºÙ
µM«á°O¿ý¦bA¡BBÄ椺¡A´N¯à§PÂ_¬O§_¦³·s¼WÀÉ®×
¤@¯ë¼Ò²Õ
  1. Sub auto_open()
  2. fd = "D:\raw data\" '¸ê®Æ§¨
  3. fs = Dir(fd & "*.xls")
  4. Set sht = ThisWorkbook.Sheets(1)
  5. Do Until fs = ""
  6. If IsError(Application.Match(fs, sht.Columns("A"), 0)) Then
  7. With Workbooks.Open(fd & fs)
  8. sht.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(fs, .Sheets(1).Cells(2, 3).Value)
  9. .Close 0
  10. End With
  11. End If
  12. fs = Dir
  13. Loop
  14. Application.OnTime Now + TimeValue("00:00:01"), "auto_open"
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


    H¤j¤j~ ³o¼Ëªº¤è¦¡~
   ¤£¬O¤]¤@©w­n¥´¶}excel¤~¯à°÷¦Û°Ê¥h·j´M¹À??
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 2# Hsieh
·PÁÂH¤j¦^µª¡A¤£¹L§Ú°õ¦æªº®É­Ô¥X²{"µLªk°õ¦æ¥¨¶°"ªºµøµ¡
Loop¤§«eªº°Ê§@³£°õ¦æ§¹²¦¤F¡A¬O³Ì«á¤@¦æApplication¨S¿ìªk°µ
½Ð°Ý¦³¤°»ò§Ú¨Sª`·Nªº¦a¤è¶Ü?
ÁÂÁÂ!!

auto open.JPG (22.7 KB)

auto open.JPG

TOP

¥»©«³Ì«á¥Ñ sweetkitty ©ó 2012-7-24 17:15 ½s¿è

¦^´_ 2# Hsieh

·PÁÂH¤j¤j!  §Úµo²{§âµ{¦¡½X©ñ¦b"¼Ò²Õ"¸Ì­±´N¥i¥H°õ¦æ¤F
¥t¥~§Ú­×§ï¦¨§Ú­nªº¼Ë¤l¡Aµo²{±z­ì¨Óªºµ{¦¡½X¶]·|¦Û°Ê§ì"¦³·s¼WªºÀÉ®×"¨ú­È
¦ý§Úªºµ{¦¡½X·|¤@ª½¤£Â_ªº±q²Ä¤@­ÓÀɮ׶}©lrepeat
½Ð°Ý¬O­þ­Óµ{¦¡½X¨S§ï¨ì©O?
·PÁÂ
  1. Sub ¦Û°Ê¾ã²z¸ê®Æ()

  2. fd = "D:\Wayne\" '¸ê®Æ§¨
  3. fs = Dir(fd & "ResultsFile*")
  4. Set sht = ThisWorkbook.Sheets(2)
  5. Do Until fs = ""
  6. If IsError(Application.Match(fs, sht.Columns("A"), 0)) Then
  7. With Workbooks.Open(fd & fs)
  8. sht.Cells(Rows.Count, 1).End(xlUp).Offset(1) = Mid(Workbooks(fs).Name, 12)
  9. sht.Cells(Rows.Count, j * 2).End(xlUp).Offset(1) = Workbooks(fs).Sheets(1).Cells(23, j * 2 + 1).Value
  10. .Close 0
  11. End With
  12. End If
  13. fs = Dir
  14. Loop

  15. Application.OnTime Now + TimeValue("00:00:01"), "¦Û°Ê¾ã²z¸ê®Æ"

  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# sweetkitty

§Úµo²{§â¤W­±¨º­Óµ{¦¡½Xªº²Ä9¦æ§ï¦¨¥H¤U³o¼Ë¡A´N¤£·|µo¥Í­«½Æ¶ñ¤J¸ê®Æªºª¬ªp¤F
  1.         sht.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(fs, .Sheets(1).Cells(2, 3).Value)
½Æ»s¥N½X
¦ý¬O§ÚÁÙ¬O¤£¤Ó©ú¥Õ­ì²z¡A§Æ±æ¥i¥H¸Ñ´b
·PÁÂ

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD