·sª©ªÑ¥«¤½¶}¸ê°TÆ[´ú¯¸ªº¸ê®Æ§ì¨ìEXECL?
- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¦^´_ 29# chang0833
¦^´_ 28# GBKEE
GBKEE ª©¤j¨S¯d·N±z¦b 34. ~ 46. ¶¡¥[¤J¤F If ~ Then ªº§PÂ_»y¥y¡C¤ï¶Õ¡I- 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(.Rows.Count + 2, 1) ' ¤U¤@WEB¬d¸ßªº¦ì¸m
- End With
- End If
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-11-13 07:55 ½s¿è
¦^´_ c_c_lai
³oºô¶¦³ÂI©_©Ç,¦~«×ªº¿ï¶µ¬°¦ó¦³¤U¤@¦~«×(¥¼¨Óªº¦~«×).
¦³¤H¥i¤À¨É¶Ü?
GBKEE µoªí©ó 2015-11-13 06:00
¤U¤@¦~«×(¥¼¨Óªº¦~«×) ¥Á°ê 105 ¦~©|¥¼¨ì¹F¡A²z½×¤W¥¦¬O¤@Ó¥¼ª¾¼Æ¡A
·|p¦~«×¤]¥u¨ì¤µ¦~«×¦Ó¤w¡A¥B©|¥¼¦~«×µ²Âàþ¨Óªº¸ê®Æ¡H
|
|
|
|
|
|
|
- ©«¤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
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-11-15 07:04 ½s¿è
¦^´_ 34# chang0833
²Ä¤@Ó¡yÀ³¦¬²¼¾Ú²bÃB¡z¬O¡y¶µ¥Ø¡z(¼ÐÃD)¡A
²Ä¤GÓ¡yÀ³¦¬²¼¾Ú²bÃB¡z¬O·|p¡y¬ì¥Ø¡z¡A
¨âªÌ¦bÄ_ªí¤W¬O¦³«e«á¦ì¸m®t²§ªº¡A
Âà¤J«á¦p¤£¥J²ÓÆ[¹î¡A«K¥H¬°¬O
«ÂСy¬ì¥Ø¡z¤F¡C
¦Ü©ó VLOOKUP ªºÀ³¥Î´N¦³³Ò GBKEE ª©¤j¤F¡C |
|
|
|
|
|
|
- ©«¤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
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 38# chang0833
¸Õ¸Õ¬Ý- Option Explicit
- Sub Ex()
- Dim URL As String, xCo_Id As String, X As Integer
- Dim xSyear As Integer, xSseason As Integer, Ar(1 To 4)
- Dim Sh(1 To 2) As Worksheet, AY, Rng As Range, E As Variant
- Dim Wb As Workbook
- For X = 0 To 3
- Ar(X + 1) = 1 + (6 * X) '²Ä¤@©u¨ì²Ä¥|©uªºÄæ¦ì
- Next
- xCo_Id = Application.InputBox("½Ð¿é¤JªÑ²¼¥N¸¹", , 2303) '¹w³]¬° 2303
- X = Year(Date) - 1910 '¤¤µØ¥Á°êªº¦~«×
- Application.ScreenUpdating = False
- Set Wb = Workbooks("book1.xls") '«ü©w¬¡¶Ã¯
- With Wb
- Set Sh(1) = .Sheets.Add '·s¼W¤u§@ªí: ½Æ»s©u°]³ø¨ì«ü©w¤u§@¶
- Set Sh(2) = .Sheets.Add '·s¼W¤u§@ªí: WEB¬d¸ß¥Î
- End With
- On Error GoTo Er '³B²zµ{¦¡¤Wªº¿ù»~
- Sh(1).Name = xCo_Id & "©u³øªí" '³o¦WºÙ¤u§@ªí¦p¤w¦s¦bµ{¦¡·|¦³¿ù»~
- On Error GoTo 0 '¤£¦b³B²zµ{¦¡¤Wªº¿ù»~
-
- For xSyear = X To X - 3 Step -1 '°j°é:¦~«× '105->102
- 'For xSyear = X - 3 To X '°j°é:¦~«× '102->105
- For xSseason = 1 To 4 ' '°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 Sh(2).QueryTables.Add(Connection:=URL, Destination:=Sh(2).[A1])
- .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 '¦³¸ê®Æ
- Set Rng = Sh(1).Cells(1, Ar(xSseason)).Cells(Rows.Count).End(xlUp)
- If Rng.Row > 1 Then Set Rng = Rng.Offset(2)
- .ResultRange.Copy Rng
- Else
- .Delete
- End If
-
- End With
- Next
- Next
- Application.DisplayAlerts = False
- Sh(2).Delete
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- Sh(1).Parent.Save
- MsgBox "Ok"
- Exit Sub
- Er: '³B²z xCo_Id &©u³øªí ¤u§@ªí¤w¦s¦b
- Application.DisplayAlerts = False
- Sheets(xCo_Id & "©u³øªí").Delete
- Application.DisplayAlerts = True
- Resume Next '¦^¨ì¿ù»~ªºµ{¦¡½X
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤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¤°»ò±¡§Î |
|
|
|
|
|
|