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

¥Î°j°é§ì¸ê®Æ¶V¶]¶VºC¡A¸Ó¦p¦óÄÀ©ñ°O¾ÐÅé?

¦^´_ 10# GBKEE


·PÁÂG¤jªº¤À¨É

¤£¹L¤p§Ì§ÚÁÙ¬O¤£¤Ó¤F¸Ñ¡A¬°¤°»ò¦bEXCEL¤¤¥Î°j°é¶×¤J¸ê®Æ·|¶V¶×¶VºC....
§Ú¨Ï¥Îªº¬Owin 7¡B64¦ì¤¸¡B6G°O¾ÐÅé
¥­±`¦³²M°£¨t²Î©U§£ªº²ßºD¡A¥B¦b°õ¦æµ{¦¡®É¡A¤£½×CPU©Î¬O°O¾ÐÅé³£Åã¥Ü©|¦³¦h¾lªº¸ê·½(±q¤u§@ºÞ²z­ûÆ[¹î)
ªp¥B¡A¦pªG¬O¦]¬°¨t²Î¸ê·½¤£¨¬©Ò¾É­Pªº°ÝÃD¡AÀ³¸Ó¤£·|¥X²{°õ¦æµ{¦¡ªì´Á³t«×¸û§Ö¡A¦Ó°õ¦æ¨ì«á­±³t«×´N¾ã­Ó©ì¤U¨Óªº±¡ªp¤~¬O
À³¸Ó¬O±qÀY¨ì§À³£«ÜºC¤~¬O
¤£¾å±o§Ú³o¼Ë±À½×¬O§_¥¿½T©O?

TOP

¦^´_ 11# sasho
§A¨Ï¥Îªº¬OEXCEL 2010 ,¥i§_¤W¶Ç§AªºÀÉ®×.
§Ú¨Ó¸Õ¸Õ  XP¡B32¦ì¤¸¡B1G°O¾ÐÅé ,2003ª©¥»ªº³t«×
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 12# GBKEE


·PÁÂGª©¤jªºÀ°¦£¡A¦]¬°§ÚµLªk¤W¶ÇÀɮסA©Ò¥H§âÀɮשñ¦b¦p¤Uªºdropboxªººô§}
¥t¥~¡A³Ìªñ´X¤Ñ´ú¸Õªºµ²ªG¡Aµo²{°ÝÃD¬O¥X¦b¨â­Ó³¡¤À
¤@­Ó¬O¶×¤Jºô­¶¸ê®Æ¡A¥t¤@­Ó¬O±N¶×¤J¸ê®Æ¥t¦s¦¨csvÀÉ
®Ú¾Ú°O¿ýªº¸ê®Æ¡A³o¨â­Ó°Ê§@³£·|ÀHµÛ°j°é¶V¶]¶V¦h¦Ó¶V¨Ó¶VºC
ÁÙ½ÐGª©¤j«üÂI¤@¤U¡AÁÂÁÂ

https://www.dropbox.com/s/n0ycnc7q4gqtbrb/%E6%8A%93%E5%8F%96%E4%B8%8A%E5%B8%82%E8%B3%87%E6%96%99.xls

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-6-25 13:39 ½s¿è

¦^´_ 13# sasho
§A­ì¥»ªº Sub °õ¦æ(),¦b§ÚªºPC¨S¦³§A©Ò»¡ªº¶V¨Ó¶VºCªº±¡§Î.³t«×»PMain() ªº°O¿ýÀÉ®t¤£¦h.

¾ã²z¤@¤U,ªþ¤W Sub Main() ªº°O¿ýÀÉ

Ex.rar (10.87 KB)
  1. Option Explicit
  2. Dim IE As Object, Query_Sh As Worksheet, CsvPath As String, SaveDate As String
  3. Dim t As Date, StartTime As Date, °O¿ýÀÉ As String, stockid As Range, spListCount As Integer
  4. Sub Main()
  5.     Dim i As Integer
  6.     t = Time
  7.     StartTime = Time
  8.     CsvPath = "D:\TSE\"
  9.     ¥Ø¿ý CsvPath
  10.     °O¿ýÀÉ = CsvPath & "Main_Record.TXT"
  11.     If Dir(°O¿ýÀÉ) <> "" Then Kill °O¿ýÀÉ
  12.     ¼È¦s­¶ "temp"
  13.     xRecond 0, "µ{¦¡¶}©l°õ¦æ" & vbCrLf
  14.     Set stockid = Sheets("¤u§@ªí1").Range("A2")
  15.     stockid.Parent.Activate
  16.     Do While stockid <> ""
  17.         Application.ScreenUpdating = True
  18.         stockid.Select
  19.         Application.ScreenUpdating = False
  20.         StartTime = Time
  21.         spListCount = ¸ê®Æ­¶¼Æ
  22.         If spListCount > 0 Then
  23.             i = i + 1
  24.             xRecond i, stockid & vbTab & "¸ê®Æ¶×¤J"
  25.             ¸ê®Æ¶×¤J
  26.             ¾ã²z
  27.             ¦sÀÉ
  28.             xRecond i, stockid.Value & vbTab & "¦sÀɧ¹²¦ " & Format(Time - StartTime, "¦@SS¬í") & vbCrLf
  29.         End If
  30.         Set stockid = stockid.Offset(1)
  31.     Loop
  32.     IE.Quit
  33.     Application.DisplayAlerts = False
  34.     Query_Sh.Delete
  35.     Application.DisplayAlerts = True
  36.     Workbooks.Open °O¿ýÀÉ
  37.     MsgBox "¦@¦s ""(" & i & ") csvÀɧ¹²¦" & vbTab & "¶O®É " & Format(Time - t, "nn¤Àss¬í")
  38. End Sub
  39. Private Sub ¼È¦s­¶(temp As String)
  40.     On Error Resume Next
  41.     Set Query_Sh = Sheets(temp)
  42.     If Err.Number = 9 Then
  43.         Sheets.Add(, Sheets(1)).Name = temp
  44.         Set Query_Sh = Sheets(temp)
  45.     End If
  46. End Sub
  47. Private Sub ¸ê®Æ¶×¤J()
  48.     Dim strURL As String
  49.     strURL = "URL;" & "http://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & stockid & "&FocusIndex=All_" & spListCount
  50.     With Query_Sh
  51.         .UsedRange.Clear
  52.         With .QueryTables.Add(strURL, Query_Sh.[a1])
  53.             .WebFormatting = xlWebFormattingNone
  54.             .WebSelectionType = xlSpecifiedTables
  55.             .WebTables = "5,table2"
  56.             .Refresh 0
  57.             .Delete
  58.         End With
  59.     End With
  60. End Sub
  61. Private Sub ¾ã²z()
  62.     Dim i As Integer
  63.     With Sheets("temp")
  64.         SaveDate = Format(.Range("B1"), "YYYYMMDD")
  65.         With .UsedRange.Range("A:A")
  66.             .SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
  67.             .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  68.         End With
  69.         .UsedRange.Columns("F:J").Cut
  70.         .Range("A" & .Rows.Count).End(xlUp).Offset(1).Insert Shift:=xlDown
  71.         .UsedRange.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo
  72.         .UsedRange.Columns("B:B").Insert Shift:=xlToRight
  73.         .UsedRange.Columns(1) = SaveDate
  74.         .UsedRange.Columns(2) = stockid
  75.          For i = 1 To .UsedRange.Rows.Count
  76.             .Cells(i, 3) = Left(.Cells(i, 3), 4)
  77.             .Cells(i, 5) = .Cells(i, 5).Value / 1000
  78.             .Cells(i, 6) = .Cells(i, 6).Value / 1000
  79.         Next
  80.     End With
  81. End Sub
  82. Private Sub ¥Ø¿ý(xPath As String)
  83.     Dim SP As Variant, P As String, i As Integer
  84.     SP = Split(xPath, "\")
  85.     P = SP(0)
  86.     With CreateObject("Scripting.FileSystemObject")
  87.         For i = 1 To UBound(SP)
  88.             P = P & "\" & SP(i)
  89.             If .FolderExists(P) = False Then .CreateFolder (P)
  90.         Next
  91.     End With
  92. End Sub
  93. Private Sub ¦sÀÉ()
  94.     Dim CSVfolder As String, CSVfile As String
  95.     CSVfolder = CsvPath & SaveDate & "\"
  96.     ¥Ø¿ý CSVfolder
  97.     CSVfile = CSVfolder & stockid & "_" & SaveDate & ".csv"
  98.     If Dir(CSVfile) <> "" Then Kill CSVfile
  99.     Query_Sh.Copy
  100.     With ActiveWorkbook
  101.         .SaveAs Filename:=CSVfile, FileFormat:=xlCSV
  102.         .Close 0
  103.     End With
  104. End Sub
  105. Private Sub xRecond(i As Integer, xSub As String)
  106.     Dim S As String
  107.     S = Time & vbTab & Format(Time - t, " ²Änn¤Àss¬í") & vbTab & " ²Ä " & i & " ­ÓCsvÀÉ " & xSub
  108.     Close #1
  109.     Open °O¿ýÀÉ For Append As #1
  110.     Print #1, S
  111.     Close #1
  112.     Application.StatusBar = S
  113. End Sub
  114. Private Function ¸ê®Æ­¶¼Æ() As Integer   '¨ú±o­¶¼Æ
  115.     If IE Is Nothing Then
  116.         Set IE = CreateObject("InternetExplorer.Application")
  117.         IE.Navigate "http://bsr.twse.com.tw/bshtm/bsMenu.aspx"
  118.         IE.Visible = True  '¥i¤£Åã¥Ü
  119.     End If
  120.     With IE
  121.         Do:  Loop While .Busy Or IE.ReadyState <> 4
  122.         With .document
  123.             .getElementByID("txtTASKNO").Value = stockid
  124.             .getElementByID("btnOK").Click
  125.             Do: Loop While IE.Busy Or IE.ReadyState <> 4 Or .getElementByID("sp_ListCount") Is Nothing
  126.             ¸ê®Æ­¶¼Æ = Val(.getElementByID("sp_ListCount").innertext)
  127.         End With
  128.     End With
  129. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 14# GBKEE

¹ê»Ú´ú¸ÕG¤j©Ò´£¨Ñªºµ{¦¡¡A½T¹ê¤ñ¤§«eªº¨º­ÓÁÙ­n§Ö«Ü¦h
¥J²Ó¤ñ¹ï«á¡A§Úµo²{¥D­nªº°ÝÃD¬O¦b©ó½Õ¾ã®æ¦¡³o³¡¤À

¦]¬°³o¨Ç¸ê®Æ§Ú·|¦A¶×¤J¨ìmysql·í¤¤¡A©Ò¥H¸ê®Æ®æ¦¡¤£¯à¥X¿ù
©Ò¥H¦b³Ì¤@¶}©l§Ú©Ò´£¨Ñªºµ{¦¡¤¤¡A¦bsub ½Õ¾ã®æ¦¡()ªº³Ì«á¡A§Ú¦³¦A¥[¤W¤@¬q«ü¥O
cells.NumberFormatLocal = "G/³q¥Î®æ¦¡"
¦Ó³o¬q«ü¥O¬OG¤j©Ò´£¨Ñªºµ{¦¡½X¤¤©Ò¨S¦³ªº
¥Ø«e±À´ú´N¬O¦]¬°³o¬q±Ô­z¡A¾É­P¾ã­Óµ{¦¡³t«×­°¤F¤U¨Ó
¥H¤Îµ{¦¡°õ¦æ¹Lµ{¡AEXCEL©Ò¥e¥Îªº°O¾ÐÅ餣Â_Ãk°ª

·PÁÂG¤jªº«üÂI¡A¨ü±Ð¤F¡I

TOP

¦^´_ 15# sasho
  1. Cells.NumberFormatLocal = "G/³q¥Î®æ¦¡"
  2. ¥i§ï¦¨(ÁY¤p½d³ò)
  3. UsedRange.NumberFormatLocal = "G/³q¥Î®æ¦¡"
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD