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

[µo°Ý] Â^¨ú¤WÂdªÑ»ù¦æ±¡~¹J¨ì«D¥æ©ö¤é§Y¸õ¥X¿ù»~°T®§!

[µo°Ý] Â^¨ú¤WÂdªÑ»ù¦æ±¡~¹J¨ì«D¥æ©ö¤é§Y¸õ¥X¿ù»~°T®§!

¦U¦ì¤j¤j:

¦A¦¸½Ð±Ð...
§Ú§ì¤WÂd³sÄò¦h¤éªº¦¬½L¦æ±¡®É,
¹J¨ì°²¤é(«D¥æ©ö¤é)®É,vba³£·|¥X²{¥H¤U°T®§~~°õ¦æ¶¥¬q¿ù»~'1004'-µLªk¶}±Ò³sµ²...


¸Óºô­¶(¤WÂd¨C¤é¦¬½L¦æ±¡¦³Ãöªº­ì©l½X¦p¤U


½Ð°Ý¸Ó«ç»ò­×§ï¥H¤Uªºvba¥N½X,
¨Ï¨ä¥i¥H¥ý§P©w·í¤é¦³¨S¦³¥æ©ö¸ê®Æ,¦A¶i¦æ¸ê®ÆÂ^¨ú/©Î¸õ¦Ü¤U¤@­Ó¥æ©ö¤é ???

(§ì¤W¥«¦¬½L»ù®É´Nºâ¹J¨ì°²¤é¤]¤£·|¥X²{¦¹¤@°ÝÃD,¦ý§ì¤WÂd¦¬½L»ù®É,«h·|¦³¿ù»~°T®§...)

·PÁÂ!!!
  1. '¤WÂd­ÓªÑ¾ú¥v¸ê®Æ
  2.    With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_" & myear & mmon & nowday & ".html" _
  3.         , Destination:=ActiveCell.Offset(2, 0))
  4.    
  5.      
  6.         .FieldNames = True
  7.         .RowNumbers = False
  8.         .FillAdjacentFormulas = True
  9.         .PreserveFormatting = True
  10.         .RefreshOnFileOpen = False
  11.         .BackgroundQuery = True
  12.         .RefreshStyle = xlInsertDeleteCells
  13.         .SavePassword = False
  14.         .SaveData = False
  15.         .AdjustColumnWidth = True
  16.         .RefreshPeriod = 0
  17.         .WebSelectionType = xlSpecifiedTables
  18.         .WebFormatting = xlWebFormattingNone
  19.         .WebTables = "1,2,3"
  20.         .WebPreFormattedTextToColumns = True
  21.         .WebConsecutiveDelimitersAsOne = True
  22.         .WebSingleBlockTextImport = False
  23.         .WebDisableDateRecognition = False
  24.         .WebDisableRedirections = False
  25.         .Refresh BackgroundQuery:=False
  26.     End With
  27.    
½Æ»s¥N½X
·s¤â¤W¸ô

¦^´_ 1# cudui


¿ù»~°T®§³£¦b~µ{¦¡run¨ì .refresh BackgroundQuery:=False ®É¥X²{error(°õ¦æ¶¥¬q¿ù»~1004)
·s¤â¤W¸ô

TOP

¦^´_ 1# cudui


    ½Ð°Ý¸Ó«ç»ò­×§ï¥H¤Uªºvba¥N½X,
¨Ï¨ä¥i¥H¥ý§P©w·í¤é¦³¨S¦³¥æ©ö¸ê®Æ,¦A¶i¦æ¸ê®ÆÂ^¨ú/©Î¸õ¦Ü¤U¤@­Ó¥æ©ö¤é ???

   §Aªº°ÝÃD«ÜÅ¢²Î...¨Sªk§¹¾ã¦^µª......¦pªG¬O¤@­Ó¥i¥¿½Trunªºvba,¤]­n¥ý»¡©ú§Aªº­ÓÅÞ¿è...

    ³Ì²³æªº¤èªk¬O±N¾ã¦~ªº¥æ©ö¤é´Á­p¹º®³¨ì,¤ñ¹ï±ý§ì¨úªº¤é´Á¥Ø¼Ð,¨Ì§P©wÅܧóreportªºhtml.....

TOP

http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_1010419.CSV
§A¤S§ì¨ì¨S¦³¸ê®Æªººô­¶¤F....

TOP

''¤WÂd­ÓªÑ¾ú¥v¸ê®Æ
   With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_" & myear & mmon & nowday & ".CSV" _
        , Destination:=ActiveCell.Offset(2, 0))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1,2,3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
   
   
    Range("a3:a2200").Select
       Selection.TextToColumns Destination:=Range("b4"), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
       Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
         Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), _
         Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
    Range("a4:a2200").Delete
    Rows("4:4").Select
    Selection.Delete Shift:=xlUp
    Columns("A:O").Select
    Range("a1").Activate
    Selection.ColumnWidth = 8
   
End Sub

¸Õ¸Õ¬Ý§a

TOP

''¤WÂd­ÓªÑ¾ú¥v¸ê®Æ
   With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.otc.org.tw/ch/st ...
chen_cook µoªí©ó 2012-4-20 07:14



    ³o¬O¿ù»~ªº¸ê®Æ

TOP

¦^´_ 2# cudui
  1. On Error Resume Next
  2.     With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_" & myear & mmon & nowday & ".html" _
  3.         , Destination:=ActiveCell.Offset(2, 0))
  4.         .WebSelectionType = xlEntirePage
  5.         .WebFormatting = xlWebFormattingNone
  6.         .WebPreFormattedTextToColumns = True
  7.         .WebConsecutiveDelimitersAsOne = True
  8.         .WebSingleBlockTextImport = False
  9.         .WebDisableDateRecognition = False
  10.         .WebDisableRedirections = False
  11.         .Refresh BackgroundQuery:=False
  12.         If Err.Number <> 0 Then Err.Clear: MsgBox "¸ê®Æ¬d¸ß¥¢±Ñ"
  13.     End With
½Æ»s¥N½X

TOP

http://www.otc.org.tw/ch/stock/aftertrading/otc_quotes_no1430/SQUOTE_AL_1010419.CSV
§A¤S§ì¨ì¨S¦³¸ê®Æªººô­¶¤F....


³oºô­¶§Ú½T©w¬O¥i¥Hrun±o¨ì¸ê®Æ¤U¨Óªº~~
¥æ©ö¤é¨S¦³°ÝÃD,¦ý¬O«D¥æ©ö®ÉµLdata®É¤~·|¥X¿ù~


   §Aªº°ÝÃD«ÜÅ¢²Î...¨Sªk§¹¾ã¦^µª......¦pªG¬O¤@­Ó¥i¥¿½Trunªºvba,¤]­n¥ý»¡©ú§Aªº­ÓÅÞ¿è...
    ³Ì²³æªº¤èªk¬O±N¾ã¦~ªº¥æ©ö¤é´Á­p¹º®³¨ì,¤ñ¹ï±ý§ì¨úªº¤é´Á¥Ø¼Ð,¨Ì§P©wÅܧóreportªºhtml.....


¥æ©ö¤é¨C¦~³£¤£¦P,©Ò¥H¥Î¤ñ¹ïªº¤èªk,·|¤ñ¸û³Â·Ð~
§Úªº°ÝÃD¥i¯àÁÙ¤£°÷©ú½T,
§Æ±æ±o¨ìªº¸Ñµª¬O:¦p¦ó¦b¸Óvba¤º,²K¥[§PÂ_¦¡---§PÂ_­Y¹J¨ì"¬d¸ß¥¢±Ñ"©Î"¬dµL¸ê®Æ"®É,¥i¥H goto NEXT one,Ä~Äò¥¼§¹ªº¬d¸ß,¦Ó«D¸õ¥X....
(¦]¬°¤£ª¾¹D·í htmlµL¸ê®Æ¥iÂ^¨ú®É,·|¦^õX¤°»ò¼Ëªº°T¸¹...)
·s¤â¤W¸ô

TOP

¦^´_ 7# GBKEE
  1. If Err.Number <> 0 Then Err.Clear: MsgBox "¸ê®Æ¬d¸ß¥¢±Ñ"
½Æ»s¥N½X
½Ð°ÝG¤j, ¸Ó¦æ¥i¥H§ï¦¨¤U¦C¼gªk¶Ü?
If Err.Number <> 0 Then
GOTO ABC123
END IF
....

ABC123:
Next J ....



ÁÂÁÂ!!
·s¤â¤W¸ô

TOP

''¤WÂd­ÓªÑ¾ú¥v¸ê®Æ
   With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.otc.org.tw/ch/st ...
chen_cook µoªí©ó 2012-4-20 07:14


ÁÂÁ¤j¤j¤F,
§Ú¦A¸Õ¸ÕCSV & Html°õ¦æ¦³¤°»ò®t§O~~
·s¤â¤W¸ô

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD