[[20190207164353]]　『worksheetクラスのselectメソッドが失敗　ファイメx（Yokwe）　ページの最後に飛ぶ

『worksheetクラスのselectメソッドが失敗　ファイルを置く場所によってエラーが出ます』（Yokwe）

こんにちは。初めて投稿いたします。

Private Sub Workbook_Open()

    Worksheets("Sheet1").Select
End Sub

これを、ネットワークドライブ（WAN）上において実行するとエラーが出るドライブ（場所）とでないドライブがあります。また、エラーが出るドライブ上でも、このコードをVBAエディタ上で表示して「Sub/ユーザフォームの実行（マクロの実行）」ボタンをクリックすると正常に動作します。

ネットワーク管理者に聞くと、「パスが深いからかもしれない」といった意味のアドバイスをいただきました。また、この広域ネットワークにはファイルを自動で暗号化するソフトが入っていて、これが時々悪さをすることもあります。

とにかく何をやっても特定ドライブに置いたファイルはエラーが出ます。
よろしくお願いいたします。

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

 試しにこんなの作って、各階層で実行してみて、イミディエイトウィンドウに表示された情報の提示いただけますか？
 パスは書き換えてもらっていいですが、階層数はわかるようにしてもらいたいです。
    Private Sub Workbook_Open()
        Dim ws As Worksheet
        Debug.Print ThisWorkbook.FullName
        For Each ws In Worksheets
            On Error Resume Next
            ws.Select
            On erro GoTo 0
            If Err.Number > 0 Then
                Debug.Print "Err:" & ws.Name
            Else
                Debug.Print "OK :" & ws.Name
            End If
            Err.Clear
        Next ws
    End Sub
（稲葉） 2019/02/07(木) 16:59

確かに、深い階層（というか長い文字列になるフルパス）に置いたファイルをWorkbooks.Openしようとすると、失敗します。 Selectする以前に、Open時にエラーになっていませんか？

対策としては、途中のフォルダをドライブマッピングすることで、文字列を短くすると良いでしょう。手動でマッピングしておくか、または、マクロ内でコマンドプロンプトの NET USEコマンドを利用し、一時的にマッピング。処理後に同じくNET USEに/DELETE指定を付けてドライブ削除する、という方法にもできます。

例えば、

 \\ffffff\eeeee_○○\ddd_□□□\ccc_△△△△\bbb_○○○○○○○○\aaa_◎◎◎◎◎\
こんな階層を、

 X:\
にしてしまう訳ですね。
（???） 2019/02/07(木) 17:13

おはようございます！
早速のご返事ありがとうございます。
実行結果です。

\\XX.YYY.Z.AA\school\学校名\A00_学校内共有\Book1.xlsm
OK :Sheet1
OK :Sheet2

共有フォルダにこのファイルを置いておき、複数のユーザが利用できるようにしようと考えております。
すみません、「手動でマッピング」というのは、VBAではどのように指定するのでしょうか。「Runメソッド」を使って、ということでしょうか。勉強不足でご迷惑をおかけいたしますが、よろしくご指導お願いいたします。
（Yokwe） 2019/02/08(金) 08:22

横からですけど
＞「手動でマッピング」というのは、VBAでは～
そうじゃなくて、手作業で、エクスプローラを開いて「ネットワークドライブの割り当て」したらどうですか？と言われているんだとおもいます。

すべてvbaのコードに落とし込みたいなら、
＞マクロ内でコマンドプロンプトの NET USEコマンドを利用し、一時的にマッピング。処理後に同じくNET USEに/DELETE指定を付けてドライブ削除する
↑をShell関数つかって実行でしょうか。

（もこな２） 2019/02/08(金) 08:40

おはようございます！
もこな２　さん、ありがとうございます。早速調べてやってみます。

今後ともご指導よろしくお願いいたします！
（Yokwe） 2019/02/08(金) 10:01

おはようございます。
shell関数、net use を使う、というのは次のようにすることでしょうか。
しかしうまくいきませんでした…

Private Sub Workbook_Open()

    Shell "net use * \\XX.YYY.Z.AA\school\学校名\A00_学校内共有\"
    workbooks("Book1.xlsm").Worksheets("Sheet1").Select
End Sub

よろしくお願いいたします。
（Yokwe） 2019/02/12(火) 09:23

ドライブ名を指定しないと、何のためにマッピングするのか意味なくなりますよ？

また、Shell関数だと、実行完了を待たずに進んでしまうので、マッピングが完了していない状態で続きを処理しようとしてエラーになる事でしょう。なので、以下のように外部オブジェクトを利用し、完了同期しつつ処理します。（テスト環境構築が面倒なため、動作確認はしていません）

 Sub test()
    Dim vw As Variant

    With CreateObject("WScript.Shell")
        vw = .exec("net use x: ""\\XX.YYY.Z.AA\school\学校名\A00_学校内共有\""").StdOut().ReadAll()
        With Workbooks.Open("x:\Book1.xlsm")
            With .Sheets("Sheet1")
                MsgBox .Name, vbInformation, "debug"
            End With
            .Close False
        End With
        vw = .exec("net use x: /delete /yes").StdOut().ReadAll()
    End With
 End Sub
（???） 2019/02/12(火) 12:02

のんびり書いてたら、先にツッコまれてしまったけど、そのまま。

横から口を出したときは↓みたいなイメージだったんですが、非同期だとちょっとまずいですね。

    Sub test1()
        Shell "net use p: ""\\123.456.789.012\school\学校名\ """
        With Workbooks.Open("P:\A00_学校内共有\Book1.xlsm")
            .Worksheets("Sheet1").Select
        End With
        Shell "net use p: /delete"
    End Sub

とりあえずWindowsScriptHostのシェルオブジェクトを使う↓に修正。

    Sub test2()
        With CreateObject("WScript.Shell").Exec("net use p: ""\\123.456.789.012\school\学校名\""")
            Do While .Status = 0
                DoEvents
            Loop
        End With

        With Workbooks.Open("P:\A00_学校内共有\Book1.xlsm")
            .Worksheets("Sheet1").Select
        End With

        Shell "net use p: /delete"

    End Sub

ただ、Workbooks.Openメソッドが失敗するんじゃなくて、Workbook_Openイベントが失敗するってことですよね。
ちょっと質問を勘違いしてました。

確認ですが、Workbook_Openイベントが失敗するときって、そのブックに記述してあるほかのマクロって動かすことできますか？
あんまり自信ないですが、問題のおきるフォルダが、「信頼できる場所」に登録されてなくて、マクロの実行がブロックされているだけとかないでしょうか？

（もこな２） 2019/02/12(火) 12:49

そう、私もエラー原因がフルパスの文字長かどうかの確認が先だと思うのですよね。

なので、コマンドプロンプト上でNET USE…を実行する事でドライブ名に割り当てて、この文字列（私の例だとXドライブ）でOpenしてみて欲しかったのですよ。これで正常にOpenできるようになるならば、今回書いた自動化するコード、のつもりでした。

ブックは開けるけどマクロは一切動いてくれない、とかなら、セキュリティ設定の問題。ブックが開けたり開けなかったり…、ならば、フルパスの長さの可能性、ってとこです。
（???） 2019/02/12(火) 13:23

 よかった、私が勘違いしているのかと思って、フェードアウトしてたのですが、
 今回の問い合わせはWorkbooks.Openが失敗しているわけじゃない、でいいんですよね・・・？

 （Yokwe） 2019/02/08(金) 08:22 で回答いただいてますが、問題の階層で成功したってことでいいのでしょうか？

（稲葉） 2019/02/12(火) 14:00

おはようございます。

皆様、ありがとうございます！
質問と、やってみたことを整理してみます。

・問題点
　Excelのブックを開いた時のイベント

　　Private Sub Workbook_Open()
　　　　Worksheet("Sheet1").Select
　　End Sub

　について、次の場所から開くと問題なく動きます
　　・自分のパソコンのディスクトップ
　　・外部業者が管理するネットワーク上のサーバーにある自分用のフォルダ
　　　　\\nas-XXX\personal\YYYYYY\x2018\（名前）

　ところが、同じ業者(だと思いますが・・）が管理するサーバーの次のフォルダからは
　エラーが出ます（実行時エラー9、インデックスが有効範囲にありません）
　　　\\999.999.999.999\XXXX\○○○\A00_学校内共有

　このエラーの出た場所でブックを開き、VBAの編集画面を開き、「実行」でこのプロシージャを
　実行すると何の問題もなく実行されます。

　やってみたこと
　・WindowsPowerShellのコマンドプロンプトで　
　　　　　net use p: \\999.999.999.999\XXXX\○○○\A00_学校内共有
　　を実行(「コマンドは正常に実行しました」とでます）、
　　
　　「PC」→「m:」にあるExcelファイル（次のマクロを記載したもの）

　　　　Private Sub Workbook_Open()

    　　　Worksheets("p:\book1.xlsm").Worksheets("Sheet1").Select
　　　　End Sub

　　を開いてもエラーが出ます。

　　よろしくお願いいたします。

（Yokwe） 2019/02/14(木) 09:12

 ＞   　　　Worksheets("p:\book1.xlsm").Worksheets("Sheet1").Select
p:\book1.xlsm はブックでありWorksheetsではないのですが、これは書き間違いでしょうか？ 
コピペしたのなら、WorkSheetsオブジェクトの下にWorkSheetsオブジェクトは無いですよ。
そして、ブックを開く命令を使っていませんが、どこでブックを開いているのでしょう？
（ローカルサーバなら正常ということは、どこかでブックを開いているはずなのですが…）

また、違うサーバのbook1.xlsmには、Sheet1 というシートは存在していますか？別名に変えていた、とか無い事を確認してください。

ここまではおそらく大丈夫なのだろうと思えますが、次に、ブックを開いた（どうやって開いているのか判りませんが、マクロ内でWorkBooks.Openしているのだと仮定して…）直後、DoEventsを3回程入れてみてください。ブックを開ききらない内に、ブック内のシートを使おうとしているのかも？
（???） 2019/02/14(木) 11:58

???さんがツッコんでるのと同じことだけど、肝心の「book1.xlsm」をどうやって開いてるのかわからないですね。
（xlsm形式なので、自ブックそのものだったりするのかも？）

いずれの場合も↓のようにしたら解決しませんか？

    Private Sub Workbook_Open()
        Dim WB As Workbook

        On Error Resume Next
        Set WB = Workbooks("book1.xlsm")
        If WB Is Nothing Then Set WB = Workbooks.Add("p:\book1.xlsm")
        On Error GoTo 0

        If WB Is Nothing Then
            MsgBox "book1.xlsm　が見つかりません"
            ThisWorkbook.Close
        Else
            WB.Activate
            WB.Worksheets("Sheet1").Select
        End If

    End Sub

（もこな２） 2019/02/14(木) 12:33

ミスった・・・

 誤　Set WB = Workbooks.Add("p:\book1.xlsm") 
 正　Set WB = Workbooks.open("p:\book1.xlsm")

ですね。

（もこな２） 2019/02/14(木) 12:38

こんにちは
見当違いなら無視してください。
Select やめて
Application.goto にしてみては。

（Heron ） 2019/02/15(金) 00:04

 なんかバカらしいことしているなと・・・。
 結局、どこでエラーになって解らないんですよね。
 なんでSelectが、どうたらとしか考えないんですかね？

 単にメッセージを表示させるとかしてみりゃいいじゃん。
 Selectだけにこだわる気がしれない。

 また、XXXX\○○○とか使かって書いているけど、実際は何文字名ですかね？
 で、どうやって問題のブックを開いているのかもしぶとく書いてないし。
 とまあ、ただのぼやきなんで回答でもないです。
（BJ） 2019/02/15(金) 00:59

 BJさんの言う通りですね
 Selectにこだわる必要がなさそう

 んで、私が最初に提示したFor Eachですべてのシートを選択する場合
 Select成功してるみたいですが、問題のサーバーなのか教えてもらえませんか？

 そして、成功しているなら、if ws.name = "Sheet1" then exit for
 で、処理終わらせたらダメですか？
（稲葉） 2019/02/15(金) 05:46

こんにちは

ブックを開いたら常にSheet1を表示させたいというのが
やりたいことなのですよね？　

ちがったら、以下無視してください。
　

 Private Sub Workbook_Open() 
　Worksheet("Sheet1").Select 
 End Sub

ここで、エラーメッセージが

実行時エラー9、インデックスが有効範囲にありません

なのであれば、Sheet1 が見つからないというエラーです。
このとき、最初に疑うのは、親オブジェクトの指定です。

なので、

 Private Sub Workbook_Open()
   ThisWorkbook.Worksheets("Sheet1").Select
 End Sub

をまず試して欲しいです。

（でれすけ） 2019/02/15(金) 09:43

コメント返信：

[ 一覧(最新更新順) ]

[[20190207164353]] 『worksheetクラスのselectメソッドが失敗 ファイメx（Yokwe） ページの最後に飛ぶ

[[20190207164353]]　『worksheetクラスのselectメソッドが失敗　ファイメx（Yokwe）　ページの最後に飛ぶ