このマクロは、対象サイトの都合で使用不可になりました。
Sub MyXlTokyoNpNews()
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Rem 東京新聞サイト[東京新聞の記事]ページ取り込み処理
  Rem 言語:Excel VBA
  Rem 機能...
  Rem   東京新聞サイトの[東京新聞の記事]ページを、[外部データの取り込み]とHttpRequestで取り込む。
  Rem 注記...
  Rem
  Rem 履歴...
  Rem   第01版:2007/02/18 作成。
  Rem   第02版:2007/03/02 OnActionの指定を変更。
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Rem 参照設定する場合...
  Rem   Microsoft Internet Controls
  Rem   Microsoft HTML Object Library
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Dim myCmmdBar As CommandBar
  Dim myCtrlBttn As CommandBarControl
  Dim myCtrlBttnDeTail As CommandBarControl
  Dim myCtrlBttnLastest As CommandBarControl
  Dim myCtrlBttnOk As CommandBarControl
  Dim myCtrlBttnCancel As CommandBarControl
  Dim x As Long
  Dim y As Long
  Dim myFaceId As Long
  Dim myCbox(1) As Boolean
  Dim myAns As Long
  Dim myMsg As String
  '
  Dim myTitle As String
  Dim myMaxRow As Long
  Dim myName As String
  Dim i As Long
  Dim myFlag As Long
  Dim myFind As Variant
  Dim myStatusBar As String
  Dim myMax As Long
  '
  Dim myIE As Variant ' InternetExplorer
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  myTitle = "MyXlTokyoNpNews"
  '
  On Error Resume Next
  CommandBars(myTitle).Delete
  On Error GoTo 0
  '
  Set myCmmdBar = CommandBars.Add(Name:="MyXlTokyoNpNews", Position:=msoBarPopup, Temporary:=True)
  Set myCtrlBttn = myCmmdBar.Controls.Add(Type:=msoControlButton, Before:=1, Temporary:=True)
  Set myCtrlBttnDeTail = myCmmdBar.Controls.Add(Type:=msoControlButton, Before:=2, Temporary:=True)
  Set myCtrlBttnLastest = myCmmdBar.Controls.Add(Type:=msoControlButton, Before:=3, Temporary:=True)
  Set myCtrlBttnOk = myCmmdBar.Controls.Add(Type:=msoControlButton, Before:=4, Temporary:=True)
  Set myCtrlBttnCancel = myCmmdBar.Controls.Add(Type:=msoControlButton, Before:=5, Temporary:=True)
  '
  myMsg = myTitle & vbCrLf & vbCrLf
  myMsg = myMsg & "東京新聞サイト" & vbCrLf
  myMsg = myMsg & "[東京新聞の記事]ページ" & vbCrLf
  myMsg = myMsg & "取り込み処理" & vbCrLf & vbCrLf
  '
  With myCtrlBttn
    .DescriptionText = "東京新聞サイト[東京新聞の記事]ページ取り込み処理ポップアップメニュー"
    .Style = msoButtonIconAndWrapCaption
    .Caption = myMsg & "処理を実行しますか?"
    .TooltipText = "処理を実行しますか?"
    .FaceId = 1089
    myFaceId = .FaceId
  End With
  '
  With myCtrlBttnDeTail
    .DescriptionText = "[ニュースの本文も取り込む]ボタン"
    .BeginGroup = True
    .Style = msoButtonIconAndCaption
    .Caption = "ニュースの本文も取り込む"
    .TooltipText = "ニュースの本文は取り込まない。"
    .FaceId = 6963
    .OnAction = "'" & myTitle & "BttnMyDetail" & " " & Chr(&H22) & myTitle & ChrW(&H22) & "'"
  End With
  '
  With myCtrlBttnLastest
    .DescriptionText = "[最新日のみ本文を取り込む]ボタン"
    .BeginGroup = True
    .Style = msoButtonIconAndCaption
    .Caption = "最新刊のみ本文を取り込む"
    .TooltipText = "最新刊のみ本文を取り込む。"
    .FaceId = 220
    .OnAction = "'" & myTitle & "BttnMyLastest" & " " & Chr(&H22) & myTitle & ChrW(&H22) & "'"
  End With
  '
  With myCtrlBttnOk
    .DescriptionText = "[OK]ボタン"
    .BeginGroup = True
    .Style = msoButtonIconAndCaption
    .Caption = "OK"
    .TooltipText = "処理をを実行します。"
    .FaceId = 459
    .OnAction = "'" & myTitle & "BttnMyValue" & " " & Chr(&H22) & myTitle & ChrW(&H22) & ", " & "459" & "'"
  End With
  '
  With myCtrlBttnCancel
    .DescriptionText = "[キャンセル]ボタン"
    .Style = msoButtonIconAndCaption
    .Caption = "キャンセル" & Space(24)
    .TooltipText = "処理を中止します。"
    .FaceId = 330
    .OnAction = "'" & myTitle & "BttnMyValue" & " " & Chr(&H22) & myTitle & ChrW(&H22) & ", " & "330" & "'"
  End With
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  x = ActiveWindow.PointsToScreenPixelsX(0)
  y = ActiveWindow.PointsToScreenPixelsY(0)
  Beep
  Do
    On Error Resume Next
    myCmmdBar.ShowPopup x, y
    On Error GoTo 0
    DoEvents
    If myCmmdBar.Controls(1).FaceId <> myFaceId Then Exit Do
  Loop
  '
  Select Case myCmmdBar.Controls(1).FaceId
    Case 459
      myAns = vbOK
    Case 330
      myAns = vbCancel
  End Select
  '
  If myCmmdBar.Controls(2).FaceId = 220 Then
    myCbox(0) = True
  Else
    myCbox(0) = False
  End If
  '
  If myCmmdBar.Controls(3).FaceId = 220 Then
    myCbox(1) = True
  Else
    myCbox(1) = False
  End If
  '
  On Error Resume Next
  myCmmdBar.Delete
  On Error GoTo 0
  '
  If myAns = vbCancel Then Exit Sub
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  myStatusBar = "□東京新聞サイト[東京新聞の記事]ページ 取り込み開始"
  Application.StatusBar = myTitle & ": " & myStatusBar
  '
  With ActiveSheet.QueryTables.Add(Connection:="FINDER;http://www.tokyo-np.co.jp/news.shtml", Destination:=Range("A1"))
    .Name = "news.shtml"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = "7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  ActiveCell.SpecialCells(xlLastCell).Select
  myMaxRow = ActiveCell.Row
  For i = 5 To myMaxRow
    If Range("A" & i).Interior.ColorIndex <> xlColorIndexNone Then
      Range("B" & i).Value = Range("A" & i).Value
      Range("B" & i).Font.Bold = True
      Range("B" & i).Interior.ColorIndex = 1
    End If
  Next ' i
  '
  Range("A1").Select
  myName = Range("B1").Value
  '
  For i = myMaxRow To 5 Step -1
    If Range("B" & i).Value = "" Then
      Range("B" & i).Select
      Selection.Delete Shift:=xlUp
    End If
  Next ' i
  '
  Range("B4").Select
  Application.FindFormat.Clear
  With Application.FindFormat.Interior
    .ColorIndex = 1
    .Pattern = xlSolid
  End With
  '
  Set myFind = Cells.Find(What:="", SearchFormat:=True)
  Do While Not myFind Is Nothing
    With myFind
      .Select
      .Interior.ColorIndex = 34
      .EntireRow.Insert
      .Offset(1, 0).Select
    End With
    Set myFind = Cells.Find(What:="", SearchFormat:=True)
  Loop
  Application.FindFormat.Clear
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  Range("B5:B" & myMaxRow).Select
  Selection.Copy
  Sheets.Add After:=Sheets(ActiveSheet.Name)
  ActiveSheet.Name = myName
  Selection.PasteSpecial Paste:=xlPasteColumnWidths
  ActiveSheet.Paste
  Range("A1").Select
  '
  ActiveSheet.Previous.Select
  Application.DisplayAlerts = False
  ActiveSheet.Delete
  Application.DisplayAlerts = True
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  Application.CommandBars("Task Pane").Visible = False
  Columns("A:A").ColumnWidth = 50
  ActiveWindow.Zoom = 75
  '
  If myCbox(0) = True Then
    ActiveSheet.UsedRange.Select
    ActiveCell.SpecialCells(xlLastCell).Select
    myMaxRow = ActiveCell.Row
    '
    myMax = myMaxRow
    For i = 3 To myMaxRow
      Range("A" & i).Select
      If Selection.Hyperlinks.Count <> 0 Then
        Range("B" & i).Value = Selection.Hyperlinks(1).Address
      End If
      If myCbox(1) = True Then
        If Range("B" & i).Value = "" Then
          myMax = i
          Exit For
        End If
      End If
    Next ' i
    '
    Set myIE = CreateObject("InternetExplorer.Application")
    Range("B1").Select
    For i = 3 To myMax
      If Range("B" & i).Value <> "" Then
        Range("B" & i).Select
        Call MyXlTokyoNpNewsBody(Range("B" & i).Value, myIE)
      End If
      myStatusBar = "□東京新聞サイト[東京新聞の記事]ページ 本文 取り込み中 "
      myStatusBar = myStatusBar & i * 100 \ myMax & "% "
      myStatusBar = myStatusBar & i & "/" & myMax & "行 "
      Application.StatusBar = myTitle & ": " & myStatusBar
      DoEvents
    Next ' i
    Rem myIE.Visible = True
    myIE.Quit
  End If
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  Range("A1").Select
  Application.ScreenUpdating = True
  myStatusBar = "処理が終了しました。"
  Application.StatusBar = myTitle & ": " & myStatusBar & Now()
  Application.Speech.Speak myStatusBar, False
  '
  Set myIE = Nothing
End Sub ' MyXlTokyoNpNews *----*----*    *----*----*    *----*----*    *----*----*

Sub MyXlTokyoNpNewsBody(myURL As String, myIE As Variant)
  Dim myHTTP As Variant ' IXMLHTTPRequest
  Dim myDoc As Variant ' MSHTML.HTMLDocument
  Dim mySubTags As Variant ' DispHTMLElementCollection
  Dim mySubTag As Variant ' HTMLHeaderElement
  '
  Dim myText As String
  Dim myValue As String
  Dim myStatusBar As String
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  Set myHTTP = CreateObject("MSXML2.XMLHTTP") ' ("Microsoft.XMLHTTP")
  '
  With myIE
    .Navigate "about:blank"
    .Visible = False ' True
    Do While .Busy
      DoEvents
    Loop
    DoEvents
    Set myDoc = .Document
  End With
  '
  Call myHTTP.Open("GET", myURL, False)
  Call myHTTP.Send
  If myHTTP.readyState <> 4 Then ' 4:READYSTATE_COMPLETE
    myStatusBar = "接続できません。" & vbCr & myHTTP.statusText & vbCr
    myStatusBar = myStatusBar & "myHTTP.readyState: " & myHTTP.readyState & vbCr
    myStatusBar = myStatusBar & "URL: " & myURL
    MsgBox myStatusBar, vbOKOnly, "MyXlKanaloco"
    GoTo MyXlTokyoNpNewsBodySubExit
  End If
  If myHTTP.Status <> 200 Then
    myStatusBar = "接続できません。" & vbCr & myHTTP.statusText & vbCr
    myStatusBar = myStatusBar & "myHTTP.Status: " & myHTTP.Status & vbCr
    myStatusBar = myStatusBar & "URL: " & myURL
    MsgBox myStatusBar, vbOKOnly, "MyXlKanaloco"
    GoTo MyXlTokyoNpNewsBodySubExit
  End If
  '
  myText = StrConv(myHTTP.responseBody, vbUnicode)
  myText = Replace(myText, "<LINK ", "<!-- LINK ", 1, 1)
  myText = Replace(myText, "<title>", " --> <title>", 1, 1)
  myText = Replace(myText, "<img ", "<!-- img ", 1, 2)
  myText = Replace(myText, "<script ", "<!-- script ", 1, 1)
  myText = Replace(myText, " src=", " Zzzsrc=")
  myText = Replace(myText, " SRC=", " Zzzsrc=")
  myText = Replace(myText, "<BR><BR>", "<BR>")
  myText = Replace(myText, "<br><br>", "<br>")
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  myDoc.write myText
  myDoc.Title = myURL
  myValue = ""
  With ActiveCell
    .ColumnWidth = 120
    .VerticalAlignment = xlTop
    .WrapText = True
  End With
  '
  If InStr(myText, "H2") < InStr(myText, "H3") Then
    Set mySubTags = myDoc.getElementsByTagName("H2")
    If Not mySubTags Is Nothing Then
      For Each mySubTag In mySubTags
        myValue = myValue & mySubTag.innertext & vbLf
      Next ' mySubTag
    End If
    Set mySubTags = myDoc.getElementsByTagName("H3")
    If Not mySubTags Is Nothing Then
      For Each mySubTag In mySubTags
        myValue = myValue & mySubTag.innertext & vbLf
      Next ' mySubTag
    End If
  Else
    Set mySubTags = myDoc.getElementsByTagName("H3")
    If Not mySubTags Is Nothing Then
      For Each mySubTag In mySubTags
        myValue = myValue & mySubTag.innertext & vbLf
      Next ' mySubTag
    End If
    Set mySubTags = myDoc.getElementsByTagName("H2")
    If Not mySubTags Is Nothing Then
      For Each mySubTag In mySubTags
        myValue = myValue & mySubTag.innertext & vbLf
      Next ' mySubTag
    End If
  End If
  '
  Set mySubTags = myDoc.getElementsByTagName("P")
  If Not mySubTags Is Nothing Then
    For Each mySubTag In mySubTags
      myValue = myValue & mySubTag.innertext & vbLf
    Next ' mySubTag
  End If
  ActiveCell.Value = myValue
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
MyXlTokyoNpNewsBodySubExit:
  Set myDoc = Nothing
  Set myHTTP = Nothing
End Sub ' MyXlTokyoNpNewsBody *----*----*    *----*----*    *----*----*    *----*----*
  
Sub MyXlTokyoNpNewsBttnMyValue(myTitle As String, myValue As Long)
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Rem コマンドボタンOnAction処理
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  CommandBars(myTitle).Controls(1).FaceId = myValue
End Sub ' MyXlTokyoNpNewsBttnMyValue *----*----*    *----*----*    *----*----*    *----*----*

Sub MyXlTokyoNpNewsBttnMyDetail(myTitle As String)
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Rem [ニュースの本文も取り込む]ボタン処理
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  With CommandBars(myTitle).Controls(2)
    If .FaceId = 6963 Then
      .FaceId = 220
      .TooltipText = "ニュースの本文も取り込む。"
    Else
     .FaceId = 6963
     .TooltipText = "ニュースの本文は取り込まない。"
    End If
  End With
End Sub ' MyXlTokyoNpNewsBttnMyDetail *----*----*    *----*----*    *----*----*    *----*----*

Sub MyXlTokyoNpNewsBttnMyLastest(myTitle As String)
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  Rem [ニュースの本文も取り込む]ボタン処理
  Rem *----*----*    *----*----*    *----*----*    *----*----*
  '
  With CommandBars(myTitle).Controls(3)
    If .FaceId = 220 Then
      .FaceId = 6963
      .TooltipText = "総て本文を取り込む。"
    Else
     .FaceId = 220
     .TooltipText = "最新刊のみ本文を取り込む。"
    End If
  End With
End Sub ' MyXlTokyoNpNewsBttnMyLastest *----*----*    *----*----*    *----*----*    *----*----*
inserted by FC2 system