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

[µo°Ý] ºô­¶¸ê®Æ¶×¤JExcel¤¤¤åÅã¥Ü²§±`

[µo°Ý] ºô­¶¸ê®Æ¶×¤JExcel¤¤¤åÅã¥Ü²§±`

¨Ï¥Î¤U¦Cµ{¦¡§ì¨úºô­¶¸ê®Æ(ºô§}¬°¤½¥q¸ê®ÆµLªk´£¨Ñ),¦³ªººô­¶¤¤¤å¥i¥¿½TÅã¥Ü,¦ý¦³ªººô­¶¤¤¤å¬°¶Ã½X(À³¸Ó¬°¨ä¥L¤å¦r½X)
Àˬdºô­¶½s½X,¤¤¤åÅã¥Ü¥¿±`ªº¬°"ÁcÅ餤¤å(BIG5),²§±`ªº¬°"Unicode(UFT-8)",½Ð°Ý»P³o­Ó¦³Ãö¶Ü??
¦pªG¬O³o­ì¦]³y¦¨ªº,½Ð°Ýµ{¦¡­n¦p¦ó­×¥¿,ÁÂÁÂ



¨Ï¥ÎVB§ì¨úºô­¶¸ê®Æ,¨C¦¸¶}±ÒExcel«á³£·|¸ß°Ý"¬d¸ß§ó·s"
¬O§_¦³³]©w¥i¥H­×¥¿


Sub ·s¼W¬d¸ß()
    Range("A3:AA65526").Clear
    Range("A3").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;ºô§}", Destination:=Selection)
             .Name = "Alarm"
        .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 = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False        
    End With
End Sub

¨Ï¥Î¤U¦Cµ{¦¡§ì¨úºô­¶¸ê®Æ(ºô§}¬°¤½¥q¸ê®ÆµLªk´£¨Ñ),¦³ªººô­¶¤¤¤å¥i¥¿½TÅã¥Ü,¦ý¦³ªººô­¶¤¤¤å¬°¶Ã½X(À³¸Ó¬°¨ä ...
jcchiang µoªí©ó 2013-2-7 11:02


§Aªº·N«ä¬O»¡¦³¦h­Óºô­¶­nŪ¨ú,
¦³ªººô­¶½s½X¬OBIG5½X,
¦³ªº¬O UTF-8,
¦Ó¥u­n½s½X¬O UTF-8 ªººô­¶Åª¹L¨Óªº¸ê®Æ³£·|¬O¶Ã½X¶Ü?

­Y¬O¦p¦¹¨º§A¥i¯à­n¥Î¤H¤u­«·s¥´¦r¥HÂл\¶Ã½X¦r,
¦]¬°©ó¤£¦P½s½X¤U©ÒÅã¥Ü¥X¨Óªº¤å¦r¥²µM¤£¦P.

©ÎªÌ¬O§A§ä¬Ý¬Ý¬O§_¦³Âà½X¥\¯àªº³nÅé,
´£¨ÑÃþ¦ü½Ķ³nÅ骺¥\¯à,
§â UTF-8 ½s½Xªº¤å¦r¶K¨ì¤@­Ó¤å¦r®Ø¤º,
¥t¤@­Ó¤å¦r®Ø²£¥Í¬Û¦P¤å¦r¦ý¬O¥Î BIG5 ¨Ó½s½X¥X¨Óªº,
§Ú¤£ª¾¹D¦³¨S¦³³oºØ³nÅé.

­Y¤£¬O¤W­±©Ò»¡¦Ó¯Â¥u¬O·Q½Õ¾ã Excel Ū¨úºô­¶¤å¦r®Éªº½s½X,
§Ú§ä¤F¤@¤U,
©Î³\§A¥i°Ñ¦Ò DefaultWebOptions ª«¥ó ©Î WebOptions ª«¥óªº Encoding ÄÝ©Ê.

TOP

¦^´_ 2# luhpro

§Ú¬O­n§ì¨ú¤@­Óºô­¶¸ê®Æ,µo²{§ì¥X¨Óªº¸ê®Æ¤¤¤å³£¬O¶Ã½X
¥Î¬Û¦Pªºµ{¦¡´ú¸Õ§ì¨ú¨ä¥Lºô­¶¸ê®Æ¨ÃµL¦¹²{¶H
¤~·|¸ß°Ý¬O§_»Pºô­¶½s½X¦³¬ÛÃö¶Ü
¦³§ä¨ì¤@½g¤å³¹¬O¥ý±Nºô­¶¸ê®ÆÂର.HTMÀÉ,¦A±N¸ê®ÆÂà¤JExcel,«h¥i¥¿±`Åã¥Ü
¥u¬O·|¦h²£¥Í¤@­Ó.HTMÀÉ
Sub Ex5()
    Dim Fs As Object, Url As String, OkFile As String, MyQuery As QueryTable
    Url = "'ºô§}"
    OkFile = "C:\OK.HTM"                              '¤U¸ü«á¦sÀɦWºÙ
    Range("A3:AA65526").Clear
    Range("A3").Select
    With CreateObject("Microsoft.XMLHTTP")
        .Open "get", Url, False
        .send
        Set Fs = CreateObject("Scripting.FileSystemObject").CreateTextFile(OkFile, True)
                                     '«Ø¥ß¤@¤å¦rÀÉ(True:Àɮצp¦s¦b ¥iÂл\ÂÂÀÉ®×)
        Fs.WriteLine .responseText   '¼g¤J¤å¦rÀÉ
        Fs.Close
        With ThisWorkbook.Sheets(1)  '¤u§@ªí¤¤¶×¤JHTMÀÉ
            Set MyQuery = .QueryTables.Add("FINDER;file:///" & OkFile, .[A3])
            MyQuery.WebTables = "2"
            MyQuery.WebFormatting = xlWebFormattingNone
            MyQuery.Refresh 0
        End With
  End With
End Sub
   
¤j¤j©Ò´£ªº¤èªk§Ú·|¦A¥h¬ã¨s¬ã¨s
ÁÂÁ§Aªº«ü¾É

TOP

        ÀR«ä¦Û¦b : ¦Y­W¤F­W¡B­WºÉ¤Ü¨Ó¡A¨ÉºÖ¤FºÖ¡BºÖºÉ´d¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD