Access

【Access VBA】ドラッグアンドドロップで Excel の非表示シート全部をまとめて表示設定する

【Access VBA】ドラッグアンドドロップで Excel の非表示シート全部をまとめて表示設定する

ドラッグアンドドロップで Excel の非表示シート全部をまとめて表示設定する

この記事ではバッチファイルと組み合わせることで Excel の非表示シート全部をまとめて表示設定する SetExcelsAllSheetsVisibleというユーザー定義関数について紹介します。 この情報が読者のお役に立てば幸いです。

解決できること

 この SetExcelsAllSheetsVisible を実装した Access ツールをバッチファイルと組み合わせることで Excel ブックをドラッグアンドドロップするだけでこの  Excel の非表示シート全部をまとめて表示設定するツールを手に入れることができます。

読んでほしい方

Microsoft 365 Apps for business  以外の Excel をお使いの方で、以下のように Excel の非表示中のシート全部をまとめて表示設定する方法を探している方。

このExcel の非表示シートをまとめて表示設定する機能は、Microsoft 365 Apps for business バージョン 2204(15128.20178) で
シート名 右クリック > 再表示(U..)
で表示される｢1つ以上のシートを再表示(U)｣というタイトル付きの「 再表示」 ダイアログで  シフトキー または CTLキーを押しながらマウスで再表示したいシートを全てクリック選択後 OK ボタンをクリックすることで一度に再表示できることは確認済です。 

何故か business バージョンで使える機能が enterprise バージョンで使えないってことが有るんですね。

【Access VBA】ドラッグアンドドロップで Excel の非表示シート全部をまとめて表示設定する

SetExcelsAllSheetsVisible 関数 の紹介

説明

この SetExcelsAllSheetsVisible を実装した Access ツールを例えば

  • %USERPROFILE%\Tools\accdb\設定_Excel_全Sheets表示.accdb

として、このツールをバッチファイルから起動できるようにすると

Excel ブックをドラッグしてこのバッチファイル、あるいはバットファイルへのショートカットにドロップするだけでこの  Excel ブック の非表示シート全部をまとめて表示設定するツールとすることができます。

参照オブジェクト

この関数を利用するために必要な参照オブジェクトはありません。

利用例

この関数を実装する Access ツール内の “autoexec” マクロ中で次のようにこの関数 SetExcelsAllSheetsVisible を呼び出します。


If SetExcelsAllSheetsVisible()>0 Then
If 文の最後

引数

この関数を利用するための引数はありません。

ただし先ほどの Access ツール起動するバッチファイルを

  • %USERPROFILE%\Tools\bat\設定_Excel_全Sheets表示.bat

とすると、このバッチファイル中で先ほどの Access ツールを以下のようにパラメータ ｢ /cmd %1｣  付きで呼び出さなければいけません。

この ｢ /cmd %1｣ は 起動される Access ツールに %1 で指定したパラメータを引数として渡すことを意味しており、その値 %1 は後で紹介する VBA の中で｢Command｣関数で参照することができます｡

また通常こうしたバッチファイルへのパラメータ渡しは一般的に Windows のコマンドラインやバッチファイルの中で明示的にパラメータをキー入力することでこれを行いますが、もう一つの方法としてドラッグした ファイルをバッチファイル、またはPCのデスクトップに配置したこのバッチファイルへのショートカットへドロップするとこのドラッグした ファイルの絶対パス名を %1 に引き渡すことができます。


@echo off

%USERPROFILE%\Tools\accdb\設定_Excel_全Sheets表示.accdb /cmd %1

こうした %USERPROFILE%､ %1 といったバッチファイルに関連した基礎的概念やフォルダ構成については必要に応じて

を参照してください｡

戻り値

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

VBAコード


Function SetExcelsAllSheetsVisible() As Integer

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

  DoCmd.RunCommand acCmdAppMinimize

  On Error GoTo exitWithFailure
 
  Dim excelAppObj As Object
  Set excelAppObj = CreateObject("Excel.Application")
  excelAppObj.Visible = False

  Dim inputParameter  As String
  inputParameter = Command
 
  ' Workbook を開く
  Dim excelWorkBookObj  As Object
  Dim excelWorkSheetObj  As Object
  
  Set excelWorkBookObj = excelAppObj.Workbooks.Open(inputParameter)
 
  For Each excelWorkSheetObj In excelWorkBookObj.Worksheets
     excelWorkSheetObj.Visible = True
  Next

  excelWorkBookObj.Close savechanges:=True
  
  excelAppObj.Quit
  Set excelWorkSheetObj = Nothing
  Set excelWorkBookObj = Nothing
  Set excelAppObj = Nothing

  SetExcelsAllSheetsVisible = C_SUCCESS
Exit Function

exitWithFailure:

  excelAppObj.Quit
  Set excelWorkSheetObj = Nothing
  Set excelWorkBookObj = Nothing
  Set excelAppObj = Nothing

  SetExcelsAllSheetsVisible = C_FAILURE
End Function

VBAコードの簡単な説明

行番号 説明
1 関数名 SetExcelsAllSheetsVisible と関数の戻り値の型(Integer:整数型)を定義しています。
3-4 このプログラムの中で利用する定数C_SUCCESS(0:成功)、C_FAILURE(1:失敗)を定義しています。ここでは34行目、44行目でこの関数の戻り値が C_SUCCESS(0:成功)、C_FAILURE(1:失敗)であることが明確にわかるようなコーディングの仕方になります。
6 この関数を実行する Access をアイコン化(最小化)しています。
8 この関数の実行中にエラーが発生したときの例外処理として、exitWithFailure にジャンプします。
10-12 Excel を excelAppObj  として表示モードで起動します。
14-15 バッチファイル中でこの Access 起動時に指定した第一パラメータ %1 つまり、このバッチファイルにドラッグした Excel ブックのフルパス名を inputParameter という文字列に設定します。 
18-21 inputParameter でフルパス指定された Excel ブックを excelWorkBookObj としてオープンし、そのシート名を excelWorkSheetObj として宣言します。
23-27 この excelWorkBookObj  内の全てのシート名を excelWorkSheetObj  に順に取り出し、そのシートを表示設定し、これを保存します。
29-32 excelAppObj を終了し、excelWorkBookObj  、excelWorkSheetObj  、excelAppObj のメモリ領域を開放します｡
34-35 この関数の戻り値に C_SUCCESS(0:成功) を設定して関数を終わります。
39-42 excelAppObj を終了し、excelWorkBookObj  、excelWorkSheetObj  、excelAppObj のメモリ領域を開放します｡
44-45 この関数の戻り値に C_FAILURE (1:失敗) を設定して関数を終わります。

まとめ

この記事ではバッチファイルと組み合わせることで Excel の非表示シート全部をまとめて表示設定する SetExcelsAllSheetsVisibleというユーザー定義関数について紹介しました。 この情報が読者のお役に立てば幸いです。

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