½Ð°Ý¦p¦ó§ì¨újavascriptªº*.csvÀɮסH
- ©«¤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
|
½Ð°Ý¦p¦ó§ì¨újavascriptªº*.csvÀɮסH
·Q½Ð±Ð¤@¤U¡A§Ún¥Î§å¦¸§ì¨úºô¶¤¤ªº*csvÀÉ¡AµM«á§â¸Ì±ªº¸ê®Æ©ñ¤Jexcelªºªí®æ¤¤¡A¦ýºô¶¤¤ªºÀɮ׳sµ²¬O¥ÎjavascriptÂð_¨Ó¡A®×¨Ò¦p¤U¡G
http://prtr.epa.gov.tw/resultEMS.aspx?emsno=A36A0770&tab=Panel5
§Ú¥´ºâ¦s©ñªºexcelÀɤw¸g¦³ºÞ¨î½s¸¹¦Cªí¡AµM«á´N®Ú¾Ú³oÓ¦Cªí¥h§ì¨ú»Ýnªº¸ê®Æ¡A¤£¹L¦b§ì*csv³oÓ¦a¤è´N¥d¦í¤F¡C
ÁÂÁÂ
|
|
|
|
|
|
|
- ©«¤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 |
|
|
|
|
|
|
- ©«¤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
- 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
|
©êºp¡Aèè¶K¿ùcode¤F¡A¦ý¤w¸g¤£¯à½s¿è¡G- Sub csv()
- Dim i As Integer, k As Integer, emsUrl As String, Rng As Range
-
- i = 1
-
- k = 1000
- Do Until k = 71000
- Set Rng = Sheets("Sheet1").Range("A" & i & "")
- emsUrl = "http://opendata.epa.gov.tw/ws/Data/EMS/?$orderby=RegistrationNo&$skip=" & i & "&$top=" & k & "&format=csv"
-
- With ActiveSheet.QueryTables.Add(Connection:="URL;" & emsUrl, Destination:=Rng)
-
- .BackgroundQuery = True
- .RefreshStyle = xlOverwriteCells
- .RefreshPeriod = 0
- .AdjustColumnWidth = False
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
-
- End With
-
- i = i + 1000
- k = k + 1000
- Loop
- 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
|
¤£¦n·N«ä¡A§Ú¬O¦]¬°«á¨Ón¨ìÀô«O¸p¥t¤@Óopendataºô¯¸§ìcsvªº®ÉÔµo²{§ì¶i¤u§@ªí³£·|Åܦ¨¶Ã½X¡A©Ò¥H¤~Áp·Q¨ì¡C
http://opendata.epa.gov.tw/Data/Contents/EMS/
³oÓºô¯¸©M¤§«e¨ºÓ²Ä¤@¼Óªººô¯¸À³¸Ó¬O³qªº¡A¦ý³o¸Ìcsv´Nª½±µ´£¨Ñ©Ò¦³³æ¦ìªººÞ¨î½s¸¹¡A¦ý¤@¦¸´£¨Ñ1000µ§¡A©Ò¥HÁ`¦@7¸U¦hµ§n¤U¸ü71¦¸csvÀɮסC
ÁöµM§Ú¥u¬On³Ì«nªººÞ¨î½s¸¹¡A¦ý¨ä¥¦³£¶Ã½XÁÙ¬Oı±o«Ü©Ç¡A¥H¤U¬O§Úªºcode¡A§ÚÁÙ¬Oªì¾ÇªÌ¡A¥Î³Ì²³æªºdo/loop¨Ó³B²z°j°é¡A¶]¨ì¤@¥b´N¥d¦í¤F¡A¤£ª¾¹D¥X¤F¤°»ò¨Æ±¡¡A¦hÁ¡I- Sub csv()
- Dim i As Integer, k As Integer, emsUrl As String
-
- Set i = 0
-
- Set k = 1000
-
- emsUrl = "http://opendata.epa.gov.tw/ws/Data/EMS/?$orderby=RegistrationNo&$skip=" & i & "&$top=" & k & "&format=csv"
-
- With ActiveSheet.QueryTables.Add(Connection:="URL;" & emsUrl, Destination:=Range("A2"))
-
- .BackgroundQuery = True
- .RefreshStyle = xlOverwriteCells
- .RefreshPeriod = 0
- .AdjustColumnWidth = False
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
-
- End With
-
- 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
|
¤Ó·P¿E¤F¡A§Ú³o´X¤Ñ×¥¿¤F¤@¨Ç¸Ì±ªºµ{¦¡½X¡AÅý¥¦¤]¥i¥H§ì§Oªº¸ê·½¡C«D±`·PÁ¡I
¶¶«K°Ý¤@¤U¡A³o¸Ì¤£§ìcsv¦Ó¬O§ìºô¶ªºtable¡A¬O¦]¬°csv¤¤¤å¶i¨Ó¬O¶Ã½X¦Ó¤SµL¸Ñªºì¦]¶Ü¡H |
|
|
|
|
|
|