はじめに

ExcelVBAを利用してあるWEBサイトからファイルを取得したい場合があります。そんな時はVBAやWindows APIを利用しwininet.dllを使用するための定義や、ダウンロード用定義のURLDownloadToFile等を入力します。

定義入力が済めば、後は実際にダウンロード処理をマクロに書き実行させるという流れになります。

ExcelVBAといえば事務的な計算処理を自動実行させたり、Excelファイルのシート上で定型的な処理をさせたりすることが多いと思いますが、今回はクローリングやスクレイピングと呼ばれる処理になります。

ただ、今回はWEBサイトからファイルを取得するだけですので、取得したファイルの解析処理等は含みません。

※Officeでは32bit版と64bit版がありますので、ご自身の環境に合わせて記述してください。一応、両方の定義を記載します。

インターネットサービス・オープン用定義(32bit版、64bit版)

まずはインターネット接続用の定義を設定します。

'========================================
'インターネットサービスのオープン
'========================================
''Office 32 bit用
'Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
'(ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, _
'ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long

'Office 64 bit用
Public Declare PtrSafe Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, _
ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long

インターネットサービス・クローズ用定義(32bit版、64bit版)

マクロの処理の最後にはちゃんとクローズ処理を入れましょう。

'========================================
'インターネットサービスのクローズ
'========================================
'Office 32 bit用
'Public Declare Function InternetCloseHandle Lib "wininet.dll" _
'(ByVal HINTERNET As Long) As Integer

'Office 64 bit用
Public Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" _
(ByVal HINTERNET As Long) As Integer

ダウンロード用定義

インターネット接続後にファイルをダウンロードしますので、ダウンロード用定義を設定します。

'========================================
'ダウンロード用定義
'========================================
'Office 32 bit用
'Public Declare Function URLDownloadToFile Lib "urlmon" _
'Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
'ByVal szURL As String, ByVal szFileName As String, _
'ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

'Office 64 bit用
Public Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

FTPサーバ接続用定義(32bit版、64bit版)

FTPサーバに接続するための定義を設定します。

'========================================
'FTPサーバへ接続
'========================================
''Office 32 bit用
'Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
'(ByVal HINTERNET As Long, ByVal lpszServerName As String, ByVal nServerPort As Integer, _
'ByVal lpszUsername As String, ByVal lpszPassword As String, ByVal dwService As Long, _
'ByVal dwFlags As Long, ByVal dwContext As Long) As Long

'Office 64 bit用
Public Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal HINTERNET As Long, ByVal lpszServerName As String, ByVal nServerPort As Integer, _
ByVal lpszUsername As String, ByVal lpszPassword As String, ByVal dwService As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Long


FTPサーバ・カレントディレクトリ取得用定義(32bit版、64bit版)

FTPサーバログイン後のカレントディレクトリ取得用定義もこの際設定します。

'========================================
'FTPサーバのカレントディレクトリを取得
'========================================
'Office 32 bit用
'Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
'(ByVal hConnect As Long, ByVal lpszCurrentDirectory As String, _
'ByRef lpdwCurrentDirectory As Long) As Boolean

'Office 64 bit用
Public Declare PtrSafe Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
(ByVal hConnect As Long, ByVal lpszCurrentDirectory As String, _
ByRef lpdwCurrentDirectory As Long) As Boolean

FTPサーバ・カレントディレクトリ変更用定義(32bit版、64bit版)

FTPサーバログイン後のカレントディレクトリ変更用定義もこの際設定します。

'========================================
'FTPサーバのカレントディレクトリを変更
'========================================
'Office 32 bit用
'Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
'(ByVal hConnect As Long, ByVal lpszDirectory As String) As Long

'Office 64 bit用
Public Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
(ByVal hConnect As Long, ByVal lpszDirectory As String) As Long

FTPサーバ・ファイル転送用定義(32bit版、64bit版)

FTPを利用しファイルを転送するための定義も設定します。

'========================================
'FTPサーバへファイルを転送
'========================================
'Office 32 bit用
'Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
'(ByVal hConnect As Long, ByVal lpszLocalFile As String, _
'ByVal lpszNewRemoteFile As String, _
'ByVal dwFlags As Long, ByVal dwContext As Long) As Long

'Office 64 bit用
Public Declare PtrSafe Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hConnect As Long, ByVal lpszLocalFile As String, _
ByVal lpszNewRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Long

上記がコアとなる定義になりますが、インターネット接続時のHTTP使用ポートやFTPポートについては適宜追加してください。

Excelファイル・ダウンロード用プログラム(サンプル)

無事、定義の入力が済んだら次は実際のプログラムを入力していきます。サンプル程度ですが、ファイルダウンロード用関数を記載します。

※main処理から呼び出す形で処理させます。標準モジュールでconfigを作り、その中に定数等を定義しています。

Sub DownloadExcelFile(str_save_dir_path)
  '========================================
  ' Excelファイル・ダウンロード処理
  '========================================
  MsgBox "Excelファイルをダウンロードします。"

  Dim download_url As String
  Dim excel_file_path As String
  Dim dl_failed_flag As Integer
  Dim cnt As Integer
  Dim result As Long

  'ダウンロードURL
  download_url = "※実際のURLを記載"

  '保存先の指定
  excel_file_path = str_save_dir_path & "\" & "ダウンロードしたエクセルファイル名"

  cnt = 1
  dl_failed_flag = 0
  Do
    'Excelファイルをダウンロード
    result = URLDownloadToFile(0, download_url, excel_file_path, 0, 0)

    If (result = 0) Then
      MsgBox "ダウンロード成功!"
    End If

    '判定
    If (cnt >= config.LOOP_MAX_COUNT) Then
      MsgBox "Excelファイルの取得に失敗しました(ループ回数オーバー)。" _
          & "〇〇のWEBサイトを確認してください。処理を終了します。"
      dl_failed_flag = 1
      Exit Do
    End If

    cnt = cnt + 1

    '再取得時にsleepを入れます。
    Sleep config.SLEEP_TIME

  Loop Until result = 0

  'DLが失敗したのでマクロを終了させます。
  If dl_failed_flag = 1 Then
    End
  End If
  
End Sub

ファイルダウンロードを実行し、上手く取得できなかった場合はスリープを入れて再取得させます。sleep関数を使う場合は別途、以下のAPI定義が必要です。

sleep関数用のAPI宣言

'========================================
'sleep関数用のAPI宣言
'========================================
'Office 32 bit用
'Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'Office 64 bit用
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

まとめ

WEBサイト等、インターネットからファイルを取得するクローリングやスクレイピング処理が他の言語でもありますが、VBAではまずAPI用の定義を記載してから実際の処理をVBAで書きます。

Excelマクロでプログラムを作るときがよくありますが、他の言語と比べて非常に使いにくいです。かなり面倒くさいです。

他の言語では組み込み関数でサクッと実装できる処理が、マクロだといちいち長ったらしい定義用文字列を設定したり、最初は非常に骨が折れます。

ただ、その苦労を超えてプログラムが上手く動いたときはかなり嬉しいですね。この嬉しさや楽しさがあるからこそ、プログラム作りは飽きないのだと思います。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です