½Ð°Ý¦p¦ó§ì¨újavascriptªº*.csvÀɮסH
- ©«¤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
|
¦^´_ 10# torrent
¸Õ¸Õ¬Ý- Option Explicit
- Sub Ex() '¥þ³¡¸ê®Æ¶W¹L 65536µ§ 2003ª©¤£¾A¥Î
- Dim Sh As Worksheet, wb As Workbook, i As Long
- Set Sh = ActiveWorkbook.Sheets(1)
- Sh.UsedRange = ""
- i = 0
- Do
- Workbooks.OpenText Filename:="http://opendata.epa.gov.tw/ws/Data/EMS/?$orderby=RegistrationNo&$skip=" & i & "&$top=1000&format=csv" _
- , Origin:=-535, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
- xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
- Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
- Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
- Array(9, 1), Array(10, 1), Array(11, 1)), TrailingMinusNumbers:=True
- With ActiveWorkbook.Sheets(1).UsedRange
- If .Cells(1) = "" Then Exit Do
- If i = 0 Then
- .Copy Sh.[a1]
- Else
- .Offset(1).Copy Sh.[a1].End(xlDown).Offset(1)
- End If
- ActiveWorkbook.Close False
- End With
- i = i + 1000
- Loop
- ActiveWorkbook.Close False
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 9
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 11
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2013-6-7
- ³Ì«áµn¿ý
- 2014-1-25
|
¦^´_ 11# GBKEE
¨ü±Ð¤F¡Aì¨Ón¥ÎWorkbooks¡C
¥t¥~¡A§Ú¦bGBKEE¤j¤jÀ°§Ú×¥¿ªº²Ä¤GÓcode¤¤°µ¤F¤@¨Ç×¥¿¡A¥Øªº¬O§âAÄ檺ºÞ¨î½s¸¹¶ñº¡¡A§Ú¦b²Ä31¦C¥[¤F³o¤@¦æ¡G
.Resize(Q.ResultRange.Rows.Count, 1).Offset(2, -1).Value = Rng
¬Ý°_¨Ó°£¤F³Ì«á¤@ÓºÞ¨î½s¸¹·|¦h¨â¦æ§À¤Ú¤§¥~¡A¦n¹³¨S¦³¨ä¥¦ªº°ÝÃD¡A¤£ª¾¹D¦U¦ì¦³¨S¦³§ó¦nªº·N¨£©Î¬Ý¥X³o¼Ë·d·|¦³bug¡H
ÁÂÁÂ
- Sub punish()
- Dim Sh As Worksheet, Rng As Range, Q As Variant
- Application.ScreenUpdating = False
- Set Rng = Sheets("Sheet1").Range("A2") 'ºÞ¨î½s¸¹
- On Error GoTo ER
- With Sheets("ºÞ¨î¤º®e")
- Set Sh = Sheets(.Name)
- .UsedRange = ""
- End With
- On Error Resume Next
- With Sh.QueryTables.Add("URL;http://prtr.epa.gov.tw/resultEMS.aspx?emsno=" & Rng & "&tab=Panel5", Sh.[AA1])
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = """GridView5"""
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- End With
- Set Q = Sh.QueryTables(1)
- Do While Rng <> ""
- If Err = 0 And Application.Count(Q.ResultRange) > 0 Then
- With Sh.Cells(Sh.Rows.Count, 2).End(xlUp)
- .Offset(1, -1) = Rng
- If .Row = 1 Then
- .Offset(, -1) = "ºÞ¨î½s¸¹"
- Q.ResultRange.Copy .Cells
- Else
- .Resize(Q.ResultRange.Rows.Count, 1).Offset(2, -1).Value = Rng
- Q.ResultRange.Rows("2:" & Q.ResultRange.Rows.Count).Copy .Offset(1)
-
- End If
- End With
- End If
- Err.Clear
- Set Rng = Rng.Offset(1)
- Q.Connection = "URL;http://prtr.epa.gov.tw/resultEMS.aspx?emsno=" & Rng & "&tab=Panel5"
- Q.Refresh BackgroundQuery:=False
- Loop
- Q.ResultRange = ""
- With Sh
- .Columns.AutoFit
- For Each Q In .Names
- Q.Delete
- Next
- For Each Q In .QueryTables
- Q.Delete
- Next
- End With
- Application.ScreenUpdating = True
- Exit Sub
- ER:
- If Err.Number = 9 Then
- Sheets.Add.Name = "ºÞ¨î¤º®e"
- Resume
- End If
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 9
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 11
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 10
- µù¥U®É¶¡
- 2013-6-7
- ³Ì«áµn¿ý
- 2014-1-25
|
¦^´_ 13# GBKEE
¦hÁ¡A³o¼Ë¶]¥X¨Óªºµ²ªG¨S¦³°ÝÃD¤F¡C |
|
|
|
|
|
|