[µo°Ý] §ó·s¤U¸ü³t«×¡B¦s¨ú°ÝÃD

spermbank

1.¥¨¶°°ÝÃD:
   °ÝÃD:¦]¬°­n±µ¦¬DDE¸ê®Æ¡A¦ý¬ODDE¸ê®Æ(¤¸¤jyewwin)¤£¯à¤@¦¸¤j¶q¤U¸ü
            ©Ò¥H§Ú¼g¤F¨â­Ó¥¨¶°«ö¶s¡B¤À§O¥h§ó·sDDE¸ê®Æ¡A¦ý¬Oı±o­n«ö¨â¦¸¥¨¶°«ö¶s¡Aı±o«Ü³Â·Ð¡C
   ½Ð°Ý:¦p¦ó¨Ï¥¨¶°1¤¤Â_10¬í«á(«D©µ¿ð10¬í)¡B«Ý§Q¥Îyes.exe(¶}±Òexcel¦Û°Ê´N·|«ùÄò§ó·s¡B¦ý°õ¦æ¥¨¶°¨S¿ìªk)§ó·s¸ê®Æ§¹²¦¡A¦bÄ~Äò°õ¦æ¥¨¶°2©O?

2.¦s¨ú°ÝÃD:(³o¬Oºô¸ô¤Wµ{¦¡)¡A¥tÃþ¦ü½Ñ¦p¦¹Ãþexcel vba¦³¨S¦³¥i±ÀÂ˪º®ÑÄy¡C

   °ÝÃD:¦ba.workbooks.open ³o¤@¦æ¥X²{»yªk¿ù»~¡A½Ð°Ý§Ú­n¦p¦ó­×§ï??

Private Sub Form_Load()
Set a = CreateObject("excel.application")
sdate = "2007/4/20" '¶}©l¤é´Á
edate = "2007/4/21" '²×¤î¤é´Á
save_file_name = "C:\test.csv" '¦sÀÉÀɦW

'¥Îexcel¨Ó¦sÀÉ
a.Workbooks.Open "http://www.taifex.com.tw/chinese/3/3_1_2dl.asp?syear=" & Year(sdate) & "&smonth=" & Month(sdate) & "&sday=" & Day(sdate) & "&eyear=" & Year(edate) & "&emonth=" & Month(edate) & "&eday=" & Day(edate) & "&COMMODITY_ID="
a.DisplayAlerts = False
a.ActiveWorkbook.SaveAs save_file_name, 6, False '¦s¦¨csv
'­nª½±µ¬Ý¥i¥H²¤¹LÃö³¬ªº«ü¥O!!!
a.quit
Set a = Nothing
Shell "explorer.exe c:\", vbMaximizedFocus
End
End Sub


3.Àx¦s®æcells(1,1) ="2000/01/31"¤é´Á®æ¦¡
§Ú­n¦p¦ó§Q¥Îx,y,z¤À§O®»2000¡B01¡B31µ¥¤T²Õ¼Æ¦r(®³±¼/©O?)

GBKEE

¦^´_ 1# spermbank
1

1.   Sub ¥¨¶°1()
   
2.     '
   
3.     '
   
4.     Application.OnTime Now + TimeValue("00:00:10"), "¥¨¶°2"
   
5. End Sub

½Æ»s¥N½X

2   2003ª© ¤¤§ä¤£¥X¿ù»~

3  X = Year(Cells(1, 1))
    Y = Month(Cells(1, 1))
   Z = Day(Cells(1, 1))
'''''''''''''''''''''''''
   A = Split(Cells(1, 1), "/")
   X = A(0)
   Y = A(1)
   Z = A(2)

spermbank

1.3¸Ñ¨M ·PÁ¤j¤j
¥i¬O²Ä¤G­Ó°ÝÃD¡A§ÚÁÙ¬O¸Ñ¨M¤£¥X¨Ó¡A¥H¤U¬O§Úªºµ{¦¡½X
§Ú¬O¥Îexcel 2007
Sub Download()

    Set f = CreateObject("excel.application")
   
    save_file_name = "C:\test.csv" '¦sÀÉÀɦW
   
    s = "2002"
    i = "00"
    j = "1"
    k = "2010"
    m = "11"
    n = "31"
    o = "2011"
   
    '¥Îexcel¨Ó¦sÀÉ
    f.Workbooks.Open "http://ichart.finance.yahoo.com/table.csv?s=" & s ".TW&a=" & i "&b=" & j "&c=" & k "&d=" & m "&e=" & n "&f=" & o "&g=d&ignore=.csv"
    'http://ichart.finance.yahoo.com/table.csv?s=2002.TW&a=00&b=1&c=2010&d=11&e=31&f=2011&g=d&ignore=.csv
    f.DisplayAlerts = False
    f.ActiveWorkbook.SaveAs save_file_name, 6, False '¦s¦¨csv
    '­nª½±µ¬Ý¥i¥H²¤¹LÃö³¬ªº«ü¥O!!!
    f.Quit
    Set f = Nothing
    Shell "explorer.exe c:\", vbMaximizedFocus
    End
   
End Sub

GBKEE

¦^´_ 3# spermbank
¤Ö¤@­Ó³s±µ²Å¸¹ &
  f.Workbooks.Open "http://ichart.finance.yahoo.com/table.csv?s=" & s ".TW&a=" & i "&b=" & j "&c=" & k "&d=" & m "&e=" & n "&f=" & o  "&g=d&ignore=.csv

F.Workbooks.Open "http://ichart.finance.yahoo.com/table.csv?s=" & s & ".TW&a=" & i & "&b=" & j & "&c=" & k & "&d=" & m & "&e=" & n & "&f=" & o & "&g=d&ignore=.csv"

spermbank

¯uªº«Ü·PÁ¤j¤j¡A¦Û¤v¬Ý³o¦æµ{¦¡¤w¸g¬Ý¹L¦n´X­Ó¤p®É¡A³£¨S¦³µo²{¡A¦p¤µµ{¦¡¯àÄ~Äò©¹¤U¼g¡A¯uªº«Ü¶}¤ß¡A¶W¯Å¶}¤ß¡AÁÂÁ¡C

spermbank

1:°õ¦æ [©Ò¦³¾ú¥vªÑ»ù«ö¶s] ¦s¦¨.csvÀÉ¡Aµo²{¦s¨úÀɮ׳t«×«D±`ºC
  °Ý:¦p¦ó­×§ïµ{¦¡´£¤É¤U¸ü³t«×?

2:¦p¦óŪ¨ú¥N¸¹1101.csv²Ä1Äæ©Ò¦³¤é´Á(data)¡A¼g¤Jexcel¤¤ªºsheet2ªº²Ä1¦C¤¤¡A¨Ã¥BŪ¨ú©Ò¦³*.csvÀɮפ¤ªº²Ä5Äæ(close)ªº¸ê®Æ¡A¨Ì§Ç¹ï·ÓÀɦW»P²Ä1Ä椤ªº¥N¸¹±N²Ä5Äæ¸ê®Æ¼g¤J¦U¥N¸¹ªº¦C¦ì¤¤.
  ¦¹°ÝÃD¦³ª¦¤å´M§ä¬ÛÃö°ÝÃD¸Õ¼g¡A¥i¬OÁÙ¬O¼g¤£¥X¨Ó¡A·Q½Ð°Ý¦U¦ì¤j¤j¡C

  PS:ªþ¥[:ªþ¥ótext1 PS:ÁÙ¬O¤p¾Ç¥Í¡A¥i½Ð¤j¤j¼g§¹¡Aµ{¦¡½X¥Î¶K¡AÁÂÁ¡C

GBKEE

¦^´_ 1# spermbank
­n¶}Ãö 1298­ÓÀÉ®× ³t«×§Ö¤£¤F

1. Sub «ö¶s3_Click()
   
2.     With ThisWorkbook.Sheets("Sheet1")
   
3.         .Range("H" & 11).Formula = "§ó·s¤¤..."
   
4.         ii = .Cells(6, 6) - 1 '°_©l¤ë
   
5.         j = .Cells(7, 6) '°_©l¤é
   
6.         k = .Cells(5, 6) '°_©l¦~
   
7.         m = .Cells(6, 8) - 1 '²×¤î¤ë
   
8.         n = .Cells(7, 8) '²×¤î¤é
   
9.         o = .Cells(5, 8) '²×¤î¦~
   
10.         h = .Cells(9, 6) '¦sÀɦì¸m
    
11.         Application.ScreenUpdating = False       '°±¤î¿Ã¹õ§ó·s
    
12.         For i = 2 To Application.CountA(.Range("A:A")) 'Äæ¦ì¦³­È½d³ò­pºâ
    
13.             symbol = .Cells(i, 1)
    
14.             save_file_name = h & symbol & ".csv" '¦sÀÉÀɦW
    
15.             If .Range("C" & i).Formula = "¥«" Then
    
16.                 '¥Îexcel¨Ó¦sÀÉ
    
17.                 Workbooks.Open "http://ichart.finance.yahoo.com/table.csv?s=" & symbol & ".TW&a=" & ii & "&b=" & j & "&c=" & k & "&d=" & m & "&e=" & n & "&f=" & o & "&g=d&ignore=.csv"
    
18.             Else
    
19.                 Workbooks.Open "http://ichart.finance.yahoo.com/table.csv?s=" & symbol & ".TWO&a=" & ii & "&b=" & j & "&c=" & k & "&d=" & m & "&e=" & n & "&f=" & o & "&g=d&ignore=.csv"
    
20.             End If
    
21.             With ActiveWorkbook   'Àɮ׶}±Ò«á¦¨¬°§@¥Î¤¤ªº¬¡­¶Ã¯
    
22.                 .SaveAs save_file_name, 6, False '¦s¦¨csv
    
23.                 .Close False
    
24.             End With
    
25.         Next
    
26.         .Range("H" & 11).Formula = "§ó·sµ²§ô"
    
27.     End With
    
28.     Application.ScreenUpdating = True     '¿Ã¹õ§ó·s
    
29. End Sub

½Æ»s¥N½X

spermbank

¦^´_ 7# GBKEE


  ¤j¤j§A¦n:
         ªþ¥[Àɮ׬O§Ú¦bºô¸ô¤W§ä¨ìªºÃþ¦üÀɮסAµo²{¥¦°õ¦æªº³t«×¡Aªº½T§Ö«Ü¦h¡Aµ¹±z°Ñ¦Ò¤@¤U¡C
  ¬Ý¬Ý¬O§_¦³¤@¨ÇÆF·P¡A¥i¥HÅýµ{¦¡³t«×Åܱo§Ö¤@¨Ç(¥i¬O¦¹ÀÉ©|¥¼¶}©ñ¥¨¶°µ{¦¡½X)¡C
        ÁÙ¦³¦A°õ¦æ¦¹µ{¦¡®É¡A¥X²{¤@­Óµøµ¡"Ápµ¸¦øªA¾¹¥H¨ú±o¸ê°T"¡A¬O§_¥i¥H¤£Åã¥Ü©Î­×§ïµ{¦¡¡A¦]¬°°£¤F©ìºCexcel°õ¦æ³t«×¤§¥~¡AÅý¹q¸£µLªk±q¨Æ¨ä¥L¤u§@¡C
¥t¥~:
       ¥i§_½Ð¤j¤j¨ó§U¸Ñ¨M²Ä¤G­Ó°ÝÃD¡AÁÂÁ¡C

       ¯uªº«ÜÁÂÁ¤j¤j¦^À³§Ú^^

GBKEE

¦^´_ 8# spermbank
¦Û¤v´ú¸Õ¬Ý¬Ý

1. Sub «ö¶s3_Click()
   
2.     Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
   
3.     With ThisWorkbook.Sheets("Sheet1")
   
4.         .Range("H" & 11).Formula = "§ó·s¤¤..."
   
5.         ii = .Cells(6, 6) - 1 '°_©l¤ë
   
6.         j = .Cells(7, 6) '°_©l¤é
   
7.         k = .Cells(5, 6) '°_©l¦~
   
8.         m = .Cells(6, 8) - 1 '²×¤î¤ë
   
9.         n = .Cells(7, 8) '²×¤î¤é
   
10.         o = .Cells(5, 8) '²×¤î¦~
    
11.         h = .Cells(9, 6) '¦sÀɦì¸m
    
12.         For i = 2 To Application.CountA(.Range("A:A")) 'Äæ¦ì¦³­È½d³ò­pºâ
    
13.             symbol = .Cells(i, 1)
    
14.             save_file_name = h & symbol & ".csv" '¦sÀÉÀɦW
    
15.             If .Range("C" & i).Formula = "¥«" Then
    
16.                 '¥Îexcel¨Ó¦sÀÉ
    
17.                 myURL = "http://ichart.finance.yahoo.com/table.csv?s=" & symbol & ".TW&a=" & ii & "&b=" & j & "&c=" & k & "&d=" & m & "&e=" & n & "&f=" & o & "&g=d&ignore=.csv"
    
18.             Else
    
19.                 myURL = "http://ichart.finance.yahoo.com/table.csv?s=" & symbol & ".TWO&a=" & ii & "&b=" & j & "&c=" & k & "&d=" & m & "&e=" & n & "&f=" & o & "&g=d&ignore=.csv"
    
20.             End If
    
21.             WinHttpReq.Open "GET", myURL, False
    
22.             WinHttpReq.Send        '
    
23.             myURL = WinHttpReq.ResponseBody
    
24.             If WinHttpReq.Status = 200 Then
    
25.                 With CreateObject("ADODB.Stream")
    
26.                     .Open
    
27.                     .Type = 1
    
28.                     .Write WinHttpReq.ResponseBody
    
29.                     .SaveToFile (save_file_name)
    
30.                     .Close
    
31.                 End With
    
32.             End If
    
33.         Next
    
34.         .Range("H" & 11).Formula = "§ó·sµ²§ô"
    
35.     End With
    
36. End Sub

½Æ»s¥N½X

spermbank

¦^´_ 9# GBKEE

³t«×§Ö¶W¦h¡A¤j·§8-10¤ÀÄÁ¡A1¤d¦hµ§¬~­Ó¾þ´N¶]§¹^^ ¯uªº¬O¤Q¤À·PÁ¤j¤j
¥i¬O1341µ§¤¤¥u¤U¸ü1255µ§¡A¤£ª¾¹D¬O¥N¸¹­«½Æ©Îmiss©Îºô­¶µL¦¹¸ê®Æ¡C§Ú¦A¨Ó§ä§ä¬Ý¿ù»~¦b­þ¸Ì
¥t¥~:

¦¹µ{¦¡¬°¦A²ÄCÄæ¦ì¤¤¦³"Âd"³o¦r²´¡A´N§R°£¸ÓÄ檺²ÄA¦ÜC¦C
¥i¬O¶]¦¹µ{¦¡µo²{¡A«o»Ý­n°õ¦æ¦h¦¸¡A¦³"Âd"³o¦r²´ªº²ÄA¦ÜC¦C¤~·|³Q§R°£
¥i¬Oµ{¦¡¤£¬O°õ¦æ¤@¦¸´N¥i¥H¦¨¥\¡A«o­n°õ¦æ¦h¦¸¤~¥i¡C
¹ê¦b¬O§ä¤£¥X¿ù»~¡A¤£¹L§ÚÃhºÃ¬O¤£¬O§Ú¤£¼ô±xdelet©Îselectªº­­¨î¡AÁÙ¬O³t«×°ÝÃD
½Ð¤j¤j«ü±Ð¡C

(¥H¤U¬Oµ{¦¡½X¡A¥t¦³ªþ¥[ÀÉ®×)
Sub «ö¶s6_Click()

    Sheets("Sheet1").Select
    x = Application.WorksheetFunction.CountA(Range("A:A")) 'Äæ¦ì¦³­È½d³ò­pºâ
    For i = 2 To x
        If Range("C" & i).Formula = "Âd" Then
           Range("A" & i, "D" & i).Select
          Selection.Delete Shift:=xlUp
        End If
    Next
End Sub