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

[µo°Ý] ÀɮפU¸ü°ÝÃD

[µo°Ý] ÀɮפU¸ü°ÝÃD

¥»©«³Ì«á¥Ñ spermbank ©ó 2011-10-1 09:32 ½s¿è

¦U¦ì¤j¤j¦n:
°ò¥»­±¸ê®Æ§¨¤¤¦³12­ÓÀɮסA¥i¬O¨C­ÓÀɮײÄ1­Ósheet1¦³­Ó«ö¶s"¿é¤J­ÓªÑ"¥¼¶}©ñ¥¨¶°µ{¦¡½X¡A¥B«ö¤U«ö¶s«á·|½Ð¨D"½Ð¿é¤J­ÓªÑ¥N¸¹"¡A§Ú·Q±q§Ú¦Û¤v³]­pªº"­ÓªÑ°ò¥»­±¤ÀªR.xls"¤¤ªº¤u§@¦C-­º­¶1..
·s¼W
    1¡B«ö¤U"¤U¸ü©Ò¦³ªÑ²¼°ò¥»­±¸ê®Æ"·|±N­º­¶1ªº©Ò¦³¥N¸¹¤¤ªº¸ê®Æ¡A¨Ì§Ç¦s¤JÀɮ׸ô®|¤¤D:\data\(ÀɦW¬°¥N¸¹)
          (¨ä¤¤¡A©Ò¦³¥N¸¹¤¤ªº¸ê®Æ¤º®e¥]§t:­·ÀIµû¦ô.xls§â§ó·s¹L«áªº¤u§@¦C-IS¡BISQ¡BBS¡BBSQ¡BBASIC¡BYrPrice¡BFR¡BCFS¡BISQT¤ÎÀò§Q«ü¼Ð.xls§â§ó·s¹L«áªº¤u§@¦C-Revenue)
    ¦]¬°°ò¥»­±¸ê®Æ§¨¤¤¦³12­ÓÀÉ®×¥¼¶}©ñ¥¨¶°µ{¦¡½X¡A©Ò¥H·Q½Ð°Ý¤j®a¤ÎG¤j³o­n«ç»ò¼g¡C

    2¡B«ö¤U"¶×¤J­ÓªÑ¸ê®Æ"·|¸õ¥Xµøµ¡Åã¥Ü"½Ð¿é¤J­ÓªÑ¥N¸¹"¨Ã¥Bª½±µ§ó·s°ò¥»­±¸ê®Æ§¨¤¤12­ÓÀɮסA¦A§â¸ê®Æ§¨¶×¤J§Ú¦Û¤vªº"­ÓªÑ°ò¥»¤ÀªR.xls"¤¤ªº¦U¤u§@¦Cªí.
         (Á|¨Ò:±q­·ÀIµû¦ô.xls§â§ó·s¹L«áªº¤u§@¦C-IS¡BISQ¡BBS¡BBSQ¡BBASIC¡BYrPrice¡BFR¡BCFS¡BISQT¶×¤J¹ïÀ³ªº¤u§@¦Cªí¤¤...)
PS:test.rar¤¤°ò¥»­±¸ê®Æ§¨¬°ºô¸ô·j´M¡A­ÓªÑ°ò¥»¤ÀªR¡A¤À¨Éµ¹¦³¬ã¨sªÑ²¼ªº½×¾ÂªB¤Í¡C

test.rar (502.82 KB)

½Ð°Ý¤j¤j¥i¤£¥i¥H¥ýÀ°§Ú³B²z¤@¤U²Ä1­Ó°ÝÃD¡A¦]¬°12­ÓÀÉ®×¥¼¶}©ñ¥¨¶°µ{¦¡½X¡A­n12­ÓÀɮקó·s12¦¸¯uªº«Ü³Â·Ð¡A·Q»¡¥Î¤@­Óexcel¦A§â12Àɮצê³s°_¨Ó¡A¥u­n§ó·s1¦¸¡A¨Ã¥B¤U¸ü©Ò¦³¥N¸¹¦sÀÉ¡A³t«×¡A¤°»ò³£·|Åܫܧֻ¡¡C

TOP

½Ð°Ý¤@¤U:
      ¨º§Ú­n«ç»ò¦A§Ú¦Û¤vªºexcelÀÉ¡A¥h°õ¦æ¨ä¥Lexcelªº¥¨¶°(¥¼¶}©ñµ{¦¡½X)©O?

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-10-2 17:48 ½s¿è

¦^´_ 3# spermbank

¬Ý¤£À´§Aªº¾ãÅé¬yµ{
§A¬O­n§â12­ÓÀÉ®×¥þ³¡¥H¤@ ­Ó¤½¥q¥N½X§¹¦¨§ó·s«á¡A¦A±N­·ÀIµû¦ô»PÀò§Q«ü¼Ð2­ÓÀɮפºªº¨º¨Ç¤u§@ªí¥H¥N¸¹¬°¦W¥t¦s·sÀɶÜ?
¦ý¤£¬O12­ÓÀɮ׳£¦³"¸ü¤J­ÓªÑ¸ê®Æ"ªºµ{¦¡½Xªü
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ spermbank ©ó 2011-10-3 02:13 ½s¿è

¦^´_ 4# Hsieh


    ¬°¤°»ò¥i¥H¬Ý¨ì><"
    §Ú¬O¥Î2007ª©excel
    ·Q¬Ýµ{¦¡½X¡A¥i¬O«o¥X²{"±M®×µLªkÀ˵ø"

§Ú¤w¸g§â¦w¥þ©Ê½Õ¦¨:±Ò¥Î©Ò¦³¥¨¶°(¤£«Øij¨Ï¥Î¡F·|°õ¦æ¦³¼ç¦b¦MÀIªºµ{¦¡½X)
                                     «H¥ô¦s¨úVBA±M®×ª«¥ó¼Ò«¬

     ¤´µM¤£¦æ»¡
     ½Ð¤j¤j«ü±Ð

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-10-3 21:38 ½s¿è
¦^´_  Hsieh
·Q¬Ýµ{¦¡½X¡A¥i¬O«o¥X²{"±M®×µLªkÀ˵ø"
spermbank µoªí©ó 2011-10-3 01:39

±à...
¨º´N¬OÀɮצ³³]©w«OÅ@ -> ­ì³]­pªÌ "¤£·QÅý§O¤H¬Ý¨ìµ{¦¡½X",
¦Ó§A·Q±q¨ä¥LÀÉ®×¥h©I¥s¸Ó«ö¶sªº Click µ{§Ç¤]¨S»³(¥u­n¥L¬O³]¦¨ Private),
«Øij§AÁÙ¬O¥h§ä¥»¤H°Ý¤ñ¸û¾A¦X.

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-10-3 22:53 ½s¿è

¦^´_ 5# spermbank

§Ú¤£µ½©ó¯}¸Ñ
¦ý¨Ì§A±Ô­z©Î³\¥i¸Õ¸Õ¦b­ÓªÑ°ò¥»­±¤ÀªR.xls¤@¯ë¼Ò²Õ
°õ¦æ§ó·s¥Dµ{§Ç¸Õ¸Õ
  1. Sub §ó·s¥Dµ{§Ç()
  2. With ThisWorkbook
  3. For Each a In .Sheets(1).[A2:A1341] '¨C­Ó¥N¸¹´`Àô
  4.   §ó·s¸ê®Æ a '°õ¦æ12Àɮקó·s
  5.   Workbooks("­·ÀIµû¦ô.xls").Sheets(Array("IS", "ISQ", "BS", "BSQ", "BASIC", "YrPrice", "FR", "CFS", "ISQT")).Copy '½Æ»s¤u§@ªí
  6.   ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & CStr(a) & ".xls" '¥t¦s·sÀÉ
  7.   Workbooks("Àç·~¦¬¤JÅÜ°Ê.xls").Sheets("Revenue").Copy Before:=Workbooks(CStr(a) & ".xls").Sheets(1) '½Æ»s¤u§@ªí
  8.   ÃöÀÉ
  9. Next
  10. End With
  11. End Sub
  12. Sub §ó·s¸ê®Æ(a)
  13. Dim Sh As Worksheet, MyURL$, MyQy As QueryTable
  14. With ThisWorkbook
  15. fd = .Path & "\°ò¥»­±\"
  16. fs = Dir(fd & "*.xls")
  17. Do Until fs = ""
  18. With Workbooks.Open(fd & fs)
  19.    For Each Sh In .Sheets
  20.       With Sh
  21.       If .QueryTables.Count > 0 Then
  22.          Set MyQy = .QueryTables(1)
  23.          With .QueryTables(1)
  24.          MyURL = .Connection
  25.          If InStr(MyURL, "StockID") > 0 Then
  26.             k = Val(Split(MyURL, "=")(UBound(Split(MyURL, "="))))
  27.             Else
  28.             k = Val(Split(MyURL, "_")(1))
  29.          End If
  30.          MyURL = Replace(MyURL, k, a)
  31.          .Connection = MyURL '§ó§ï¬d¸ß
  32.          .BackgroundQuery = False '¹õ«e§ó·s
  33.          .Refresh '§ó·s
  34.          End With
  35.       End If
  36.       End With
  37.    Next
  38. End With
  39. fs = Dir()
  40. Loop
  41. End With
  42. End Sub
  43. Sub ÃöÀÉ()
  44. For Each w In Windows
  45. If w.Caption <> ThisWorkbook.Name Then w.Close 1
  46. Next
  47. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


              Workbooks("Àç·~¦¬¤JÅÜ°Ê.xlsm").Sheets("Revenue").Copy Before:=Workbooks(CStr(a) & ".xlsx").Sheets(1) '½Æ»s¤u§@ªí
¦b°õ¦æµ{¦¡®É³o¤W­z¨â¦æµ{¦¡®É¡A¥X²{"ClassWorksheetªºCopy¤èªk¥¢±Ñ"
¤£ª¾¹D¦p¦ó¸Ñ¨M¡A½Ð¤j¤j«üÂI¡C
¥t:
      ¤j¤j¦^´_ªº°ÝÃD¤¤
     ¦ý¤£¬O12Àɮפ¤³£¦³"¿é¤J­ÓªÑ¸ê®Æ"ªºµ{¦¡½X
     ¬O¤£¬O­n¥Î2003ª©¥»¶}´N¥i¥H¬Ý¨ì©O?
     ÁÙ¬O¥u¦³¯}¸Ñ¤@³~©O?

TOP

¦^´_ 8# spermbank
­n¥t¦s¦¨xlsxÀɮסA«e­±ªº¦sÀÉ´N­n¤@°_§ï¦¨xlsx
¤§«e´£¨ìªº¤£¬O¨C­ÓÀɮ׳£¦³¸Óµ{§Ç¡A¬O¦]¬°§Ú´ú¸Õ®É¨S¦³¶}©ñ¥¨¶°¾É­P»~¸Ñ
­ì§@ªÌ¥H«ö¶sÅX°Ê¼Ò²Õ¤ºµ{§Ç¡A¥i§Q¥Î½Æ»s¤u§@ªí¡A´N¯à¬Ý¨ì¤u§@ªí¼Ò²Õ¤ºµ{¦¡½X
¦ý¬O­Y­n¬Ý¨ì¥þ³¡µ{¦¡½X´N¥²¶·¯}¸Ñ¡Aªp¥B¨C­ÓÀÉ®×µ{¦¡½X¬O¥HInputBox¿é¤J¥N½X
©Ò¥H¡A­n¨C­ÓÀɮ׭קïµ{¦¡½X¤£¦p­«·s¼¶¼g
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ spermbank ©ó 2011-10-5 14:04 ½s¿è

¦^´_ 9# Hsieh
H¤j§A¦n:
§Ú±Nµ{¦¡½X§ï¦¨
    With ThisWorkbook
    For Each a In .Sheets(1).[A2:A1341] '¨C­Ó¥N¸¹´`Àô
      Application.ScreenUpdating = False       '°±¤î¿Ã¹õ§ó·s
      §ó·s¸ê®Æ a '°õ¦æ12Àɮקó·s
      Workbooks("­·ÀIµû¦ô.xls").Sheets(Array("IS", "ISQ", "BS", "BSQ", "BASIC", "YrPrice", "FR", "CFS", "ISQT")).Copy '½Æ»s¤u§@ªí
      ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & CStr(a) & ".xlsx" '¥t¦s·sÀÉ
      Workbooks("Àç·~¦¬¤JÅÜ°Ê.xls").Sheets("Revenue").Copy Before:=Workbooks(CStr(a) & ".xlsx").Sheets(1) '½Æ»s¤u§@ªí
      ÃöÀÉ
    Next
    Application.ScreenUpdating = False       '°±¤î¿Ã¹õ§ó·s
    End With
    ¤´µM¥X²{¤U¦C¿ù»~
    °õ¦æ¶¥¬q¿ù»~ ¡¥1004¡¦ :
    Class WorksheetªºCopy¤èªk¥¢±Ñ

¥t¥~:
    H¤j»¡:­ì§@ªÌ¥H«ö¶sÅX°Ê¼Ò²Õ¤ºµ{§Ç¡A¥i§Q¥Î½Æ»s¤u§@ªí¡A´N¯à¬Ý¨ì¤u§@ªí¼Ò²Õ¤ºµ{¦¡½X¡A§Ú¤£ª¾¹D¦p¦ó¬Ý¨ì¡A¤£¤F¸Ñ¤j¤j·N«ä¡C
    ·Q½Ð±Ð¤j¤j¬O¤£¬O¥i¥H¬Ý¨ì³¡¤Àªºµ{¦¡½X¤¤¬Ý¨ì¤U¸ü§ó·sªººô§}¤Î¬ÛÃöµ{¦¡½X¡B¥i¥H¶Kµ¹§Ú¡A©Î³\§Ú¥i¥H¥ÎG¤j±Ð§Úªº¤U¸ü¡B³t«×°ÝÃD¬ÛÃöµ{¦¡¡A¦pªk¬¶»sªº¼g¼g¬Ý¡C
    ­Y¤£¦æ¦A½Ð¤j¤j¨ó§U¡C

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD