ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¤@­Ó¥ÎVBA±qºô­¶¨ú±o·Q­n¸ê®Æªº¼gªk~

[µo°Ý] ¤@­Ó¥ÎVBA±qºô­¶¨ú±o·Q­n¸ê®Æªº¼gªk~

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-1-15 12:59 ½s¿è

³Â·Ð¦U¦ì¤j¤jÀ°¦£¡AÁÂÁ¡A·P®¦~~~~¡C

»¡©ú

§Ú­nµo°Ýªº°ÝÃD~.jpg
2012-4-28 15:00


½Ð°Ý~~¡C§Ú·Q¥ÎVBA»s§@¤@­ÓBUTTON¡A·í§Ú«ö¤U®É¯à±q¤U­±ºô§}®»¨ì§Ú«ü©wªº¸ê®Æ¡A¨Ã©ñ¤JexcelªºÀx¦s®æ¤º¡C¸Ó«ç»ò¼g©O¡I
§Ú¥u­n®» ¥H¤U¤ù¬qªº¸ê®Æ
               
                    ±i¼Æ             ¦ûªÑ¥»¤ñ¨Ò
¸³ºÊ«ùªÑ          21,712         6.04%
¶°«O®w¦s          364,848      101.44%
¤»¤é§¡¶q          1,834            0.51%  

¬ÛÃöºô§}
http://jsjustweb.jihsun.com.tw/z/zc/zcx/zcx_6257.asp.htm
http://jsjustweb.jihsun.com.tw///z/zc/zcx/zcxD1.djjs?A=6257"

ª÷®`~¡I§Ú¥u¯à®»¨ì¥¦ªº   ­ÓªÑ°ò¥»¸ê®Æ
¬°¤°»ò®»¤£¨ì§Ú­nªº¸ê®Æ©O¡H
Private Sub CommandButton1_Click()
Dim webURL As String
webURL = "URL;http://jsjustweb.jihsun.com.tw/z/zc/zcx/zcx_6257.asp.htm"
With ActiveSheet.QueryTables.Add(Connection:=webURL, Destination:=Range("A1"))
        ' xlOverwriteCells ªí¥ÜÂл\Äæ¦ì
        .RefreshStyle = xlOverwriteCells
                .WebTables = "1"
        .Refresh BackgroundQuery:=False
End With
End Sub

TOP

¦^´_ 2# vesperlee


    ¦n¹³»PJAVA¦³Ãö..°ê®õ¤]¬O¦P¼Ëªººô­¶,µLªk¶×¤J Webtable =1 ¥¿½T¬O 2©Î3
    ¨ä¥¦ÃҨ馳¬Û¦Pªº¸ê®Æ,¥h§ì¥¦ªº¨Ó®M§a!!!
    ¨ä¥¦ªº­n¨D¦Û¤v·Q§a!!

TOP

¥»©«³Ì«á¥Ñ white5168 ©ó 2012-5-1 08:37 ½s¿è

ªÑ²¼¦WºÙ¤Î¥N½X¬O¦bºô­¶¸ü¤J§¹¦¨«á¡A¤~¥Î JavaScript °ÊºA²£¥Íªº
©Ò¥H Web Query §ì¤£¨ì
¦]¬°ºô­¶¸ê®ÆÁÙ³æ¯Â¡A«Øij¥ÎVBA¶}±Òºô­¶«á½Æ»sªí®æ¨ìexcel,¦A¿ï¦Û¤v­nªº³¡¥÷§Y¥i
Ãö©óªÑ²¼¥N½Xªº³¡¥÷½Ð¦Û¦æ­×§ï,
¹ï©ó§ì³o¼Ëªº¸ê®Æ§Ú³£¬O¥Îpython,¦Ó¥B³t«×¤W¤]¤ñVBA§Ö«Ü¦h
PS:¼gµ{¦¡®É½Ð°O±o¥[¤Jµù¸Ñ,¥H¾i¦¨¨}¦nªº¼¶¼g²ßºD,¤]Åý«á­±ªº¤H¥i¥H¤è«K¾Ç²ß
  1. Sub Test()
  2.     Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zck/zck_6257.asp.htm"
  3.     Cells.Clear
  4.     Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
  5.     With ie
  6.         .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
  7.         .Navigate url
  8.         Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
  9.             DoEvents
  10.         Loop
  11.         .ExecWB 17, 2 'Select All
  12.         .ExecWB 12, 2 'Copy selection
  13.         Sheets("Sheet1").Cells.Select
  14.         Range("A1").Activate
  15.         ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  16.                 False, NoHTMLFormatting:=True
  17.     End With
  18.     Columns("A:B").Delete
  19.     ie.Quit
  20.     MsgBox "¸ê®Æ½Æ»sµ²§ô"
  21. End Sub
½Æ»s¥N½X
³o¸Ì¤ñ¸û·|¦³°ÝÃD¬Ý¤£À´ªº¦a¤è¤j·§´N¬OExecWB
§Úªþ¤WMSDNªº³sµ²,½Ð¦Û¦æ°Ñ¦Ò http://msdn.microsoft.com/en-us/library/aa752087(v=vs.85).aspx

TOP

vesperlee
4¼Ó¤j¤j ¦^ÂЪº¤£¬O§A«ü©wªººô­¶,½Ð§A¦Û¦æ½Õ¾ã§Y¥i¹F¦¨;¦]«D¶×¤J©Ò¥H®æ¦¡·|¤£¦P´N¬O¤F!!

TOP

¦^´_ 4# white5168
¦^´_ 2# vesperlee
½Ð¸Õ¸Õ¬Ý¡I
  1. Private Sub CommandButton1_Click()
  2.     ' Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zck/zck_6257.asp.htm"
  3.     Const url As String = "http://jsjustweb.jihsun.com.tw/z/zc/zcx/zcx_6257.asp.htm"     ' ¥¿½Tªº

  4.     Cells.Clear

  5.     Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"

  6.     With ie
  7.         .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
  8.         .Navigate url
  9.         Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
  10.         DoEvents
  11.         Loop
  12.         .ExecWB 17, 2 'Select All
  13.         .ExecWB 12, 2 'Copy selection
  14.         Range("A1").Activate
  15.         ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
  16.             False, NoHTMLFormatting:=True
  17.     End With

  18.     Columns("A:B").Delete    ' ±N¶×¤J®É A¡BB ¨âÄæ²¾°£¡A ­ì¥» C:I ªºÄæ¦ì¥þ³¡¥ª¾a¡A ¦¨¬° A:I
  19.     ie.Quit
  20.     MsgBox "¸ê®Æ½Æ»sµ²§ô"    ' Â÷¶}«eÅã¥Ü¤@¤pµøµ¡´£¿ô¡A«ö¥¦«á§Yµ²§ô¡C
  21. End Sub
½Æ»s¥N½X
01.png
2012-5-1 16:42

TOP

¤p§Ì´£¨Ñ¥t¤@­Ó¤è¦¡¥Îxmlhttp¨Ó§ì¨ú, µM«á¦b¦Û¤v¸ÑªR»Ý­nªº¸ê®Æ
§Ú¤§«e¬O¥Î¦basp¤W, vbaÀ³¸Ó¤]¬O¥i¥H¨Ï¥Îªº
Function getHTTPPage(ByVal url As String)

Dim objXmlHttp As Object

objXmlHttp = Server.CreateObject("Microsoft.XMLHTTP")
objXmlHttp.Open("GET", url, False)

objXmlHttp.setRequestHeader("Content-Type", "text/html")
objXmlHttp.setRequestHeader("charset", "BIG5")

objXmlHttp.Send()

getHTTPPage = BytesToBstr(objXmlHttp.ResponseBody, "BIG5")

End Function

Function BytesToBstr(ByVal body() As Byte, ByVal CSet As String)

Dim objStream As Object

objStream = Server.CreateObject("ADODB.Stream")
objStream.Type = 1
objStream.Mode = 3
objStream.Open()
objStream.Write(body)
objStream.Position = 0
objStream.Type = 2
objStream.Charset = CSet

BytesToBstr = objStream.ReadText()

objStream.Close()

End Function

TOP

¦^´_ 7# arksu
objXmlHttp.Open("GET", url, False) µ¥¼Ò²ÕµLªk¨Ï¥Î¡A
¥i¯à¶·­n¦A¤J¨ä¬Û¹ï DLL¡A¦ý§Ú¹ï VBA ¤£±E¡A
½Ð°Ý¸Ó¦p¦ó³]©w¡H

TOP

¦^´_ 8# c_c_lai


§Ú²³æ¼g¤F­Ó½d¨Ò, ±z°Ñ¦Ò¤@¤U

excel¤@¶}±Ò´N§ì¨úºô­¶¤º®e¥Îmsgbox¨q¥X¨Ó

ademo.zip (14.29 KB)

TOP

ÁÙ¬O¤£¤F¸ÑExecWB ªº¹B§@
¦ý¬O·PÁ¤À¨É!

TOP

        ÀR«ä¦Û¦b : ¡i°µ¤Hªº¶}©l¡j¨C¤@¤Ñ³£¬O¬G¤Hªº¶}©l¡A¨C¤@­Ó®É¨è³£¬O¦Û¤vªºÄµ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD