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

²£¥Í¦h¾lªº¦WºÙ

²£¥Í¦h¾lªº¦WºÙ

½Ð°Ý¦U¦ì¤j¤j
     §Ú°õ¦æ©³¤U³o¨â¬qµ{¦¡,·|©w¸q¦h¾lªº¦WºÙ"2012_F3_1_10_1101.php?STK_NO=1101&myear=2012",¥B¤@ª½­«ÂЩw¸q,­n¦p¦óÅý¥L¤£¥Î¦Û°Ê©w¸q¦WºÙ
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.twse.com.tw/ch/trading/exchange/FMSRFK/genpage/Report" & year1 & "01/" & year1 & "_F3_1_10_" & stock_id & ".php?STK_NO=" & stock_id & "&myear=" & year1 & "" _
        , Destination:=Range("A6"))
        .Name = "2012_F3_1_10_1101.php?STK_NO=1101&myear=2012"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "8"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
..........................
...........................
..............................
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = [B2].Address Then
        With Sheets("test")
            If WorksheetFunction.CountIf(Sheets("Sheet3").Range("A2:A1425"), .[B2]) = 1 Then
             Call test
             Else
             Call test3
            End If
        End With
    End If
    Application.EnableEvents = True
End Sub

ÁÂÁÂÀ°¦£

¦^´_ 1# ddhh4053
WEB¬d¸ßªº©w¸q¦WºÙ¬O¥²»Ýªº
­Y¨S¦³µ¹¥¦©w¸q¦WºÙ¡A«hµLªk°µ¸ê®Æ§ó·s¡A¤]´N¬OEXCEL±N·|²¾°£¦¹¬d¸ß
¦P²z¡A­Y§A¥u»Ý¬d¸ß¤@¦¸¨Ã¤£»Ý­n¤é«á§@¸ê®Æ§ó·s
¨º»ò¦bWith ActiveSheet.QueryTables.Addªº°Ï¬q±Ô­zµ²§ô«e¥[¤J.Delete
±N¬d¸ß§R°£§Y¥i
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

½Ð°Ý §Ú¸ÕµÛ±N.Delete¥[¦b.Refresh BackgroundQuery:=False«e«á­±:
.Delete
.Refresh BackgroundQuery:=False
»P
.Refresh BackgroundQuery:=False
.Delete
µ²ªG¥[¦b«e­±¥X²{¦¹³B¶·­nª«¥ó
¥[¦b«á­±¨S¦³®ÄªG,
¦³¨S¦³¨ä¥L¤èªk
ÁÂÁ«ü¾É

TOP

¦^´_ 3# ddhh4053

        .Refresh BackgroundQuery:=False
        .Delete
     End With
    ³o¼ËÀ³¸Ó´N¦b¦WºÙ¤º¨S¦³·s¼W¤@­Ó¦WºÙ¤F
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

§Ú¸Õ¹LÁÙ¬O·|¤@ª½²Ö¿n°Ñ·Óªº¦WºÙ ¬Ý¨Ó¥u¯à·s¼Wtemp¤u§@ªí

TOP

¦^´_ 5# ddhh4053

play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# ddhh4053
¥ý°õ¦æEx
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Q As QueryTable
  4.     With ActiveSheet
  5.         For Each Q In .QueryTables
  6.             Q.Delete
  7.         Next
  8.     End With
  9. End Sub
½Æ»s¥N½X
µM«á¦b ActiveSheet.QueryTables.Add ¤¤ ¥[¤W.Delete ¬Ý¬Ý
  1. .Refresh BackgroundQuery:=False
  2.         .Delete
  3.      End With
½Æ»s¥N½X

TOP

ÁÂÁ¨â¦ì¤j¤jªºÀ°¦£
  §Ú¤]«Ü¯Ç´e¬°¤°»ò§Ú¥[¤F¨º¤@¦æ«o¤£¦æ
.Refresh BackgroundQuery:=False
        .Delete
     End With
¦Ó§Aªº½T¥i¥H §¹¥þ¤@¼ËªºªF¦è

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD