簡単なデータ転送:Web サイトのデータを Excel に取り込むための 2 つの実証済みの VBA 方法
方法 1 – Web サイトから Excel VBA メッセージボックスに特定のデータをインポートする
手順:
- 開発者 に移動します。 リボンのタブをクリックします。
- [Visual Basic] をクリックします。 コードから カテゴリをクリックしてVisual Basic Editorを開きます。 。またはALT キーを押します。 +F11 Visual Basic エディタを開きます。 .
- これはVisual Basic Editorに表示されます。 .
- ツールに移動します。 メニューをクリックし、[参考資料] をクリックします。 .
- リファレンス – VBAProject ダイアログボックスが表示されます。
- 少し下にスクロールして、2 つの Microsoft を有効にします。 図書館 。 Microsoft HTML オブジェクト ライブラリにチェック マークを付けます。 およびMicrosoft インターネット コントロール .
- [OK] をクリックします。 .
- [モジュール ] をクリックします。 挿入 から ドロップダウン メニュー バー。
- これによりモジュール が作成されます ワークブック内で。
- VBA をコピーして貼り付けます。 以下に示すコード。
VBA コード:
Sub Import_SpecificData()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant
website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", website, False
request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"
request.send
response = StrConv(request.responseBody, vbUnicode)
html.body.innerHTML = response
total = html.getElementsByClassName("wikitable")(0).innerText
MsgBox total
End Sub- RubSub をクリックしてコードを実行します。 ボタン、またはキーボード ショートカット F5 を押します。 .
- これにより、データが取得され、Web サイトからそれらのデータがインポートされ、メッセージボックスに表示されます。 .
VBA コードの説明
Sub Import_SpecificData()サブ コード内の作業を処理するために使用されるコードの一部ですが、値は返されません。サブプロシージャとも呼ばれます。プロシージャに Import_SpecificData() という名前を付けました。 .
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim total As Variant薄暗い VBA のステートメント 「宣言する」を指します。 ” 変数を宣言するために使用する必要があります。
website = "https://en.wikipedia.org/wiki/2022_FIFA_World_Cup"
Set request = CreateObject("MSXML2.XMLHTTP")これにより、ウェブページリクエストを行うオブジェクトが作成されます。
request.Open "GET", website, Falseこのブロックから、どこに行くべきか、そしてそこに行く方法がわかります。この行を変更する必要はありません。Web サイトからあらゆる種類のデータをインポートするときに、この行を直接コピーできます。
request.setRequestHeader "If-Modified-Since", "Mon, 14 Nov 2022 00:00:00 GMT"新しいデータは Web サイトから取得します。
request.sendこの行はリクエストを Web ページに送信します。
response = StrConv(request.responseBody, vbUnicode)ウェブページの応答データを変数に取得します。
html.body.innerHTML = responseこのコード行は、Web ページを HTML オブジェクトに入れて、データ参照を容易にします。
total = html.getElementsByClassName("wikitable")(0).innerTextこの行では、Web ページの指定された要素の合計を取得します。クラス名を取得する必要があります。その特定の要素のクラス名を取得するには、簡単な手順に従う必要があります。
- Web サイトにアクセスし、右クリックします。
- 次に、[検査] をクリックします。 .
- これにより HTML が表示されます とCSS そのウェブサイトの要素。受講する必要があるクラスをクリックし、html.getElementsByClassName(“wikitable”) 内に配置します。 .
MsgBox totalこれにより、合計が Microsoft 形式で表示されます。 ウィンドウ。
End Subこれで手順は終了です。
方法 2 – ウェブサイトから Excel にデータをスクレイピング
手順:
- 録画を示す小さなアイコンをクリックします。 マクロ .
- 記録マクロ ダイアログが表示されます。
- マクロに名前を付けます。マクロに Get_Data という名前を付けます。 .
- [OK] をクリックします。 .
- データに移動します。 リボンのタブをクリックします。
- [ウェブから]をクリックします。 [データの取得と変換] の下 .
- ウェブから ウィンドウが表示されます。
- CTRL をクリックする +V キーボードでURLを貼り付けます。 URL内 ベーシックを選択した後のエリア .
- OK を押します。 ボタン。
- ナビゲーター 結果としてダイアログ ボックスが表示されます。
- 表示オプションから任意のデータ テーブルを選択します ニーズに合ったメニューをご提案します。
- 「読み込み先」を選択します。 。
- データのインポート ダイアログが表示されます。
- テーブルを選択します 「ワークブック内でこのデータを表示する方法を選択してください。」 」 をクリックし、既存のワークシートをクリックします。 .
- データをインポートするセルを選択します。
- [OK] をクリックします。 .
- これにより、ウェブサイトからデータがインポートされます。
- ワークシート全体を選択し、[削除] をクリックします。 ボタン、マイクロソフト エクセル ダイアログが表示されます。必ず [いいえ] をクリックしてください。 .
- これにより削除されます すべてのデータ。
- データに移動します リボンから [すべて更新] をクリックします。 クエリと接続 の下 .
- すべてのデータをワークシートに戻します。
- リボンを開き、[開発者] を選択します。 ドロップダウン メニューから選択します。
- ビジュアルベーシックを選択します。 Visual Basic エディタを開きます。 。またはVisual Basic エディタ ALT を押してアクセスすることもできます。 +F11 .
- モジュール内 、VBA マクロ を取得します。
VBA コード:
Option Explicit
Sub Get_Data()
'
' Get_Data Macro
'
'
Sheets("Get Data").Select
Range("N11").Select
ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes)", _
Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) _
& "" & Chr(10) & " Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes (Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
Workbooks("Import Data from Website .xlsm").Connections.Add2 _
"Query - 2022 FIFA bidding (majority 12 votes)", _
"Connection to the '2022 FIFA bidding (majority 12 votes)' query in the workbook." _
, Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes)"";Extended Properties=""""" _
, ""), "SELECT * FROM [2022 FIFA bidding (majority 12 votes)]", 2
Application.CommandBars("Queries and Connections").Visible = False
ActiveWorkbook.Queries.Add Name:="2022 FIFA bidding (majority 12 votes) (2)" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://en.wikipedia.org/wiki/2022_FIFA_World_Cup""))," & Chr(13) & "" & Chr(10) & " Data1 = Source{1}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data1,{{""Bidders"", type text}, {""Votes Round 1"", type text}, {""Votes Round 2"", type text}, {""Votes Round 3"", type text}, {""Votes Round 4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""2022 FIFA bidding (majority 12 votes) (2)"";Extended Propertie" _
, "s="""""), Destination:=Range("$B$2")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [2022 FIFA bidding (majority 12 votes) (2)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_2022_FIFA_bidding__majority_12_votes___2"
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 2.86
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("G:G").ColumnWidth = 12
Range("M26").Select
Application.CommandBars("Queries and Connections").Visible = False
Cells.Select
Selection.ListObject.QueryTable.Delete
Selection.ClearContents
Range("B2:F9").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B2").Select
End Sub
- これは自動マクロ コードです。
Excel の [データ] タブを使用して Web データをインポートする方法
手順:
- URLを入力します。 セルC4内 インポート データ ワークシートに記載されています。
- URLをコピーします。 CTRL を押して +C .
- データに移動します。 リボンのタブをクリックします。
- [ウェブから]をクリックします。 [データの取得と変換] の下 .
- Web からという名前のウィンドウ が表示されます。
- 基本 を選択します URL 内 フィールド、 URL を貼り付けてください CTRL を押して +V キーボードから。
- [OK] をクリックします。 ボタン。
- これによりナビゲータが表示されます。 ダイアログボックス。
- 表示オプションから要件に応じてデータ テーブルを選択します。 .
- [ロード]をクリックします。 .
- これにより、選択したデータがそのウェブサイトからインポートされ、Excel ファイルに読み込まれます。
練習ワークブックをダウンロード
ワークブックをダウンロードして練習することができます。
関連記事
- Excel VBA:Chrome を使用した Web スクレイピング
- Web サイトからテーブルを取得するための Excel VBA
- Excel VBA を使用してブラウザなしで Web スクレイピングを行う方法
-
Excelで数式をコピーするときにセル参照を保持する
数式を含むセルをコピーすると、数式内のセル参照も対応する数のセルを上下に移動することに気付くでしょう。このタイプのセル参照は、相対参照と呼ばれます。 コピーするとき（ Ctrl + C ）上の画像の数式を貼り付けて（ Ctrl + V ）別のセルに移動すると、セルの参照が Bから変更されていることがわかります。 Dへの列 列なので、合計は異なります。 セルをコピーするときにExcelがセル参照を変更しないようにする場合は、絶対参照を使用する必要があります。絶対参照を作成するには、ドル記号（ $ ）を挿入します ）次の画像に示すように、数式のセル参照の両方の部分の前にフ
-
Officeリボンメニュー設定をエクスポートおよびインポートする方法
Microsoft Officeは、多機能のリボンメニューバーを提供します。 Officeリボンメニューをカスタマイズして、タブとコマンドを配置できます。これは、Officeアプリケーションのウィンドウの上部にあるツールバーのセットです。メニューを非表示または再表示したり、都合の良いときにコマンドを置き換えたりすることができます。もう1つの良い面は、将来の参照用に保存して、ピアグループと共有できることです。 このようなパーソナライズは、ユーザーの要件に応じてエクスポートおよびインポートできます。エクスポートオプションを使用すると、リボンで行われたすべてのカスタマイズをバックアップできます。こ