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

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

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

½Ð°Ý¦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)

¦^´_ 1# yuch8663

°Ñ¦Ò! ¶×²v.rar (20.08 KB)

TOP

¦^´_ 1# yuch8663
Const ³¯­z¦¡
  1. »yªk
  2. [Public | Private] Const constname [As type] = expression
  3. Const ³¯­z¦¡ªº»yªk¨ã¦³¤U¦C´X¶µ³æ¤¸¡G
  4. ³æ¤¸ ´y­z
  5. Public ¿ï¾Ü©Ê¤Þ¼Æ¡C¼Ò²Õ¼h¦¸©Ò¨Ï¥ÎªºÃöÁä¦r¡A¥Î¨Ó«Å§i¦b©Ò¦³¼Ò²Õùتº©Ò¦³µ{§Ç¤¤³£¥i¥H¨Ï¥Î±`¼Æ¡C ¤£¥i¦bµ{§Ç¤¤¨Ï¥Î¦¹¶µ«Å§i¡C
  6. Private ¿ï¾Ü©Ê¤Þ¼Æ¡C¼Ò²Õ¼h¦¸ªºÃöÁä¦r¡A¥Î¨Ó«Å§i¥u¦b¤w«Å§iªº¼Ò²Õùؤ~¥i¥H¨Ï¥Î±`¼Æ¡C¤£¥i¦bµ{§Ç¤¤¨Ï¥Î¦¹¶µ«Å§i¡C
  7. Constname ¥²­nªº¤Þ¼Æ¡C±`¼Æªº¦WºÙ¡A¿í´`ÅܼƩR¦W³W®æ¡C
  8. Type ¿ï¾Ü©Ê¤Þ¼Æ¡C±`¼Æªº¸ê®Æ«¬ºA¡A¥i¥H¬O Byte¡BBoolean¡BInteger¡BLong¡BCurrency¡BSingle¡BDouble¡BDecimal (¥Ø«e¥¼¤ä´©¡^¡BDate¡BString¡A©Î¬O Variant¡C¨Ï¥Î As type ¤l¥y¨Ó«Å§i±`¼Æ¡C
  9. Expression ¥²­nªº¤Þ¼Æ¡C¤å¦r¡B¨ä¥¦±`¼Æ©Î¥ô¦óºâ³N¤ÎÅÞ¿è¹Bºâ¤lªº²Õ¦X¡A¦ý Is «h°£¥~¡C
  10. ½Ðª`·N
  11. ±`¼Æ¬Ò¹w³]¬°¨p¦³ªº¡Aµ{§Ç¤¤ªº±`¼Æ³£¬O¨p¦³ªº¡A±`¼Æªº¥i¨£©Ê¤£¥i¥H§ïÅÜ¡C¦b¤@¯ë¼Ò²ÕùØ¡A¼Ò²Õ¼h¦¸±`¼Æªº¦³®Ä½d³ò¥i¥H¥Î Public ÃöÁä¦r¨Ó§ïÅÜ¡CµM¦Ó¡A¦bª«¥óÃþ§O¼Ò²ÕùØ¡A±`¼Æ¥u¯à¬°¨p¦³ÅܼơA¥BµLªk¥Ñ Public ÃöÁä¦r¨Ó§ïÅܨä¥i¨£©Ê¡C
  12. ­Y­n§â¦h­Ó±`¼Æ«Å§i¦¡©ñ¦b¤@¦æùØ¡A«h¥Î³r¸¹¨Ó¤À¹j¨C­Ó±`¼Æ«ü©w¦¡¡C·í¦h­Ó±`¼Æ«Å§i©ñ¦b¦P¤@¦æùØ¡A­Y¨Ï¥Î Public ©Î Private ÃöÁä¦r¡A«h¾A¥Î©ó¥þ³¡¦P¤@¦æªº±`¼Æ¡C
  13. '*******************************
  14. «ü©wµ¹±`¼Æªº¹Bºâ¦¡¤£¥i¥H¬OÅܼơB¨Ï¥ÎªÌ¦Û­q¨ç¼Æ©Î Visual Basic ¤º«Øªº¨ç¼Æ (¨Ò¦p Chr¡^¡C
  15. '********************************
  16. ªþµù ±`¼Æ¥i¥H¨Ï±zªºµ{¦¡¸û®e©ö­×§ï¡C©MÅܼƤ£¤@¼Ëªº¬O¡A¦bµ{¦¡°õ¦æ®É¡A±`¼Æ¥i¥HÁקK¤£¤p¤ß¿ù»~ªº­×§ï¡C
  17. ¦pªG¨S¦³©ú½Tªº¨Ï¥Î As type ¨Ó«Å§i±`¼Æªº«¬ºA¡A«h±`¼Æ·|¿ï¥Î³Ì¾A¦X expression ªº«¬ºA¡C
  18. ¦b Sub¡BFunction ©Î Property µ{§Çùةҫŧiªº±`¼Æ¡A¥u¾A¥Î¦b¸Óµ{§Çªº°Ï°ìùØ¡C¦bµ{§Ç¥~©Ò«Å§iªº±`¼Æ¡A«h¬O¦b«Å§i¥¦ªº¼Ò²Õ¤¤©Ò©w¸qªº¡C¦b¥ô¦ó¥i¥H¨Ï¥Î¹Bºâ¦¡ªº¦a¤è¡A´N¥i¥H¨Ï¥Î±`¼Æ¡C
½Æ»s¥N½X
¥i­×§ï¦p¤U
  1. url = "http://www.xe.com/currencytables/?from=USD&date=" & a & "-" & b & "-" & c    ' ¥¿½Tªº
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁ¤À¨É, ¦n¦n®ø¤Æ¤@¤U
ss

TOP

¦^´_ 3# GBKEE
½Ð±Ð,Ãö©ó Public ¤Î Call

Call ¼Ò²Õ¦WºÙ.µ{§Ç¦WºÙ
NOTE:¦bµ{§Ç¤¤¥i¥H©I¥s¦P¤@¼Ò²Õ¤¤ªº¥ô¦óµ{§Ç,¦ý¹ï©ó¨ä¥¦¼Ò²Õ¤¤ªºµ{§Ç¥u¯à©I¥s³]©w¬°Publicªº±M®×¤Î¼Ò²Õµ{§Ç¡C
¤W­±³o¬q»¡©ú¬O®Ñ¤W¼gªº¡A¦³ÂI§Ë¤£À´¡C

§Ú¦b°õ¦æ®É,Áö¤£¦P¼Ò²Õ,«o¥i©I¥s¤£¦P¼Ò²Õ,«o¨S³]©wPublic ªº¼Ò²Õ
½Ð°Ý³o·|¦³ °¸º¸ bugªº°ÝÃD?!

§í©Î.....§Úªº½d¨Ò,³o¼Ë¨â¦¸ call µ{§Çªº¼gªk¬O¿ùªº
¦]¬° "¦³®É" ·|¥X²{²Ä¤G¦¸©I¥s®É,¤u§@ªí¤Ï¶Âªºµe­±

¥t¥~¤@°Ý:
­Y§Q¥Î IE¤U¸üºô­¶¸ê®Æ®É,¤u§@ºÞ²z­ûªº³B²zµ{§Ç¸Ì
®É±`¦³¥¼Ãö³¬ªºiexplore.exe
³o¸Ó«ç»ò³B²z??

Public´£°Ý.rar (9.33 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-1 10:25 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ HSIEN6001 ©ó 2013-7-1 10:31 ½s¿è

¦^´_ 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§@ªí·|¤Ï¶Âªº­ì¦]

TOP

¦^´_ 7# HSIEN6001
ÁöµM¬O On Error Resume Next       '***¦³¨Çtable¨SRows¸ê®Æ·|²£¥Í¿ù»~ ¤£²z·|¥¦,µ{¦¡Ä~Äò¨«
'
'
¦ý³Ì«áÁÙ¬O·| .Quit        'Ãö³¬ºô­¶
·Ó¹D²z¤£·|´Ý¯dIEªº.
­n¥ÎVBA Ãö³¬´Ý¯dIE,ÁÙ¨S¸Õ¹L.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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.   Set oShellWindows = CreateObject("Shell.Application").Windows

  4.   If oShellWindows.Count > 0 Then
  5.     For Each obj In oShellWindows
  6.       If StrComp(Right(obj.FullName, 12), "iexplore.exe", vbTextCompare) = 0 Then obj.Quit
  7.     Next
  8.   End If
  9. End Sub
½Æ»s¥N½X

TOP

¦^´_ 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.   If oShellWindows.Count > 0 Then
  5.     For Each obj In oShellWindows
  6.       If StrComp(Right(obj.FullName, 16), "iexplore.exe *32", vbTextCompare) = 0 Then obj.Quit
  7.     Next
  8.   End If
  9. End Sub
½Æ»s¥N½X
But ­è­è´ú¸Õ...¨S¤ÏÀ³

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD