·sª©ªÑ¥«¤½¶}¸ê°TÆ[´ú¯¸ªº¸ê®Æ§ì¨ìEXECL?
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 8# GBKEE
ª©¤j³oÓµ{¦¡¯uªº«Ü¦n¥Î¡A
§Ú¬OEXCEL VBAªì¾ÇªÌ¡AÁÙ¦b¼Ò¯Á¶¥¬q¡KµhW¾Ç²ß¤¤
¥i¥HÀµ½Ðª©¤j¦b¤j¤OÀ°¦£¤@¤U¡A¦p¦ó¯à¤@¦¸¤U¸ü³Ì·s8©uªº¸ê®Æ¤Î¦b¶}ÀY¦C¥XªÑ¦W©Î¥N¸¹
·P¿E¤£ºÉ¡AÁÂÁ |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 22# chang0833
·PÁª©¤j¡A¥N¸¹¿é¤J°ÝÃD¤w¸g¸Ñ¨M^^
¦A½Ðª©¤j¸Ñ¨M¤@Ó°ÝÃD¡An¦p¦ó¨Ï¤U¸ü¤U¨Óªº¸ê®Æ¡A©ñ¦b«ü©wªºÀx¦s®æ¦ì¸m??
·Ð½Ð½ç±Ð¤F¡AÁÂÁÂ^^ |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
ª©¤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 |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 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) |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 28# GBKEE
·PÁª©¤j¡A¥Ø«e¤U¸ü¨S°ÝÃD¤F¡AÁÂÁÂ^^
¥Ø«e¥ý¨ÓºCºC¬ã¨sª©¤jªºµ{¦¡¡A¤Ó·P®¦¤F^^ |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
ª©¤j¡A¥i¥H¦b½Ð±Ð¤@¤UÃö©ó³oÓ°]³øºô¶¤U¸ü¤U¨Óªº¸ê®Æ°ÝÃD¶Ü¡H
§Ú¥Îvlookup ¨Ó¤ñ¹ï¸ê®Æ¡A«oµo²{¤U¦C¸ê®Æ¦³«½Æ¡Avlookup ¥u¯à®»¨ì²Ä¤@µ§¸ê®Æ¡A
¦ý¬O²Ä¤@µ§¸ê®Æ¬OªÅ¥Õªº¡An¦p¦ó®»¨ì«½Æ¸ê®Æ¦ý¤S¥i¥H¿ï¾Ü¨ä«á¦³¸ê®ÆªºÄæ¦ì®»¨ú
¦A³Â·Ðª©¤j½ç±Ð¤F^^
À³¦¬²¼¾Ú²bÃB
À³¦¬²¼¾Ú²bÃB 72,036
À³¦¬±b´Ú²bÃB
À³¦¬±b´Ú²bÃB 19,452,028
À³¦¬±b´Ú¡ÐÃö«Y¤H²bÃB
À³¦¬±b´Ú¡ÐÃö«Y¤H²bÃB 231,367 |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 35# c_c_lai
·PÁ¤j¤jªº¦^´_¡A§ÚÀ´¤F¡Aì¨Ó®t²§´N¦b«e±ªÅ®æªº±Æ¦C¶¶§Ç¤F¡A¤£¥J²Ó¬Ý¯uªº¬Ý¤£¥X¨Ó^^
¥t¥~·PÁÂGBKEEª©¤jªº¥Î¤ß¦^´_ÁÂÁÂ^^ |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 36# GBKEE
ª©¤j¡A§Ú±N§Aªºµ{¦¡¡A§ï¦¨¦U©u°]³ø¾î©ñ¡A¦ýn¦p¦ó½Æ»s¦U©u°]³ø¨ì¨ä¥L¤u§@¶®É¡A¥i¥H"«ü©w"Àx¦s®æ¦s©ñ
¤ñ¦p²Ä¤@©u°]³ø½Æ»s¨ä¥L¤u§@ªí¨ìa¨ìdÄæ¡A²Ä¤G©u½Æ»s¨ìgÄæ¨ìjÄæ¦s©ñ -----( ±Ä©T©w4Ä檺®æ¦¡¦s©ñ)
§Ú¥»¨Ó·Q»¡¥ÎIF¶]°j°éªºµ{¦¡¦pŪ¨ú¨ì²Ä¤@¦C¦³"·|p¶µ¥Ø"®É¡A«h¿ï¨ú²Ä1¨ì²Ä4Äæ½Æ»s¨ì¨ä¥¦¤u§@ªíªº«ü©w¦ì¸m
Ū¨ú¨ì²Ä¤GÓ"·|p¶µ¥Ø"®É¡A½Æ»s¨ì¤UªºÓ«ü©wªº¤u§@ªí¤W...
¤£¹L¹ï·s¤âªº§Ú¨Ó»¡ÁÙ¬O«ÜÃø°µ¨ì¡A¦A·Ð½Ðª©¤j½ç±Ð¤F¡AÁÂÁÂ^^
Dim URL As String, xCo_Id As String, x As Integer, Rng As Range
Dim E As Variant, xSyear As Integer, xSseason As Integer, D_Name As String
Dim Ia As Integer
With ActiveSheet
For Each E In .QueryTables 'WEB¬d¸ßª«¥ó¶°¦X
E.Delete
Next
For Each E In .Names 'Name ª«¥óªº¶°¦X
.Names(E.Name).Delete
Next
.UsedRange.Clear
Set Rng = .Range("a1") '«ü©w¤u§@ªí¤W WEB¬d¸ßªº¦ì¸m
End With
xCo_Id = Application.InputBox("½Ð¿é¤JªÑ²¼¥N¸¹", , 2303) '¹w³]¬° 2303
x = Year(Date) - 1910 '¤¤µØ¥Á°êªº¦~«×
For xSyear = x To x - 3 Step -1 '°j°é:¦~«× '105->102
'For xSyear = X - 3 To X '°j°é:¦~«× '102->105
For xSseason = 4 To 1 Step -1 ' '°j°é:©u§O '1,2,3,4
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:=Rng)
.Name = xCo_Id & "_" & xSyear & "_²Ä" & xSseason & "©u" 'WEB¬d¸ßªº¦WºÙ
.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
If .ResultRange.Rows.Count = 2 Then 'µL¸ê®Æ
D_Name = .Name 'WEB¬d¸ßªº¦WºÙ
.Delete '§R°£:WEB¬d¸ß
With Rng.Parent
For Each E In .Names
If InStr(E.Name, D_Name) Then E.Delete '§R°£:¤u§@ªí¤Wªº¦WºÙ->WEB¬d¸ßªº¦WºÙ
Next
End With
Else
With .ResultRange 'WEB¬d¸ß¸ê®Æªº½d³ò
Set Rng = .Cells(1, .Columns.Count + 2) '¤U¤@WEB¬d¸ßªº¦ì¸m
End With
End If
End With
Next
Next
Dim Ba As Integer '¦]Ū¨ú¨ìÁÙ¥¼µo§Gªº°]³ø¡A·|¯dªÅ¥ÕÄæ
'§PÂ_«e50Äæ¬O§_¦³ªÅ¥ÕÄæ¡A¦³«h§R°£
For Ba = 1 To 50
If Range("A" & Ba).Value = "" Then
Selection.EntireColumn.Delete
Else
End If
Next
End Sub |
|
|
|
|
|
|
- ©«¤l
- 23
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 58
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-26
- ³Ì«áµn¿ý
- 2018-8-23
|
¦^´_ 39# GBKEE
·PÁª©¤jªº¼ö¤ß¦^´_^^
ª©¤j³o¦¸¼gªºµ{¦¡¡A¹ï·s¤âªº§Ú¦³ÂI....§xÃø
§Ú¸ÕµÛ¶]µ{¦¡....¦ý¶]¥X"°}¦C¯Á¤Þ¶W¥X½d³ò"...³o¬O¤°»ò±¡§Î |
|
|
|
|
|
|