Access

【Access VBA】Excel の指定ブックの指定シート内でセル領域をコピーする

【Access VBA】Excel の指定ブックの指定シート内でセル領域をコピーする

Excel の指定ブックの指定シート内でセル領域をコピーする

この記事では「Excel の指定ブックの指定シート内でセル領域をコピーする」ために利用可能な CopyCellsInSheet というユーザー定義関数について紹介します。 この情報が読者のお役に立てば幸いです。

解決できること

Microsoft Access の中でここで紹介する CopyCellsInSheet をマクロの中で呼び出すと ％USERPROFILE% からの相対パスで指定された Excel の入力ブックの指定シート内の指定セル範囲を 同一シート内の指定セル範囲へ 簡単にコピーできます。

ここに ％USERPROFILE% は Windows であらかじめ定義されている環境変数で Windows PCを使っている利用者のデフォルトのフォルダパスを表しています。この環境変数については必要に応じて以下の記事を参照下さい。

【Windows業務効率化】バッチファイルの基本テクニックを知ろう
【Windows業務効率化】バッチファイルの基本テクニックを知ろう

読んでほしい方

こうした Microsoft Access VBA を使ったアプリケーションをこれから作ろうと思っている方、今まで VBA によるアプリケーションを作ってきたが、そのツールを第三者に提供する際に、フォルダの保存パス名等の利用者個人に紐づいた環境差分をVBAのプログラムの中にじかに書くのに抵抗がある方。

CopyCellsInSheet 関数 の紹介

説明

％USERPROFILE% からの相対パスで指定された Excel の入力ブックの指定シート内の指定セル範囲を 同一シート内の指定セル範囲へ 簡単にコピーできます。

参照オブジェクト

この関数を利用するためにはこの関数の実行前に データベースツール > Visual Basic > ツール > 参照設定

【Access VBA】Excel の指定ブックの指定シート内でセル領域をコピーする

の中で

  • Microsoft Office 16.0 Access Database engine
  • Microsoft Excel 16.0 Object Library

にチェックを入れる必要があります。

利用例

Access のマクロ定義例えば “M0090_Call_CopyCellsInSheet” の中で


If CopyCellsInSheet("Desktop\試験.xlsx","Sheet1","$A$1:$C$2","$E$1:$G$2")>0
IF 文の最後

のように CopyCellsInSheet を “Desktop\試験.xlsx“、”Sheet1“、”$A$1:$C$2“、”$E$1:$G$2” といったパラメータを付けて呼び出します。

ここに “Desktop\試験.xlsx“、”Sheet1“、”$A$1:$C$2“は対象 Excel ブックの％USERPROFILE% からの相対パス名、シート名、コピー元セル範囲、”$E$1:$G$2“　は同一シート内のコピー先のセル範囲です。

この “M0090_Call_CopyCellsInSheet” のデザインビューを閉じて、 この　”M0090_Call_CopyCellsInSheet” マクロをダブルクリックすると、 このマクロの中の  CopyCellsInSheet が実行され、指定された “Desktop\試験.xlsx” の中で、”Sheet1“の”$A$1:$C$2“から”$E$1:$G$2” へセルの内容がコピーされます。

引数

属性 意味
対象ブック名の相対パス名 文字型 ブック内で シート間での セル範囲のエクスポートをしたい Excel ブックのファイル名の％USERPROFILE% からの相対パス名を””で囲んだ文字列で関数のパラメータとして指定します。
対象シート名 文字型 セル範囲のコピーをしたいシートの名前を””で囲んだ文字列で関数のパラメータとして指定します。
コピー元セル範囲 文字型 エクスポート元の シートにおけるセル範囲を””で囲んだ文字列で関数のパラメータとして指定します。例えば”$A$1:$C$2”は、シート内のの$A$1:$C$2で表現されるセル領域を指定する意味になります
コピー先セル範囲 文字型

エクスポート元の シートにおけるセル範囲を””で囲んだ文字列で関数のパラメータとして指定します。例えば”$E$1:$G$2”は、シート内のの$E$1:$G$2で表現されるセル領域を指定する意味になります。

コピー元のセル範囲とコピー先のセル範囲は領域の大きさを一致させる必要があります。

戻り値

値 意味
0 正常終了
1 異常終了

 

VBAコード


Function CopyCellsInSheet(input_book_path As String, input_sheet_name As String, input_range_string As String, _
   output_range_string As String) As Integer

  Const C_SUCCESS As Integer = 0
  Const C_FAILURE As Integer = 1

  Dim excelAppObj As Object
  Dim inputExcelWorkBookObj As Object
  Dim inputExcelWorkSheetObj As Object

  Dim fullPath As String
  fullPath = Environ("UserProfile") & "\" & input_book_path

  On Error GoTo exitWithFailure

  Set excelAppObj = CreateObject("Excel.Application")

  excelAppObj.Visible = True
  excelAppObj.UserControl = True

  Set inputExcelWorkBookObj = excelAppObj.Workbooks.Open(fullPath)
  Set inputExcelWorkSheetObj = inputExcelWorkBookObj.Worksheets(input_sheet_name)

  inputExcelWorkSheetObj.Select

  inputExcelWorkSheetObj.Range(output_range_string).Value = inputExcelWorkSheetObj.Range(input_range_string).Value

  inputExcelWorkBookObj.Close savechanges:=True

  excelAppObj.Quit

  Set inputExcelWorkSheetObj = Nothing
  Set inputExcelWorkBookObj = Nothing
  Set excelAppObj = Nothing
  CopyCellsInSheet = C_SUCCESS
Exit Function

exitWithFailure:
  Set inputExcelWorkSheetObj = Nothing
  Set inputExcelWorkBookObj = Nothing
  Set excelAppObj = Nothing
  CopyCellsInSheet = C_FAILURE

End Function

VBAコードの簡単な説明

行番号 説明
1-2 関数名と関数の戻り値の型(Integer:整数型)、入力パラメータ input_book_path 、 input_sheet_name 、 input_range_string 、 output_range_string    の名前、型(String:文字列型)を定義しています。また1行目の最後尾にある ‘_’ はこの行が次の行に継続する(継続行がある)ことを示します。
4-5 このプログラムの中で利用する定数C_SUCCESS(0:成功)、C_FAILURE(1:失敗)を定義しています。ここでは35行目、42行目ででこの関数の戻り値が C_SUCCESS(0:成功)、C_FAILURE(1:失敗)であることが明確にわかるようなコーディングの仕方になります。
7-9 excelAppObj 、inputExcelWorkBookObj 、inputExcelWorkSheetObj  、を オブジェクトとして定義しています。この excelAppObj はMicrosoft Excel のアプリケーションを表すオブジェクト、inputExcelWorkBookObj　は対象の Excel Book を表すオブジェクト 、inputExcelWorkSheetObj  は 入力側 シート名を表すオブジェクトです。
11-12

Environ 関数を使用して 利用者の %USERPROFILE% に相当する絶対パス名 を取得し､そして入力パラメータ input_book_path と併せて対象 Excel ブックファイルのフルパス名 fullPath を求めます。
14 この関数の実行中にエラーが発生したときの例外処理として、exitWithFailure にジャンプします。
16 Excel にアクセスするため “Excel.Application” オブジェクトを生成し、 excelAppObj に設定しています。
18-19  excelAppObj を実行中に非表示にし、ユーザー制御可能な状態に設定しています。
21-22 入力パラメータとして指定された input_book_path、 input_sheet_nameを使って、 inputExcelWorkBookObj  をオープンし、  入力側の inputExcelWorkSheetObj  シートを指定しています。
26 実際にシート inputExcelWorkSheetObj の　output_range_string    で指定された範囲に 　input_range_string で指定された範囲からセルの値を貼り付けているこのVBAコードの中心部分です。
28 この inputExcelWorkBookObj の変更を保存後、この Excel Book をクローズします。
30 この excelAppObj を終了します。
32-34 この関数の中で使用したオブジェクトを(メモリ)開放します。
35-36 この関数の戻り値に C_SUCCESS(0:成功) を設定して関数を終わります。
39-42 この関数の中で使用したオブジェクトを(メモリ)開放します。
42-44 この関数の戻り値に C_FAILURE (1:失敗) を設定して関数を終わります。

まとめ

この記事では「Excel の指定ブックの指定シート内でセル領域をコピーする」ために利用可能な CopyCellsInSheet というユーザー定義関数について紹介しました。 この情報が読者のお役に立てば幸いです。

ライブラリ一覧
ライブラリ一覧