ªð¦^¦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)

¦^´_ 14# sp218882


    ¶â¶â¡AÁÂÁ§A^^

TOP

½Ð°Ý
¥»®×»P  http://forum.twbts.com/thread-4906-1-1.html  ¬O§_
¦³¬Û¦P¤º®e¡A©Î³\¥i¨Ñ°Ñ¦Ò

TOP

¥H¥«³õ¤Woffice¬Û®e³nÅé¡]Open Office¡ÄCalc ¡^¥´¶}Àɮ׫á¡A§Y¥i¬Ý¨ìµ{¦¡½X
¤]´N¬OMS Office Excel ±K½X¥\¯à¹ï¬Û®e³nÅé¡]Open Office¡^¬O¨S¦³¥Îªº
§Ú¹Á¸Õ¥´¶}   Àò§Q«ü¼Ð.xls ¬Ý¨ì¥¨¶°«ü¥O¦p¤U
Rem Attribute VBA_ModuleType=VBAModule
Sub Module1
Rem Sub §ó·s­ÓªÑ¸ê®Æ()
Rem     On Error Resume Next
Rem     Dim StockNum As String
Rem 0
Rem     StockNum = Application.InputBox("½Ð¿é¤J­ÓªÑ¥N¸¹")
Rem     If StockNum <> "" Then
Rem     If Asc(StockNum) >= 48 And Asc(StockNum) <= 57 Then GoTo 1 '§P©w¬°¼Æ¦r´N¶i¦æ
Rem     End If
Rem     If StockNum = "False" Then GoTo 2   '­Y¨ú®ø«h°h¥X
Rem     MsgBox ("½Ð¿é¤J­ÓªÑ¥N¸¹")
Rem     GoTo 0
Rem 1
Rem     Sheets("IS").Select
Rem     With Selection.QueryTable
Rem         .Connection = "URL;http://dj.mybank.com.tw/z/zc/zcq/zcqa/zcqa_" & StockNum & ".asp.htm"
Rem         .WebSelectionType = xlSpecifiedTables
Rem         .WebFormatting = xlWebFormattingNone
Rem         .WebTables = "3"
Rem         .WebPreFormattedTextToColumns = True
Rem         .WebConsecutiveDelimitersAsOne = True
Rem         .WebSingleBlockTextImport = False
Rem         .WebDisableDateRecognition = False
Rem         .WebDisableRedirections = False
Rem         .Refresh BackgroundQuery:=False
Rem     End With
Rem     Selection.QueryTable.Refresh BackgroundQuery:=False
Rem
Rem     Sheets("ISQ").Select
Rem     With Selection.QueryTable
Rem         .Connection = "URL;http://dj.mybank.com.tw/z/zc/zcq/zcq_" & StockNum & ".asp.htm"
Rem         .WebSelectionType = xlSpecifiedTables
Rem         .WebFormatting = xlWebFormattingNone
Rem         .WebTables = "3"
Rem         .WebPreFormattedTextToColumns = True
Rem         .WebConsecutiveDelimitersAsOne = True
Rem         .WebSingleBlockTextImport = False
Rem         .WebDisableDateRecognition = False
Rem         .WebDisableRedirections = False
Rem         .Refresh BackgroundQuery:=False
Rem     End With
Rem     Selection.QueryTable.Refresh BackgroundQuery:=False
Rem     
Rem     Sheets("«ü¼Ð¹Ïªí").Select
Rem 2
Rem End Sub
Rem
Rem
End Sub

TOP

¦^´_ 11# Hsieh


    ÁÂÁ¤j¤j¡A§Ú¦A·Q·Q¡A½T¹ê¦³³o¤u§@ï¡A°ÆÀɦW§Ú¤]¨S§Ë¿ù¡C
    ·PÁ¤j¤j«ü±Ð^^

TOP

¦^´_ 10# spermbank

Ãö©óµ{¦¡½X¥X¿ù
½ÐÀˬdWorkbooks("Àç·~¦¬¤JÅÜ°Ê.xls").Sheets("Revenue")    ¦¹¤u§@ªí¬O§_¦s¦b?(ª`·N¤u§@ï°ÆÀɦW¬O§_¥¿½T)
¾Ç®üµ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

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

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

¥»©«³Ì«á¥Ñ 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

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD