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

[¤À¨É] ¤j½L¨C¤ë¨C¤Ñ¾ú¥v¦¨¥æ¶q»Pª÷ÃB¤U¸ü

[¤À¨É] ¤j½L¨C¤ë¨C¤Ñ¾ú¥v¦¨¥æ¶q»Pª÷ÃB¤U¸ü

¥»©«³Ì«á¥Ñ white5168 ©ó 2012-5-31 00:43 ½s¿è

Ä~¤W¦¸¦UªÑªÑ»ù¾ú¥v¸ê®Æ¤U¸ü,¦A¤@¦¸¤À¨É ¤j½L¾ú¥v¦¨¥æ¶q¤U¸ü
ªþ¥ó¦³ "¤j½L¨C¤ë¾ú¥v¦¨¥æ¶q»Pª÷ÃB¤U¸ü" ÀÉ, Åwªï¦U¦ì¥ý¶i¸Õ¥Î¬Ý¬Ý
¦p¦³°ÝÃDÅwªï§iª¾¥H«K©ó­×§ï,µ{¦¡½X«Ý¤j®aı±o¤£¿ù¥Î®É,·|µy«á¸É¤W

¤j½L¦¨¥æ¶q¤U¸ü.rar (19.38 KB)

¤j½L¨C¤ë¾ú¥v¦¨¥æ¶q»Pª÷ÃB¤U¸ü

¥»©«³Ì«á¥Ñ white5168 ©ó 2012-6-23 22:57 ½s¿è

¦bSheet1ªºµ{¦¡½X
  1. Private Sub ¤j½L¦¨¥æ¸ê°T_Click()
  2.     Dim Year As String
  3.     Dim Mon As String
  4.    
  5.     Year = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
  6.     Mon = Format(Range("C2"), "00")     '­×§ï¦r¦ê®æ¦¡
  7.     Call Run(Year, Mon)                 '©I¥sModule1¤¤ªº¨ç¼Æ
  8. End Sub
½Æ»s¥N½X
¦bModule1ªºµ{¦¡½X
  1. Sub Run(Year As String, Month As String)
  2.     Dim sheetName As String
  3.     sheetName = "Temp"
  4.    
  5.     If CheckSheetExist(sheetName) <> True Then      '½T©wTemp¤u§@ªí¬O§_¦s¦b,­Y¤£¦s¦b«h©I¥sAddTempSheet«Ø¥ßTemp¤u§@ªí
  6.         Call AddTempSheet(sheetName)
  7.     End If
  8.    
  9.     Call ClearTempTablesData(sheetName)             'ÁקKTemp¤u§@ªí¦s¦b®É¸ê®Æ®æ¦¡¥¼²M°£,¦Ó²M°£
  10.     Call GetPrice(sheetName, Year, Month)           '±qTWSE¨ú±o¤j½L¾ú¥v¸ê®Æ
  11.     Call ClearsheetTablesData("Sheet1")             '²M°£­ì¥»¦bSheet1¤u§@ªíªº¸ê®Æ
  12.     Call SetCellWidthSize(sheetName)                '³]©wTWSE¨ú±oªº¸ê®Æ©Ò³y¦¨ªº®æ¦¡,±N¦¹®æ¦¡½Õ¾ã¬°excel¹w³]ªºÀx¦s®æ®æ¦¡
  13.     Call CopyDatatoSheet(sheetName)                 '±NTemp¤u§@ªí¸ê®Æ«þ¨©¦ÜSheet1¤u§@ªí
  14.     Call DeleteTempSheet(sheetName)                 '§R°£Temp¤u§@ªí   
  15.     Sheets("Sheet1").Select                         '±Nfocus³]©w¨ìSheet1¤u§@ªí
  16. End Sub

  17. Function CheckSheetExist(sheetName As String) As Boolean
  18.     Dim i As Integer
  19.     CheckSheetExist = False
  20.     For i = 1 To Worksheets.Count                   '¨ú±o¥Ø«e¤u§@ªíªº¼Æ¶q
  21.         If sheetName = Worksheets(i).Name Then      '§PÂ_«ü©wªº¤u§@ªí¦WºÙ¬O§_¦s¦b,¦s¦b«h¦^¶Ç§ä¨ì¤u§@ªíªº°T®§
  22.             CheckSheetExist = True                  '±N§ä¨ìªº°T®§³]©w¦Ü¦^¶Ç­È
  23.         End If
  24.     Next
  25. End Function

  26. Sub AddTempSheet(sheetName As String)
  27.     ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)   '«Ø¥ß«ü©w¤u§@¨ì²{¦s¤u§@ªíªº¹ï«á­±
  28.     Worksheets(Worksheets.Count).Select                                 '¿ï¾Ü«Ø¥ß¤u§@ªí
  29.     ActiveSheet.Name = sheetName                                        '­×§ï¤u§@ªí¦WºÙ
  30. End Sub

  31. Sub GetPrice(sheetName As String, Year As String, Month As String)
  32.    
  33.     Sheets(sheetName).UsedRange.Select              '¿ï¨ú«ü©w¤u§@ªíA1:H50ªºÀx¦s®æ½d³ò
  34.     Selection.Clear                                 '²M°£©Ò¿ï¨úÀx¦s®æ®æ¦¡
  35.     Selection.ClearContents                         '²M°£©Ò¿ï¨úªº¸ê®Æ
  36.     Sheets(sheetName).Range("A1").Select            '¿ï¨úTemp¤u§@ªíA1Àx¦s®æ,ÁקK¨Ï¥ÎQueryTable«á,¦]¬°¸ê®ÆÀ½À£·|³y¦¨Àx¦s®æ¥k²¾,¾É­Pfoucs¤£¦bA1Àx¦s®æ¤W¦Óµo¥Í¿ù»~°T®§
  37.    

  38.     '¥H¤U´N¤£¦h¤¶²Ð,Excel¬ÛÃö¤º®e
  39.     With ActiveSheet.QueryTables.Add(Connection:= _
  40.         "TEXT;http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & Year & "&mmon=" & Month & "&type=csv", _
  41.         Destination:=Range("A1"))
  42.         .Name = "¤j½L¾ú¥v¸ê®Æ"
  43.         .FieldNames = True
  44.         .RowNumbers = False
  45.         .FillAdjacentFormulas = False
  46.         .PreserveFormatting = True
  47.         .RefreshOnFileOpen = False
  48.         .RefreshStyle = xlInsertDeleteCells
  49.         .SavePassword = False
  50.         .SaveData = True
  51.         .AdjustColumnWidth = True
  52.         .RefreshPeriod = 0
  53.         .TextFilePromptOnRefresh = False
  54.         .TextFilePlatform = 950
  55.         .TextFileStartRow = 1
  56.         .TextFileParseType = xlDelimited
  57.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  58.         .TextFileConsecutiveDelimiter = False
  59.         .TextFileTabDelimiter = False
  60.         .TextFileSemicolonDelimiter = False
  61.         .TextFileCommaDelimiter = True
  62.         .TextFileSpaceDelimiter = False
  63.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
  64.         .TextFileTrailingMinusNumbers = True
  65.         .Refresh BackgroundQuery:=False                             '­Y¨S¦³ Sheets(sheetName).Range("A1").Select,¦b¦¹¦æ·|µo¥Í¿ù»~
  66.         If Err.Number <> 0 Then Err.Clear: MsgBox "¸ê®Æ¬d¸ß¥¢±Ñ"    '³Q§K¸ê®Æ§ì¨ú¤£¦¨¥\,¦ÓÅã¥Ü°T®§
  67.     End With
  68. End Sub

  69. Sub SetCellWidthSize(sheetName As String)
  70.     Dim n As Integer
  71.     Worksheets(sheetName).Select
  72.     n = ActiveSheet.Range("A1").End(xlDown).Row             '¨ú±o¿ï¨ú¦³¦s¦b¸ê®ÆªºÀx¦s®æ¦C¼Æ
  73.     ActiveSheet.Range("A1:F" & n).UseStandardWidth = True   '³]©w«ü©w¤u§@ªíªºÀx¦s®æ¼e«×¬°¹w³]­È
  74. End Sub
  75. Sub CopyDatatoSheet(sheetName As String)
  76.     Dim n As Integer
  77.    
  78.     Worksheets(sheetName).Select                            '¿ï¨ú«ü©w¦WºÙ¤u§@ªí
  79.     n = ActiveSheet.Range("A3").End(xlDown).Row - 1         '¨ú±o¿ï¨ú¦³¦s¦b¸ê®ÆªºÀx¦s®æ¦C¼Æ
  80.     ActiveSheet.Range("A3:F" & n).Copy                      '½Æ»s¿ï¨úªºÀx¦s®æ¸ê®Æ
  81.    
  82.     Worksheets("Sheet1").Select                             '¿ï¨úSheet1¤u§@ªí
  83.     Range("A5").Select                                      '¿ï¨úA5Àx¦s®æ
  84.     ActiveSheet.Paste                                       '¶K¤W¸ê®Æ   
  85. End Sub

  86. Sub ClearsheetTablesData(sheetName As String)
  87.     Dim n As Integer
  88.     Dim qyt As QueryTable
  89.    
  90.     Worksheets(sheetName).Select
  91.     If ActiveSheet.Range("A5") <> "" Then                   '§PÂ_¥Ø«eªº¬¡­¶Ã¯¬O§_¦³¸ê®Æ¦s¦b, ³o¦æ¥i¥H¦A¼gªº§óÂÔ·V,Åwªï¦U¦ì¦Û¦æ­×§ï
  92.         n = ActiveSheet.Range("A5").End(xlDown).Row         '¿ï¨ú¥Ø«e¬¡­¶Ã¯±qA4¦ì¸m¨ì³Ì«á¤@¦æªº½d³ò
  93.         For Each qyt In ActiveSheet.QueryTables             '¿ï¨ú¥ÎQueryTables§ì¨úªº¨C¤@¦æ
  94.             qyt.Delete                                      '±N¨Ï¥ÎQueryTables¤èªk©Ò²£¥Íªº¦æ¶i¦æ§R°£,ÁקKQueryTables¥Î¤[¤F,³y¦¨¨t²Î°ï¿n¤@°ïQueryTablesªº©U§£,¦p¦¹¨t²Î¤~³¡·|ÅܺC
  95.         Next
  96.         ActiveSheet.Range("A5:G" & n).Clear                 '²M°£©Ò¿ï¨úÀx¦s®æ®æ¦¡
  97.         ActiveSheet.Range("A5:G" & n).ClearContents         '²M°£©Ò¿ï¨úªº¸ê®Æ
  98.     Else
  99.         ActiveSheet.Range("A5:G40").Clear                   '²M°£©Ò¿ï¨úÀx¦s®æ®æ¦¡
  100.         ActiveSheet.Range("A5:G40").ClearContents           '²M°£©Ò¿ï¨úªº¸ê®Æ
  101.     End If
  102. End Sub

  103. Sub DeleteTempSheet(sheetName As String)
  104.     Worksheets(sheetName).Select
  105.     Application.DisplayAlerts = False                       'Ãö³¬Äµ§iµøµ¡
  106.     Worksheets(sheetName).Delete                            '§R°£§@¥Î¤¤ªº¤u§@ªí
  107.     Application.DisplayAlerts = True                        '«ì´_ĵ§iµøµ¡
  108. End Sub

  109. Sub ClearTempTablesData(sheetName As String)
  110.     Dim n As Integer
  111.     Dim qyt As QueryTable
  112.    
  113.     Worksheets(sheetName).Select                            '¿ï¨ú«ü©w¦WºÙ¤u§@ªí
  114.     If ActiveSheet.Range("A1") <> "" Then
  115.         n = ActiveSheet.Range("A1").End(xlDown).Row         '¿ï¨ú¥Ø«e¬¡­¶Ã¯±qA1¦ì¸m¨ì³Ì«á¤@¦æªº½d³ò
  116.         For Each qyt In Worksheets(sheetName).QueryTables   '¿ï¨ú¥ÎQueryTables§ì¨úªº¨C¤@¦æ
  117.             qyt.Delete                                      '±N¨Ï¥ÎQueryTables¤èªk©Ò²£¥Íªº¦æ¶i¦æ§R°£,ÁקKQueryTables¥Î¤[¤F,³y¦¨¨t²Î°ï¿n¤@°ïQueryTablesªº©U§£,¦p¦¹¨t²Î¤~³¡·|ÅܺC
  118.         Next
  119.         ActiveSheet.Range("A1:F" & n).Clear                 '²M°£©Ò¿ï¨úÀx¦s®æ®æ¦¡
  120.         ActiveSheet.Range("A1:F" & n).ClearContents         '²M°£©Ò¿ï¨úªº¸ê®Æ
  121.     Else
  122.         ActiveSheet.UsedRange.Clear                         '²M°£©Ò¿ï¨úÀx¦s®æ®æ¦¡
  123.         ActiveSheet.UsedRange.ClearContents                 '²M°£©Ò¿ï¨úªº¸ê®Æ
  124.     End If
  125. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-6-24 08:41 ½s¿è

¦^´_ 2# white5168
­n¤À¨É°O±o±M®×½Ð¤£­n¤£¤WÂê
SHEET1ªºµ{¦¡½X
  1. Private Sub ¤j½L¦¨¥æ¸ê°T_Click()
  2.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
  3.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
  4.     xlTheMonth = Format(Range("C2"), "00")     '­×§ï¦r¦ê®æ¦¡
  5.     UsedRange.Offset(4).Clear
  6.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
  7.     With Workbooks.Open(xlTheFile)
  8.         .Sheets(1).UsedRange.Offset(2).Copy [a5]
  9.         .Close 0
  10.     End With
  11. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ white5168 ©ó 2012-6-23 23:03 ½s¿è

ª©¥D,½Ð°Ý¦³¦Û¦æ½T»{¹L¥H¤Wµ{¦¡½X¬O¥i¥H±N¸ê®Æ¦¨¥\²£¥Í¦bsheet1¶Ü?

TOP

¦^´_ 4# white5168
¸Õ¸Õ¬Ý

    ¤j½L¨C¤ë¨C¤Ñ¾ú¥v..rar (7.64 KB)

TOP

¦^´_ 3# GBKEE
ÆZ¤£¿ùªº¼gªk¡I
¤@¤ä Private Sub ¤j½L¦¨¥æ¸ê°T_Click() ´N§¹¦¨¤F©Ò¦³ªº§@·~¡C

TOP

¦^´_ 7# usana642
1.½Æ»sµ{¦¡½X¨ì ¤@¯ë¼ÒèÊ,©Î ThisWorkbook¼ÒèÊ    2.¦b¤u§@ªí¤W ´¡¤J§Ö¨ú¹Ï®×,   3.±N¹Ï®×ªº¥¨¶°«ü©w¦¹µ{§Ç
©ó¤u§@ªí ªº C1 :  ¿é¤J¦è¤¸¦~¥÷   C2 :  ¿é¤J¤ë¥÷    «ö¤U §Ö¨ú¹Ï®× ´N¥i¥H
  1. Sub ¤j½L¦¨¥æ¸ê°T()
  2.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
  3.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
  4.     xlTheMonth = Format(Range("C2"), "00")     '­×§ï¦r¦ê®æ¦¡
  5.     UsedRange.Offset(4).Clear
  6.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
  7.     With Workbooks.Open(xlTheFile)
  8.         .Sheets(1).UsedRange.Offset(2).Copy [a5]
  9.         .Close 0
  10.     End With
  11. End Sub
½Æ»s¥N½X
EX1.GIF
2012-6-25 20:32

TOP

¦^´_ 4# white5168

ª©¤j¨¯­W²¤Æªºµ{¦¡½X¡AÃø¹D§A¨S´ú¸Õ¬Ý¬Ý?
ªP¶Oª©¤jªº±Ð¾Ç
µ¹GBKEEª©¥D«ö­ÓÆg

TOP

«z¡I¶W¤è«Kªº~~~
ÁÂÁ¤j¤jªº¤À¨É~
¤×¨äÁÂÁÂGBKEEª©¥DªºÂ²©öª©~~~
¦ý¤p§Ì²{¦b¦³¤@­Ó°ÝÃD...­n«ç»ò¼g¥X¤@­Óµ{¦¡¡A»Ý¨D¬O¡G
¤U¸ü§¹«áªº¸ê®Æ¡A¦Û°ÊÀx¦s¨ì¥t¤@­ÓSHEET¡]Á`ªí¡^¡A
¤U¸ü¥t¤@®É´Áªº¸ê®Æ«á¡A¦A¦Û°ÊÀx¦s¦bÁ`ªí¤ºªÅ¥ÕÀx¦s®æ¡H
¨Ã¥B¬O¦V¤UÀx¦s³o¼Ë¡H

TOP

¦^´_ 9# turbine
  1. Option Explicit
  2. Private Sub ¤j½L¦¨¥æ¸ê°T()
  3.     Dim xlTheYear As String, xlTheMonth As String, xlTheFile As String
  4.     Dim Sh As Worksheet
  5.     xlTheYear = Format(Range("C1"), "0000")  '­×§ï¦r¦ê®æ¦¡
  6.     xlTheMonth = Format(Range("C2"), "00")   '­×§ï¦r¦ê®æ¦¡
  7.     Set Sh = ThisWorkbook.Sheets.Add         '·s¼W¤u§@ªí
  8.     Sh.Name = xlTheYear & "_" & xlTheMonth   '·s¼W¤u§@ªí©R¦W
  9.     xlTheFile = "http://www.twse.com.tw/ch/trading/exchange/FMTQIK/FMTQIK2.php?STK_NO=&myear=" & xlTheYear & "&mmon=" & xlTheMonth & "&type=csv"
  10.     With Workbooks.Open(xlTheFile)
  11.         .Sheets(1).UsedRange.Copy Sh.[a1]
  12.         .Close 0
  13.     End With
  14.     Sh.Cells.EntireColumn.AutoFit            '½Õ¾ãÄæ¼e
  15.     Sh.Columns("A:A").ColumnWidth = 28.56
  16. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD