[[20220311162341]]　『セルのリンク先のシートの有無を判定したい』（no_name）　ページの最後に飛ぶ

『セルのリンク先のシートの有無を判定したい』（no_name）

下記のようなプログラムで任意のセルへ入力しリンク先の値を表示する場合

Range("A1") = "='" & Path & "\[" & fileName & "]" & sheetName & "'!P11"

パス・ファイル名・シート名が正しく，存在していれば値が表示されるが，
シートがない場合，指定したファイル内のシートを選択する組み込みダイアログボックス「シートの選択」が表示され，プログラムが停止する．

そのため，リンク先の値を入力する前にリンク先のシートが存在するするか判定する処理を実現したいと思っています．方法はいくつかあると思いますが，ご教授お願い致します。

出来ればリンク先のブックを開かずに処理したいと思います．ブックを開かずにシート名を取得する方法（ExecuteExcel4Macro等）はあるみたいですが，あまり推奨されていないのでできれば使用したくないです．

私の考えでは組み込みダイアログボックス「シートの選択」が表示されたことを判定するか，そのダイアログボックスをvbaからキャンセルできれば考えている処理ができるのではないかと考えているのですが，取得方法が分からず手が止まっています．

上記以外の方法でも良い方法があれば教えてくださると大変助かります．

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

＞組み込みダイアログボックス「シートの選択」が表示されたことを判定するか，
＞そのダイアログボックスをvbaからキャンセルできれば
どちらも難しい印象です。

状況が良く見えていないので、追加説明いただいきたいですが、
リンク先は可変なんですか？
変数sheetNameには、何をどういう根拠で設定されているのですか？
普通は前もって確認するのではないですか？

（γ） 2022/03/11(金) 19:41

＞ブックを開かずに処理したいと思います
＞ブックを開かずにシート名を取得する方法（ExecuteExcel4Macro等）はあるみたいですが~使用したくない
断言しませんが、開かずに【シート名を取得】する方法はないんじゃないですかね。

むしろ、開きたくない理由はなんですか？
どうせマクロが勝手に作業するので、手間にはならないとおもいますが・・・・

開いてしまえばRange型変数へのセットが成功するかどうかで判定できますし、Formula（Value）プロパティへの書き込みもAddressプロパティのExternalをTrueにして取得すれば簡単ですよね。
Sub 研究用5()

    Dim MyWB As Workbook
    Dim MyRNG As Range
    Dim dstSH As Worksheet

    Set dstSH = ActiveSheet
    On Error Resume Next
    Set MyWB = Workbooks.Open("C:\WORK\hoge.xlsx")
    Set MyRNG = MyWB.Worksheets("hoge").Range("P1")
    On Error GoTo 0

    Stop 'ブレークポイントの代わり

    If MyWB Is Nothing Then
        dstSH.Range("A1").Formula = "該当するブックが存在しません"
    Else
        If MyRNG Is Nothing Then
            dstSH.Range("A1").Formula = "該当するシートが存在しません"
            MyWB.Close False
        Else
            dstSH.Range("A1").Formula = "=" & MyRNG.Address(External:=True)
            MyRNG.Parent.Parent.Close False
        End If
    End If

End Sub

（もこな２） 2022/03/11(金) 19:53

 ファイルの中の情報を取り出すには、何らかの方法で覗き見しないといけないのです

 一般的にいう「開く」操作（手動/マクロ=WorkBooks.Open）をしないで
 シート名のリストを取得するには、
　
  ・PowerQuery使って、シート名のリストをシート上に書き出す
　・ブック(xlsx)をzipに拡張子変更して、zipファイルの中のxmlフィアルを読み込む
 とか言う方法があります。

 コード作成のコストと「開く」ことを忌避する理由の兼ね合いですが・・・・

 ファイルサイズがすごく大きくて、開く時間を短縮したいという理由なら、
 PowerQueryはありかもしれません（未検証です）
　
（´・ω・`） 2022/03/11(金) 20:50

 あ、おすすめは、「開いてる動作を見せないで、裏でこっそり開く」です。
（´・ω・`） 2022/03/11(金) 20:54

 検索してみました
 check for existing sheetname without opening workbook
https://www.mrexcel.com/board/threads/check-for-existing-sheetname-without-opening-workbook.311538/
（´・ω・`） 2022/03/12(土) 01:59

 私のお勉強の結果メモ ブックを開かないように見せてでシート名のリストを取得

 Sub test()

  Debug.Print SheetExist("D:\test.xlsx", "Sheet1")  ' 有無の判定

  For Each sh In GetSheetsList("D:\test.xlsx")      ' 全シート名書き出し
     Debug.Print sh
  Next

 End Sub

 Function SheetExist(ByVal xlsFilePath As String, ByVal shName As String) As Boolean
  On Error Resume Next
    SheetExist = WorksheetFunction.Match(shName, GetSheetsList(xlsFilePath), 0)
 End Function

 Function GetSheetsList(ByVal xlsFilePath As String) As Variant()
   '参照設定 ADODB : Microsoft ActiveX Data Objects X.X Library
   '参照設定 ADOX  : Microsoft ADO Ext. X.X for DDL and Security
    Dim cnn As ADODB.Connection, ctlg As ADOX.Catalog, tbl As ADOX.Table
   ' Dim cnn As Object, ctlg As Object, tbl As Object
    Dim shName As String, ret() As Variant, i As Long

    ret = Array()
    If Dir(xlsFilePath) = "" Then GetSheetsList = ret: Exit Function

    Set cnn = CreateObject("ADODB.Connection")
    With cnn.Properties
        .Item("Data Source") = "Excel Files"
        .Item("Initial Catalog") = xlsFilePath
    End With
    cnn.Open
    Set ctlg = CreateObject("ADOX.Catalog")
    Set ctlg.ActiveConnection = cnn
    If ctlg.Tables.Count > 0 Then
       ReDim ret(1 To ctlg.Tables.Count)
       i = 1
       For Each tbl In ctlg.Tables
           shName = tbl.Name
           ret(i) = IIf(Right(shName, 1) = "$", Left(shName, Len(shName) - 1), shName)
           i = i + 1
       Next
    End If
    GetSheetsList = ret
    cnn.Close
 End Function
（´・ω・`） 2022/03/12(土) 06:59

Yさんもこな２さん ´・ω・`さん

ご助言ありがとうございます。

ブックを開かずシート名を取得することは難しそうなので
おとなしくブック開いて処理を作成しようと思います．

´・ω・`さん
参照設定はオフラインの端末でも有効ですか？
作成しようとしているエクセルファイルはオフライン端末上で使用する予定です．
（no_name） 2022/03/15(火) 14:08

（´・ω・`）さんではないですが、気になったので。
＞参照設定はオフラインの端末でも有効ですか？

上手く説明できませんが、参照するのはそのコンピューターにある資源のはずです。
（イメージとしてはExcelから別のアプリケーションを使うための手続きです）
パソコン上にある資源を利用するのでそのパソコンがネットワークにつながっているかどうかは関係なかったと思います。

なお、多少のちらつきは許容できるというなら

 ブックを開いた直後に最小化してしまう
 マクロ実行中は画面更新を抑制してユーザーに見せない

などのアプローチもあるとおもいます。

（もこな２） 2022/03/15(火) 14:20

 >参照設定はオフラインの端末でも有効ですか？
 もこな２さんの解説のとおりです。

 ローカルのライブラリへの参照を設定するものです。
 VBEのメニューの[ツール]→[参照設定]で、必要なライブラリにチェクします。

 参照設定は絶対必用なものでもありません。
 GetSheetsList関数の中の宣言文で、
    Dim cnn As ADODB.Connection, ctlg As ADOX.Catalog, tbl As ADOX.Table　' この行削除
   ' Dim cnn As Object, ctlg As Object, tbl As Object                     ' この行の最初の ' 削除して有効化
 すれば、参照設定しなくても動きます。
（´・ω・`） 2022/03/15(火) 14:34

もこな２さん　´・ω・`さん

補足ありがとうございます。
参照設定を調べること「ライブラリをインストール」という文言が出てきたので
オンライン上でないと出来ないのかと思い質問させいただきました．
（no_name） 2022/03/16(水) 09:43

参照設定について↓あたりを読むと理解が深まるかもしれません。
https://www.sejuku.net/blog/76520
http://officetanaka.net/excel/vba/tips/tips100.htm

なお、（´・ω・`）さんのコメントの通り、コスト（勉強する手間も含む）とやりたいことの重要度が見合っているかも検討したほうがいいんじゃないかと思いました。

 繰り返しになりますが、画面の抑制程度でよければExcelVBAだけで対応できます。

（もこな２） 2022/03/16(水) 10:36

 >シートがない場合，指定したファイル内のシートを選択する
 >組み込みダイアログボックス「シートの選択」が表示され，
 >プログラムが停止する．

参照する数式を入力してみて、数式が不正（指定したブックまたはシートが存在しない）な場合、
プログラムが途中で停止することを回避できたらいいのでは？

（まっつわん） 2022/03/16(水) 12:17

コメント返信：

[ 一覧(最新更新順) ]

[[20220311162341]] 『セルのリンク先のシートの有無を判定したい』（no_name） ページの最後に飛ぶ

[[20220311162341]]　『セルのリンク先のシートの有無を判定したい』（no_name）　ページの最後に飛ぶ