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

[µo°Ý] (EXCEL VBA°ÝÃD)½Ð°Ý«ç¼Ë¥ÎQueryTable§ì¨úºô­¶¸ê®Æ?

[µo°Ý] (EXCEL VBA°ÝÃD)½Ð°Ý«ç¼Ë¥ÎQueryTable§ì¨úºô­¶¸ê®Æ?

(EXCEL VBA°ÝÃD)½Ð°Ý«ç¼Ë¥ÎQueryTable§ì¨úºô­¶¸ê®Æ?
ºô§}:www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=20171229&type=ALLBUT0999
§Ú»Ý­nªº¬O¤¤¶¡³¡¥÷
106¦~12¤ë29¤é¨C¤é¦¬½L¦æ±¡(¥þ³¡(¤£§tÅvÃÒ¡B¤ûºµÃÒ))

¨Ï¥Î¿ý»s¥¨¶°¤è¦¡

sub test()

With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html& date =20171229&type=ALLBUT0999" _
        , Destination:=Range("A1"))
        .Name = "MI_INDEX?response=html&date=20171229&type=ALLBUT0999"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

±N¨ä¤¤ªº¤é´Á20171229§ï¬°
" & Range("a1") & Range("b1") & Range("c1") & "
«oµo²{¤£¥i¦æ
a1,b1,c1¤À§O¿é¤J2017,12,27
¥X²{ªºÁÙ¬O20171229ªº

¤£ª¾¹D°ÝÃD¥X¦b¨º¸Ì?

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-1-1 07:25 ½s¿è

¦^´_ 1# paul3063
  1. =html& date =
½Æ»s¥N½X
ºô§}¿ù»~¶Ç¦^³Ìªñ¤@¤Ñªº¸ê®Æ,,»Ý¨S¦³ªÅ®æ
  1. =html&date=
½Æ»s¥N½X
  1. http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=" & Range("A1") & Range("B1") & Range("C1") & "&type=ALLBUT0999"
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


GBKEE¤j¡A³s³oºØ®t¤@­ÓªÅ¥ÕÁ䪺¿ù»~³£§ä±o¥X¨Ó¡A±z¯uªº¬O¤Ó¼F®`¤F¡C
¥t¥~¡A·Q¦A°Ý¤@¤U¡A¦pªG·Q§â¤é´Á¿é¤J§ï¥ÎINUTBOX¤è¦¡¡Aµ{¦¡½X­n«ç¼Ë­×§ï?

TOP

¦^´_ 3# paul3063
  1. Option Explicit
  2. Sub EX()
  3.     Dim xDay As String
  4.     xDay = InputBox("¿é¤J¤é´Á", "¤j½L²Î­p¸ê°T¤é´Á", Date)
  5.     If IsDate(xDay) Then
  6.         If Weekday(xDay, vbMonday) < 6 Then
  7.             xDay = Format(xDay, "yyyymmdd")
  8.             With ActiveSheet.QueryTables.Add(Connection:= _
  9.                 "URL;http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html& date =" & xDay & "&type=ALLBUT0999" _
  10.                 , Destination:=Range("A1"))
  11.                 '
  12.                 '
  13.             End With
  14.         Else
  15.             MsgBox xDay & "  °²¤é¨SÀç·~"
  16.         End If
  17.     Else
  18.         MsgBox "¤é´Á¿é¤J¿ù»~"
  19.     End If
  20. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2018/1/4 19:03): ªþÀɬݬÝ

¦^´_ 4# GBKEE

GBKEE¤j¡A¸Õ¹L¤F¡A¦n¹³¤£¦æ¡A°õ¦æ¥¨¶°¡A¨S¦³¤ÏÀ³¡C

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2018/1/6 06:02): ½Ð¥Îªþ¥ó¤W¶Ç,¥i¥ÎÂX®i¦W: chm, pdf, zip, rar, tar, gz, bzip2, gif, jpg, jpeg, png

¦^´_ 4# GBKEE


GBKEE¤j¡AÀɮ׺ô§}¦p¤U½Ð¤U¸ü¡A
Download Page Link
https://www.sendspace.com/file/onxcag

TOP

¦^´_ 4# GBKEE


    GBKEE¤j¡AÀɮפw¤W¶Ç¡A INPUTBOX+QUERYTABLE.rar (7.65 KB)

TOP

­×§ï§ì¨úªí®æ°Ñ¼Æ§A¸Õ¬Ý¬Ý

Sub Ex()
        Dim xDay As String
        xDay = InputBox("¿é¤J¤é´Á", "¤j½L²Î­p¸ê°T¤é´Á", Date)
        If IsDate(xDay) Then
            If Weekday(xDay, vbMonday) < 6 Then
                xDay = Format(xDay, "yyyymmdd")
                With ActiveSheet.QueryTables.Add(Connection:= _
                    "URL;http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=" & xDay & "&type=ALLBUT0999" _
                    , Destination:=Range("A1"))
                    .WebFormatting = xlNone
                    .RefreshStyle = xlInsertDeleteCells
                    .AdjustColumnWidth = True
                    .Refresh BackgroundQuery:=True
                End With
            Else
                MsgBox xDay & "  °²¤é¨SÀç·~"
            End If
        Else
            MsgBox "¤é´Á¿é¤J¿ù»~"
        End If
    End Sub
Rogersg

TOP

¦^´_ 8# rogersg


rogersg¤j¡A¥i¥H¤F¡A¸U¤À·PÁ¡C

±z¸òGBKEE¤jªºµ{¦¡½X¥u¦h¤F
.WebFormatting = xlNone
                    .RefreshStyle = xlInsertDeleteCells
                    .AdjustColumnWidth = True
                    .Refresh BackgroundQuery:=True
·Q¤£¨ì³ºµM¥i¦æ¡A¯u¬O¥O¤H³Y²§¡C

TOP

¦^´_ 9# paul3063


ÃD¥~¸Ü¡A
¨ä¹ê¤¤¶¡¦³¸ÕµÛ±N¥¨¶°§ï¦¨
Sub test()
X = InputBox()
With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.twse.com.tw/exchangeReport/MI_INDEX?response=html&date=" & X & "&type=ALLBUT0999" _
        , Destination:=Range("A1"))


µ²ªG¥X²{¡A
½sĶ¿ù»~:¤Þ¼Æ¤£¬°¿ï¾Ü©Ê
©Ò¥H¤~¤S°Ý¤FGBKEE¤j«á­±ªº°ÝÃD

Á`¤§¡AÁÂÁÂGBKEE¤j¸òrogersg¤j

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD