chang0833

¦^´_ 8# GBKEE
ª©¤j³o­Óµ{¦¡¯uªº«Ü¦n¥Î¡A
§Ú¬OEXCEL VBAªì¾ÇªÌ¡AÁÙ¦b¼Ò¯Á¶¥¬q¡Kµh­W¾Ç²ß¤¤
¥i¥HÀµ½Ðª©¤j¦b¤j¤OÀ°¦£¤@¤U¡A¦p¦ó¯à¤@¦¸¤U¸ü³Ì·s8©uªº¸ê®Æ¤Î¦b¶}ÀY¦C¥XªÑ¦W©Î¥N¸¹
·P¿E¤£ºÉ¡AÁÂÁÂ

chang0833

¦^´_ 21# chang0833
½Ð°Ýª©¤j¡A§Ú¥Î³Ì²Âªº¤è¦¡½Æ»s¤F¤K¦¸µ{¦¡(©T©w¦~«×¤Î©u§O)¡AÁ`©ó¥i¥H¶]¥X¨Ó¤F¡A¥i¬O"ªÑ²¼¥N¸¹"¥i§_³]­p¤@­Ó¥i¥H¦Û¥Î½Õ¾ãªºÅܼƨӿé¤J
"xCo_Id = "K"                   '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹"¡K¡K³o¤@¦æ¸Ó¦p¦ó¼g¡A©ÎªÌ¦³¨ä¥¦§ó¦nªº¤è¦¡¡C

·Ð½Ðª©¤j½ç±Ð¤F¡A·P¿E¤£ºÉ¡AÁÂÁ¡C

Dim URL As String, xCo_Id As String, xSyear As String, xSseason As String

xCo_Id = "K"                   '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹
xSyear = "102"    'Format(Date, "e")->¤¤µØ¥Á°êªº¦~«×
xSseason = "2"       'Format(Date, "q")->·í¦~«×ªº©u§O
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
        .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3,4"                 '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

GBKEE

"xCo_Id = "K"                   '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹"¡K¡K³o¤@¦æ¸Ó¦p¦ó¼g¡A©ÎªÌ¦³¨ä¥¦§ó¦nªº¤è¦¡

¦^´_ 22# chang0833

°Ñ¦Ò ³o¸Ì  §ï¤@¤U

1. X = Application.InputBox("½Ð¿é¤J¿z¿ïÃöÁä¦r")
   
2. If X = "" Or X = "False" Then Exit Sub

½Æ»s¥N½X

chang0833

¦^´_ 22# chang0833
·PÁª©¤j¡A¥N¸¹¿é¤J°ÝÃD¤w¸g¸Ñ¨M^^
¦A½Ðª©¤j¸Ñ¨M¤@­Ó°ÝÃD¡A­n¦p¦ó¨Ï¤U¸ü¤U¨Óªº¸ê®Æ¡A©ñ¦b«ü©wªºÀx¦s®æ¦ì¸m??
·Ð½Ð½ç±Ð¤F¡AÁÂÁÂ^^

chang0833

ª©¤j¡G§Ú°õ¦æ«á¡Aºô­¶¸ê®Æ³£¬O±q²Ä¤@¦C¶}©l±Æ¦C¡A¦b°õ¦æ¨ì¤U¤@©u®É·|§â¸ê®Æ©¹¥k±À
¡A¦ý¹J¨ì°]³ø²Ä¥|©u®É¡A¦]¨ä®æ¦¡»P¨ä¥L¤T©u¤£¦P¡A¾É­P§ó·s¸ê®Æ°_©l¤£¦P¦Ó§ïÅܸê®Æ¦ì¸m
¡A©Ò¥H§Ú»Ý­n§â¨C¤@©uªº¤U¸ü¦ì¸m¡A³£©ñ¦b©T©wÀx¦s¦ì¸m¡A¸ê®Æ¤~¤£·|¨C¦¸³£¤£¤@¼Ë
¦b³Ò·Ðª©¤j¤F^^ÁÂÁÂ

Dim URL As String, xCo_Id As String, xSyear As String, xSseason As String

K = Application.InputBox("½Ð¿é¤JªÑ²¼¥N¸¹")
X = Application.InputBox("½Ð¿é¤J³Ì·s¦~«×")
Y = X - 1
Z = X - 2

xCo_Id = "" & K                 '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹
xSyear = "" & Z    'Format(Date, "e")->¤¤µØ¥Á°êªº¦~«×
xSseason = "2"       'Format(Date, "q")->·í¦~«×ªº©u§O
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
        .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3,4"                 '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
xCo_Id = "" & K                     '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹
xSyear = "" & Z    'Format(Date, "e")->¤¤µØ¥Á°êªº¦~«×
xSseason = "3"       'Format(Date, "q")->·í¦~«×ªº©u§O
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
        .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3,4"                 '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
xCo_Id = "" & K                     '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹
xSyear = "" & Z 'Format(Date, "e")->¤¤µØ¥Á°êªº¦~«×
xSseason = "4"       'Format(Date, "q")->·í¦~«×ªº©u§O
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
        .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3,4"                 '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
xCo_Id = "" & K                     '­n¨D¿é¤Jºô­¶ªº°Ñ¼Æ:ªÑ²¼¥N¸¹
xSyear = "" & Y    'Format(Date, "e")->¤¤µØ¥Á°êªº¦~«×
xSseason = "1"       'Format(Date, "q")->·í¦~«×ªº©u§O
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
        .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3,4"                 '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

GBKEE

¦^´_ 25# chang0833
¤w­×§ï ¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim URL As String, xCo_Id As String, xSyear As String, X As Integer, Rng As Range
   
4.     Dim xSseason As Variant
   
5.     xCo_Id = Application.InputBox("½Ð¿é¤JªÑ²¼¥N¸¹", , 2303)         '¹w³]¬° 2303
   
6.     X = Application.InputBox("½Ð¿é¤J³Ì·s¦~«×", , Year(Date) - 1911) '¤¤µØ¥Á°êªº¦~«×
   
7.    
   
8.     With ActiveSheet
   
9.         For Each xSseason In .QueryTables 'WEB¬d¸ßª«¥ó¶°¦X
   
10.             xSseason.Delete
    
11.         Next
    
12.         For Each xSseason In .Names       'Name ª«¥óªº¶°¦X
    
13.             .Names(xSseason.Name).Delete
    
14.         Next
    
15.         .UsedRange.Clear
    
16.         Set Rng = .Range("a1") '«ü©w¤u§@ªí¤W WEB¬d¸ßªº¦ì¸m
    
17.     End With
    
18.     '''''''''''''''''''
    
19.     For Each xSseason In Array(2, 3, 4, 1) '°j°éxSseason => 2, 3, 4, 1
    
20.         xSyear = "" & IIf(xSseason > 1, X - 2, X - 1)
    
21.         URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    
22.         With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Rng)
    
23.             .Name = xCo_Id & "-" & xSyear & "- ²Ä " & xSseason & " ©u" 'WEB¬d¸ßªº¦WºÙ
    
24.             .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
    
25.             .WebSelectionType = xlSpecifiedTables
    
26.             .WebFormatting = xlWebFormattingNone
    
27.             .WebTables = "2,3,4"                  '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
    
28.             .WebPreFormattedTextToColumns = True
    
29.             .WebConsecutiveDelimitersAsOne = True
    
30.             .WebSingleBlockTextImport = False
    
31.             .WebDisableDateRecognition = False
    
32.             .WebDisableRedirections = False
    
33.             .Refresh BackgroundQuery:=False
    
34.             With .ResultRange      'WEB¬d¸ß¸ê®Æªº½d³ò
    
35.                 Set Rng = .Cells(.Rows.Count + 2, 1) '¤U¤@WEB¬d¸ßªº¦ì¸m
    
36.             End With
    
37.         End With
    
38.     Next
    
39. End Sub

½Æ»s¥N½X

chang0833

¦^´_ 26# GBKEE
·PÁª©¤jªº¼ö¤ß¦^´_¡A´î»´«Ü¦h¾Ç²ß¤Wªº§xÂZ^^
¦A½Ð±Ð¤@¤Uª©¤j©Ò¼gªº³o¬qµ{¦¡¡A¥u¯à±N¿é¤J³Ì·sªº¦~«×©¹«e¬d«e¤G¦~ªº¸ê®Æ
¡A¦ý¤µ¦~ªº¸ê®Æ·|µLªk¬d¸ß¡A¸Ó¦p¦óÅý¤µ¦~ªº¸ê®Æ¤]¥i¥H¤U¸ü¡A
¦b³Ò·Ðª©¤j¤F¡AÁÂÁÂ^^


    For Each xSseason In Array(2, 3, 4, 1) '°j°éxSseason => 2, 3, 4, 1

        xSyear = "" & IIf(xSseason > 1, X - 2, X - 1)

GBKEE

¦^´_ 27# chang0833
¸Õ¸Õ¬Ý(¥þ³¡)

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim URL As String, xCo_Id As String, X As Integer, Rng As Range
   
4.     Dim E As Variant, xSyear As Integer, xSseason As Integer, D_Name As String
   
5.    
   
6.     With ActiveSheet
   
7.         For Each E In .QueryTables 'WEB¬d¸ßª«¥ó¶°¦X
   
8.             E.Delete
   
9.         Next
   
10.         For Each E In .Names       'Name ª«¥óªº¶°¦X
    
11.             .Names(E.Name).Delete
    
12.         Next
    
13.         .UsedRange.Clear
    
14.         Set Rng = .Range("a1") '«ü©w¤u§@ªí¤W WEB¬d¸ßªº¦ì¸m
    
15.     End With
    
16.     xCo_Id = Application.InputBox("½Ð¿é¤JªÑ²¼¥N¸¹", , 2303)         '¹w³]¬° 2303
    
17.     X = Year(Date) - 1910                  '¤¤µØ¥Á°êªº¦~«×
    
18.     For xSyear = X To X - 3 Step -1        '°j°é:¦~«×    '105->102
    
19.     'For xSyear = X - 3 To X               '°j°é:¦~«×    '102->105
    
20.         For xSseason = 1 To 4 '             '°j°é:©u§O    '1,2,3,4
    
21.             URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
    
22.             With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Rng)
    
23.                 .Name = xCo_Id & "_" & xSyear & "_²Ä" & xSseason & "©u" 'WEB¬d¸ßªº¦WºÙ
    
24.                 .AdjustColumnWidth = True                  '¦Û°Ê½Õ¾ãÄæ¼e
    
25.                 .WebSelectionType = xlSpecifiedTables
    
26.                 .WebFormatting = xlWebFormattingNone
    
27.                 .WebTables = "2,3,4"                  '¸ê²£­t¶Åªí,ºî¦X·l¯qªí,²{ª÷¬y¶qªí
    
28.                 .WebPreFormattedTextToColumns = True
    
29.                 .WebConsecutiveDelimitersAsOne = True
    
30.                 .WebSingleBlockTextImport = False
    
31.                 .WebDisableDateRecognition = False
    
32.                 .WebDisableRedirections = False
    
33.                 .Refresh BackgroundQuery:=False
    
34.                 If .ResultRange.Rows.Count = 2 Then 'µL¸ê®Æ
    
35.                     D_Name = .Name                  'WEB¬d¸ßªº¦WºÙ
    
36.                     .Delete                         '§R°£:WEB¬d¸ß
    
37.                     With Rng.Parent
    
38.                         For Each E In .Names
    
39.                             If InStr(E.Name, D_Name) Then E.Delete '§R°£:¤u§@ªí¤Wªº¦WºÙ->WEB¬d¸ßªº¦WºÙ
    
40.                         Next
    
41.                     End With
    
42.                 Else
    
43.                     With .ResultRange      'WEB¬d¸ß¸ê®Æªº½d³ò
    
44.                         Set Rng = .Cells(.Rows.Count + 2, 1) '¤U¤@WEB¬d¸ßªº¦ì¸m
    
45.                     End With
    
46.                 End If
    
47.             End With
    
48.         Next
    
49.     Next
    
50.     MsgBox "Ok"
    
51. End Sub

½Æ»s¥N½X

chang0833

¦^´_ 28# GBKEE

·PÁª©¤j¡A¥Ø«e¤U¸ü¨S°ÝÃD¤F¡AÁÂÁÂ^^
¥Ø«e¥ý¨ÓºCºC¬ã¨sª©¤jªºµ{¦¡¡A¤Ó·P®¦¤F^^

c_c_lai

¦^´_ 29# chang0833
¦^´_ 28# GBKEE

GBKEE ª©¤j
°²³]¦pªG±N¡G
    X = Year(Date) - 1910                 '  ¤¤µØ¥Á°êªº¦~«×
    For xSyear = X To X - 3 Step -1       ' °j°é: ¦~«×    ' 105->102
­×§ï¬°¡G
    X = Year(Date) - 1911                 '  ¤¤µØ¥Á°êªº¦~«×
    For xSyear = X To X - 2 Step -1       ' °j°é: ¦~«×    ' 104->102  ³Ì·sªº¦~«×©¹«e¬d«e¤G¦~ªº¸ê®Æ
«h¥»¦~«×¸ê°T·|±q ¡y"$A$1"¡z¦ì§}¶}©l¦C¦L¡A§_«h·|±q ¡y"$A$13"¡z¦ì§}¶}©l¦C¦L¡C
³o°¦µ{¦¡«D±`ºë²©öÄý¡A§Ú·|¦n¦n«O¦s³Æ¿ýªº¡AÁÂÁ GBKEE ±zªº«ü¾É¡I

1. "$A$1"     2015¦~3¤ë31¤é        2014¦~12¤ë31¤é        2014¦~3¤ë31¤é   
   
2. "$A$282"   2015¦~6¤ë30¤é        2014¦~12¤ë31¤é        2014¦~6¤ë30¤é
   
3. "$A$567"   2015¦~9¤ë30¤é        2014¦~12¤ë31¤é        2014¦~9¤ë30¤é
   
4. "$A$856"   2015¦~²Ä3©u        2014¦~²Ä3©u        2015¦~01¤ë01¤é¦Ü2015¦~09¤ë30¤é        2014¦~01¤ë01¤é¦Ü2014¦~09¤ë30¤é
   
5. 
   
6. "$A$859"   2014¦~3¤ë31¤é        2013¦~12¤ë31¤é        2013¦~3¤ë31¤é
   
7. "$A$1133"  2014¦~6¤ë30¤é        2013¦~12¤ë31¤é        2013¦~6¤ë30¤é
   
8. "$A$1409"  2014¦~9¤ë30¤é        2013¦~12¤ë31¤é        2013¦~9¤ë30¤é
   
9. "$A$1688"  2014¦~12¤ë31¤é        2013¦~12¤ë31¤é       
   
10. 
    
11. "$A$1971"  2013¦~3¤ë31¤é        2012¦~12¤ë31¤é        2012¦~3¤ë31¤é        2012¦~1¤ë1¤é
    
12. "$A$2247"  2013¦~6¤ë30¤é        2012¦~12¤ë31¤é        2012¦~6¤ë30¤é        2012¦~1¤ë1¤é
    
13. "$A$2530"  2013¦~9¤ë30¤é        2012¦~12¤ë31¤é        2012¦~9¤ë30¤é        2012¦~1¤ë1¤é
    
14. "$A$2812"  2013¦~12¤ë31¤é        2012¦~12¤ë31¤é        2012¦~1¤ë1¤é
    
15.     ~
    
16. "$A$3092"  

½Æ»s¥N½X