ªð¦^¦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¨º¸Ì?

¦^´_ 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

[ª©¥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

¦^´_ 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»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD