[µo°Ý] ¸ü¤Jºô­¶¸ê®Æªº°ÝÃD

yuch8663

½Ð°Ý¦b³o²Õµ{¦¡¸Ì·Q±N¤é´Á³¡¤À§ï¬°ÅܼÆ`´N·|¥X²{ "¥²¶·¬O±`¼Æ¹Bºâ¦¡"ªº¿ù»~°T®§¡AÀ³¸Ó¦p¦ó­×§ï?ÁÂÁ¡C
Sub Test()
Sheets("sheet1").Select
                                         'http://www.xe.com/currencytables/?from=USD&date=2013-06-23
Dim a, b, c As String
    a = Sheets("sheet1").Range("p1").Text ' M°_ºâ¦~
    b = Sheets("sheet1").Range("p2").Text ' M°_ºâ¤ë
    c = Sheets("sheet1").Range("p3").Text ' M°_ºâ¤é
    Const url As String = "http://www.xe.com/currencytables/?from=USD&date=" & a & "-" & b & "-" & c    ' ¥¿½Tªº
    Cells.Clear
    Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
    With ie
        .Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
        .Navigate url
        Do While .ReadyState <> 4 'µ¥«Ýºô­¶¶}±Ò
        DoEvents
        Loop
        .ExecWB 17, 2 'Select All
        .ExecWB 12, 2 'Copy selection
        Range("A1").Activate
        ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
            False, NoHTMLFormatting:=True
    End With
'   Columns("A:B").Delete    ' ±N¶×¤J®É A¡BB ¨âÄæ²¾°£¡A ­ì¥» C:I ªºÄæ¦ì¥þ³¡¥ª¾a¡A ¦¨¬° A:I
    ie.Quit
  '  MsgBox "¸ê®Æ½Æ»sµ²§ô"    ' Â÷¶}«eÅã¥Ü¤@¤pµøµ¡´£¿ô¡A«ö¥¦«á§Yµ²§ô¡C
             Dim qyt As QueryTable
            For Each qyt In Worksheets("sheet1").QueryTables
                qyt.Delete
            Next
End Sub


Book2.rar (7.56 KB)

¤U¸ü¦¸¼Æ: 10

2013-6-30 20:54

yuch8663

ÁÂÁªO¤W¦U¦ì¤j¤j¸ÑÃD¡A·|¦bµZ¿i°Ñ¸Ô¡C

HSIEN6001

§Ñ¤F!!
(ªþ¹Ï)
¤U¸ü®É,¤u§@ªí¤Ï¶Âªº²{¶H¬O³o¼Ë

¤U¸ü (41.51 KB)

2013-7-1 15:41

¦³¨S¦³¤Hª¾¹D³o­Ó°ÝÃDªº²£¥Í­ì¦]

HSIEN6001

¦^´_ 11# stillfish00
QueryTable ¦n¼Ëª¾¹D­C!  ..^_^..
¤j²¤À³¥Î´XºØ¤è¦¡±Æ°£:

(1)
Public Sub §R°£¥»¬¡­¶Ã¯¤¤©Ò¦³ªº©w¸q¦WºÙ()
For Each c In ActiveWorkbook.Names
   c.Delete
Next
End Sub
*****************
(2) ¤U¸ü¸ê®Æ to ·s¼W¬¡­¶Ã¯
*****************
(3)
·s¼W"¼È®É"¤u§@ªí
¥Î§¹Delete¸Ó¤u§@ªí
¤£¯d³s½u QueryTable
*****************
(4)
Dim QueryTable_Name As String   
.
.
.
QueryTable_Name = .Name         
.
.Names(QueryTable_Name).Delete   
---------------------
¤£ª¾¹D¬°¬Æ»ò,§Ú C:\
¦@¦³¨â­ÓProgram Files
Program Files
Program Files (x86)

1. Sub QuitIE()
   
2. Dim oShellWindows As Object, obj As Object
   
3. Set oShellWindows = CreateObject("Shell.Application").Windows
   
4.     FullName = "C:\Program Files (x86)\Internet Explorer\iexplore.exe"
   
5.    
   
6.     If oShellWindows.Count > 0 Then
   
7.         For Each obj In oShellWindows
   
8.             If StrComp(Right(obj.FullName, 12), "iexplore.exe", vbTextCompare) = 0 Then obj.Quit
   
9.         Next
   
10.     End If
    
11. End Sub

½Æ»s¥N½X

³o¼Ë¹ï¤£¹ï?!!
(¹ï¤£°_!!­Yµ{§Ç«Ü¦n¯º,§Ú¬O¯uªº¥~¦æ,¦³®ÉÀH«K®M¥Î¨Óªº)
§O¨£©Ç!!   ..^__^..

stillfish00

¦^´_ 10# HSIEN6001
FullName¤¤¬Oµ{§Çªº§¹¾ã¸ô®|¡A¹³§Úªº¬O "C:\Program Files\Internet Explorer\iexplore.exe"
¦A¥h¤ñ¸û«á­±ªº¦r§À¬Ý¬O¤£¬OIEªºµ{§Ç¦Ó¤w¡AÀ³¸Ó¤£¥Î¥[ *32§a

¥t¥~§Úı±o§A­nª`·Nªº¬OQueryTable­Y¥u¬O¥Î¨Ó¨ú±o¤@¦¸©Ê¸ê®Æ¡A³Ì¦nÁÙ¬O¶¶«K§â³s½u§R°£¡A¤£µM¨C¦¸³£·|·s¼W³s½u¡A¥H«á¸Ó¤u§@ªí´N¦³«Ü¦h¸ê®Æ³s½u...¡A¥i¯à³y¦¨¶}ÀÉÅܺCªº°ÝÃD¡C

HSIEN6001

¦^´_ 9# stillfish00


­ì¨Ó¬O¤¤Â_«á¯d¤U¨Óªº
SO...
¦pªG§ÚªºÅã¥Ü³o¼Ëiexplore.exe *32
À³¸Ó¬O§ï¬°³o¼Ë?!

1. Sub QuitIE()
   
2.   Dim oShellWindows As Object, obj As Object
   
3.   Set oShellWindows = CreateObject("Shell.Application").Windows
   
4. 
   
5.   If oShellWindows.Count > 0 Then
   
6.     For Each obj In oShellWindows
   
7.       If StrComp(Right(obj.FullName, 16), "iexplore.exe *32", vbTextCompare) = 0 Then obj.Quit
   
8.     Next
   
9.   End If
   
10. End Sub

½Æ»s¥N½X

But ­è­è´ú¸Õ...¨S¤ÏÀ³

stillfish00

¦^´_ 8# GBKEE
¥i¯à¤UÂ_ÂI«á«öF8°±¤î¡A¤]·|´Ý¯d¡C
¤£©¯ªº¬OIE¤£¯à¥ÎGetObject¨ú±oª«¥ó¨Óµ²§ô±¼¡A
©³¤U¬O§ä¨ìªº¥t¥~¤@ºØ¤èªk¨Ñ°Ñ¦Ò

1. Sub QuitIE()
   
2.   Dim oShellWindows As Object, obj As Object
   
3. 
   
4.   Set oShellWindows = CreateObject("Shell.Application").Windows
   
5. 
   
6.   If oShellWindows.Count > 0 Then
   
7.     For Each obj In oShellWindows
   
8.       If StrComp(Right(obj.FullName, 12), "iexplore.exe", vbTextCompare) = 0 Then obj.Quit
   
9.     Next
   
10.   End If
    
11. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 7# HSIEN6001
ÁöµM¬O On Error Resume Next       '***¦³¨Çtable¨SRows¸ê®Æ·|²£¥Í¿ù»~ ¤£²z·|¥¦,µ{¦¡Ä~Äò¨«
'
'
¦ý³Ì«áÁÙ¬O·| .Quit        'Ãö³¬ºô­¶
·Ó¹D²z¤£·|´Ý¯dIEªº.
­n¥ÎVBA Ãö³¬´Ý¯dIE,ÁÙ¨S¸Õ¹L.

HSIEN6001

¦^´_ 6# GBKEE

¹ï!
Ãþ¦ü³o¼ËªºVBA¶}±ÒªºIE
§Ú²q...¬O§_¨S¦³§ä¨ìtable®ÉªºOn Error Resume Next
©Ò¯d¤U¨Óªº,²Ö¿n¤Ó¦h´N¼vÅT«áÄò¤U¸ü
¥u¯à¤â°Ê¤@­Ó­Ó±j¨î "µ²§ô³B²zµ{§Ç" or ­«¶}¾÷
¦³¨S¦³µ{§Ç¥i¥HÃö³¬¨º¨Ç´Ý¯d....

1.     With CreateObject("InternetExplorer.Application")
   
2.         .Visible = False
   
3.         .navigate "http://...."
   
4.         Do Until .readyState = READYSTATE_COMPLETE
   
5.             DoEvents
   
6.         Loop
   
7.         Set A = .document.getElementsByTagName("table")
   
8.         With Workbooks.Add
   
9.             On Error Resume Next       '***¦³¨Çtable¨SRows¸ê®Æ·|²£¥Í¿ù»~ ¤£²z·|¥¦,µ{¦¡Ä~Äò¨«
   
10.                 For ii = 11 To A.Length - 1
    
11.                     For i = 0 To A(ii).Rows.Length - 1
    
12.                         k = k + 1       '
    
13.                         For j = 0 To 2
    
14.                             Cells(k + 1, j + 1) = A(ii).Rows(i).Cells(j).innerText
    
15.                         Next
    
16.                     Next
    
17.                 Next
    
18.         End With
    
19.         .Quit        'Ãö³¬ºô­¶
    
20.     End With

½Æ»s¥N½X

********************************
"¦³®É" ·|¥X²{²Ä¤G¦¸©I¥s®É,¤u§@ªí¤Ï¶Âªºµe­±.....¨º´N¤£¬O¦³¨S¦³¥[µùPublicªº°ÝÃD
§ÚÁÙ¨S§ä¨ì¬°¦ó "¦³®É" ¤u§@ªí·|¤Ï¶Âªº­ì¦]

GBKEE

¦^´_ 1# yuch8663

1. Sub Test()
   
2. Dim url As String
   
3.     'url = "URL;http://www.xe.com/zh-HK/currencytables/?from=USD&date=" & a & "-" & b & "-" & c   ' ¥¿½Tªº
   
4.                                                                         'A,B,C -> YYYY-MM-DD
   
5.     url = "URL;http://www.xe.com/zh-HK/currencytables/?from=USD&date=" & Format(Date - 1, "YYYY-MM-DD")
   
6.                                                                         '¤é´Á¶·¤p©ó·í¤é
   
7.     With Sheets("Sheet1")
   
8.         .Cells.Clear
   
9.         For Each e In .QueryTables
   
10.             e.Delete
    
11.         Next
    
12.         With .QueryTables.Add(Connection:=url, Destination:=.[a1])
    
13.             .WebSelectionType = xlSpecifiedTables
    
14.             .WebFormatting = xlWebFormattingNone
    
15.             .WebTables = """historicalRateTbl"""
    
16.             .WebConsecutiveDelimitersAsOne = True
    
17.             .WebSingleBlockTextImport = False
    
18.             .WebDisableDateRecognition = False
    
19.             .WebDisableRedirections = False
    
20.             .Refresh BackgroundQuery:=False
    
21.         End With
    
22.     End With
    
23. End Sub

½Æ»s¥N½X

¦^´_ 5# HSIEN6001
Sub EX¾ã­¶¤U¸ü(¼g¤Jªí®æ, ¼g¤J¦ì¸m, theURL)   ¨S¥[µù¬O Public ´N¬O¤½¥Îµ{§Ç ¥iª½±µ©I¥s

Private Sub EX¾ã­¶¤U¸ü(¼g¤Jªí®æ, ¼g¤J¦ì¸m, theURL)  ¥[µù¬°¨p¥Îµ{§Ç
¨p¥Îµ{§Ç ¥ÎRUN ©I¥s
Run "A¤U¸ü¤½¥Îµ{¦¡.EX¾ã­¶¤U¸ü", "¼È®É", Sheets("¼È®É").Range("B" & Rows.Count).End(xlUp).Offset(1), "http://siis.twse.com.tw/server-java/STAMAK03?TYPEK=otc&isnew=&colorchg=1&year=102&smonth=01&emonth=12&step=1&order=12"

¤u§@ºÞ²z­û¤¤¦³¨SÃö³¬ªºIE,¥i¯à¬O¦bVBA¤¤¶}±Ò«á¨SÃö³¬§a