Excel VBA(エクセル マクロ) 外部データの取り込みのマクロ化 Webデータ(ホームページファイル)の取得。

Excelツールで外部データの取り込みがある。
VBAでマクロ化して、Excelにファイル名を変更して読み込むようにする。

Excelで外部からWebデータを取得したい。

Excelのツールで外部データの取り込みがあるので、マクロ化して都度テキストファイル名を変更して取り込むことを考えた。


サンプルソースをダウンロードできます。

作成したマクロは以下の通りです。

Sub torikmi1()
  Dim url As String
  url = Sheets("取り込みボタン").Cells(1, 2).Value & Sheets("取り込みボタン").Cells(2, 2).Value
  With Sheets("取り込みデータ").QueryTables.Add(Connection:="URL;" & url, Destination:=Sheets("取り込みデータ").Range("A1"))
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .Refresh BackgroundQuery:=False
    .RefreshPeriod = 0
    .RefreshOnFileOpen = False
    .PreserveFormatting = True
    .AdjustColumnWidth = True
    .FillAdjacentFormulas = False
    .RefreshStyle = xlInsertEntireRows
    .SavePassword = False
    .SaveData = True
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = True
    .Refresh
    .Parent.Names(.Name).Delete
    .Delete
  End With
End Sub

外部データの取り込みをマクロ化するのにこんなに設定しなくてはいけないのと思った(実際はデフォルト設定のため設定必要なし)。

1行づつ調査したことを記述する。

With Sheets("取り込みデータ").QueryTables.Add(Connection:="URL;" & url, Destination:=Sheets("取り込みデータ").Range("A1"))


End With


QueryTables.Addを使用し、Connection:="URL;http://***"でURLに接続する。
Destination:=Sheets("取り込みデータ").Range("A1")でシート「取り込みデータ」のA1にコピーする。
これだけでは、データは取り込めない。
.RefreshのみをWith構文の中に記入すると文字化けするが取り込める。
(Connection:="TEXT;text1.txt"でテキスト(CSV)ファイルが取り込める)


外部データ取り込みの「外部データ範囲のプロパティ」部分の設定はテキストデータファイル取り込み時と同じのため、「テキストファイル取り込み」を参照願います。

.Name = "test"
.FieldNames = True
.RowNumbers = False
.Refresh BackgroundQuery:=False
.RefreshPeriod = 0
.RefreshOnFileOpen = False
.PreserveFormatting = True
.AdjustColumnWidth = True
.FillAdjacentFormulas = False
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True


次は外部データ取り込み実行時のWebクエリの編集を行う。

Webクエリの編集。

.WebSelectionType = xlAllTables
 xlEntirePage・・・ページ全体の取り込み
 xlAllTables・・・Webの表のみ取り込み

.WebFormatting = xlWebFormattingNone Webクエリのoptionの書式設定
 xlWebFormattingNone・・・指定なし
 xlWebFormattingRTF・・・リッチテキスト
 xlWebFormattingAll・・・HTML

.WebPreFormattedTextToColumns = True
 Webクエリのoptionの書式設定されて<pre>ブロックのインポート設定。
 書式設定された<pre>ブロックを列に入力するをチェック。Trueのためチェックする。
 HTML言語の<pre>で囲まれた部分を列として取り出せる。
 重要なデータが<pre>で囲まれていることが多く、これのみデータで取り出せるので活用する。

.WebConsecutiveDelimitersAsOne = True
 区切り記号が続く場合一つの区切り記号として扱うをチェック。Trueのためチェックする。
 <pre>〜</pre>の中に複数個のデータがあって区切り記号で分離している場合、列に分割できる。
 これも、重要なデータの取り出しに役立つ。(後で分離しなくて良いため)

.WebSingleBlockTextImport = False
 セクション全体に対し同じインポートを設定するをチェック。Falseのためチェックしない。
 デフォルトで設定されていないし、Trueにしてもあまり変わらなかったので必要に応じて変える。

.WebDisableDateRecognition = False
 日付認識を無効にするをチェック。Falseのためチェックしない。

.WebDisableRedirections = True
Webクエリのリダイレクトを無効にするをチェック。Trueのためチェックする。


以上で設定終わり。

設定した物を実行する。

.Refresh
取り込み実行。これが無いと取り込めない。私はこの命令を忘れて取り込めず苦労した。

.Parent.Names(.Name).Delete
設定したクエリ名を削除する。マクロ実行毎に作成されたクエリが保存されるのを防ぐ。
.Refresh BackgroundQuery:=Falseで定期更新は行わない設定にしておかないとエラーが出る可能性あり。

.Delete
今まで設定してきたクエリを削除する。

以上でテキストデータを外部ファイルウィザードを使用して取り込みを行った。

サンプルソースをダウンロードできます。


PS.

Webファイルの外部データ取り込みは少しは情報が出ているので、少しは調べられました。

こちらもテキストファイル取得同様、インターネットや本の情報が非常に少なく、苦労しました。

マクロの自動記録があるらしいので、こちらも挑戦しようと思います。


忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP