EXCEL VBAコミュのご享受下さい。フォルダを指定し、その中から選んだブックを開きたい

フォルダを指定し、その中から選んだブックを開きたいのですが、
下記コードでは不具合が２点あります。

（下記コードも先日、教えていただいたものです。超初心者のため、質問がおかしいこともあるかもしれませんが、よろしくお願い致します。）

◆不具合
　　?SendKeysメソッドが不安定で、（エクセルを立ち上げたまま少し時間が経過すると？）効かなくなる。
?選択したブックにパスワードが設定されている場合、
　ダイアログが出るが、その時点でキャンセルを押すと
　当然ながらエラーになってしまう。

◆やりたいこと
　?のSendKeysメソッドを使用せず、
　　Bookで始まるファイル名を取得したい。
　?パスワードのダイアログのＯＫorキャンセルの戻り値？を取得したい。
　（haspasswordを使用するのかと思いやってみましたがうまくいかず・・・)
　?下記のコードとは関係なく、コンボボックスにしてしまって、　　そのリストに、指定フォルダ内の選択ブック名（Bookではじまるブック名全て）
　　を流し込み、リストから選択して開くようにしたい。

何卒よろしくお願い致します。

Sub ブックを開く()

Dim fileToOpen As String

ChDrive "D"
ChDir "D:\test\Atest\Btest"

SendKeys "Book*{Enter}"
fileToOpen = Application.GetOpenFilename("Excel ファイル (*.xls), *.xls")

If fileToOpen = "False" Then
Exit Sub
MsgBox "キャンセルされました。"
Else
Workbooks.Open fileToOpen

End If
End Sub

コメント(16)

最初
全て
最新の40件

[1] mixiユーザー 02月26日 21:28

>?選択したブックにパスワードが設定されている場合、
>　ダイアログが出るが、その時点でキャンセルを押すと
>　当然ながらエラーになってしまう。
は、
　　On Error Resume Next
　　Workbooks.Open fileToOpen
　　On Error GoTo 0
で回避すればいいと思います。

パスワードの問題以外は
>　?下記のコードとは関係なく、コンボボックスにしてしまって、
>　　そのリストに、指定フォルダ内の選択ブック名（Bookではじまるブック名全て）
>　　を流し込み、リストから選択して開くようにしたい。
を解決すれば済む話のような気がします。
コンボボックスに入れるには、
　　Dim wkDir As String
　　Dim wkFileName As String
　　wkDir = "D:\test\Atest\Btest\" 'フォルダの指定
　　Me.ComboBox1.Clear
　　wkFileName = Dir(wkDir & "Book*.xls")
　　Do Until wkFileName = ""
　　　　Me.ComboBox1.AddItem wkFileName
　　　　wkFileName = Dir()
　　Loop
といった感じでできます。

開くときには、
　　fileToOpen = "D:\test\Atest\Btest\" & Me.ComboBox1.Text
　　If Not Dir(fileToOpen) = "" Then
　　　　On Error Resume Next
　　　　Workbooks.Open fileToOpen
　　　　On Error GoTo 0
　　End If
とすればいいです。多分。。。

[2] mixiユーザー 02月26日 22:33

出先なのでちょっと解決策を提示できないのですが、
ビリーさんのコンボボックスにファイル名を流し込むのは
おもしろいですね。いただきます（笑）

素浪人は普段は何も考えず、指定したホルダの中のファイルを全部取り込んでしまいますので、コンボボックスで動的にリスト化して選択できるようにするのは素敵ですね。

[3] mixiユーザー 02月26日 22:50

いいですねぇ

できることをやるのではなく、やりたいことを
やろうと目指す…

VBAって人生なんですね…。

[4] mixiユーザー 02月26日 23:40

ついでなので、もうちょっと書いておきます。
"D:\"に"BookX.xls"があり、このブックには"123"という読み取りパスワードがかかっているとします。
パスワードを手入力させるなら、Sample1のコードでできます。
間違ったパスワードを入れたり、キャンセルをしたりするとエラーメッセージが出るようにしてあります。
パスワードを手入力させないなら、Sample2のコードになります。

Sub Sample1()
　　Dim wkFName As String
　　wkFName = "D:\BookX.xls"
　　On Error Resume Next
　　Workbooks.Open wkFName
　　If Not Err.Number = 0 Then
　　　　MsgBox Err.Number & vbCrLf & Err.Description, vbCritical
　　　　Exit Sub
　　End If
　　On Error GoTo 0
　　MsgBox "ブックを開きました。", vbInformation
End Sub

Sub Sample2()
　　Dim wkFName As String
　　Dim wkPassword As String
　　wkPassword = "123"
　　wkFName = "D:\BookX.xls"
　　On Error Resume Next
　　Workbooks.Open wkFName, , , , wkPassword
　　If Not Err.Number = 0 Then
　　　　MsgBox Err.Number & vbCrLf & Err.Description, vbCritical
　　　　Exit Sub
　　End If
　　On Error GoTo 0
　　MsgBox "ブックを開きました。", vbInformation
End Sub

[5] mixiユーザー 02月26日 23:43

ビリー様、皆様
早速のレスありがとうございます。

ビリー様
コードの提示ありがとうございます。助かります！

>?選択したブックにパスワードが設定されている場合、
>　ダイアログが出るが、その時点でキャンセルを押すと
>　当然ながらエラーになってしまう。
は、
　　On Error Resume Next 　
　　Workbooks.Open fileToOpen
　　On Error GoTo 0

　　On Error Resume Next 　　
　　で回避できるのですね！
　　知ってはいたのですが、どういうときに
　　使ったらいいかがわからず、全く浮かんできませんでした。。。勉強になりました。　

それから、コンボボックスは用したことがあまり（というかほとんど）ないので途方にくれていました。
明晩にでも勉強させていただきます。
またお世話になるかと思いますが、よろしくお願い致します。

[6] mixiユーザー 02月27日 19:47

ビリー様
昨晩はありがとうございました。
早速、やってみましたところ、希望通り動かすことができました！
（フォームを表示→コンボボックスでブックを選択→開くボタンで開く。）
感動です！でもまだわからないところがあります。
申し訳ないのですが下記３点教えていただけますでしょうか。

?◆のところの記述の意味
?■パスワードダイアログが出た時、キャンセルボタンを押すと
　メッセージボックス「キャンセルされました」と表示させたい。
?●キャンセルボタンを押すと、フォームを消すようにしたい。
お手数ですが、よろしくお願い致します。

Private Sub UserForm_initialize()
Dim wkDir As String
Dim wkFileName As String
Dim fileToOpen As String

'コンボボックスにファイル名を入れる
wkDir = "D:\test\Atest\Btest\" 'フォルダの指定
Me.Hコンボ1.Clear '初期化
wkFileName = Dir(wkDir & "Book*.xls")

Do Until wkFileName = "" '空欄になるまで繰り返す
　　　　Me.Hコンボ1.AddItem wkFileName
　　　　wkFileName = Dir()　←◆　　　　　
Loop
End Sub

Private Sub H開く1_Click()
Dim wkDir As String
Dim wkFileName As String
Dim fileToOpen As String

'開く
fileToOpen = "D:\test\Atest\Btest\" & Me.Hコンボ1.Text
If Not Dir(fileToOpen) = "" Then
　　　　On Error Resume Next
　　　　Workbooks.Open fileToOpen
　　　　On Error GoTo 0
　　　　msgbox "キャンセルされました。"　←■　開いた時にも当然ながら表示。
End If

'**************************************************************
'Dir関数･･･指定したパターンやファイル属性と一致するファイルまたは
'フォルダの名前を表す文字列型 (String) の値を返す。
'ドライブのボリュームラベルも取得できる｡

'AddItemメソッド･･･単一行のテキストの取得と表示が設定されているリストボックス
'またはコンボボックスの場合は､リストに項目を追加する｡
'複数行のテキストの取得と表示が設定されているリストボックス
'またはコンボボックスの場合は､一覧に行を追加する｡
End Sub

Private Sub Hキャンセル1_Click()
　　　　UserForm1.Hide ←●消えますが、これで合っているのでしょうか？
End Sub

[7] mixiユーザー 02月27日 23:10

>?◆のところの記述の意味
最初の
　　wkFileName = Dir(wkDir & "Book*.xls")
で、１つ目のファイルを取ってきます。
そして、２つ目以降のファイルを取得するために、
　　　　wkFileName = Dir()　←◆　　　　　
を入れています。
それぞれの直後に
　　MSGBOX wkFileName
と入れるとなんとなく分かるかと思います。

>?■パスワードダイアログが出た時、キャンセルボタンを押すと
>　メッセージボックス「キャンセルされました」と表示させたい。

>?●キャンセルボタンを押すと、フォームを消すようにしたい。

2005年02月26日 23:40のSample1のコードが参考になりませんか？
こう↓してみてください。
>'開く
>fileToOpen = "D:\test\Atest\Btest\" & Me.Hコンボ1.Text
>If Not Dir(fileToOpen) = "" Then
>　　On Error Resume Next
>　　Workbooks.Open fileToOpen
　　 If Not Err.Number = 0 Then
　　　　 MsgBox Err.Number & vbCrLf & Err.Description, vbCritical
　　　　 UNLOAD ME '←ユーザーフォームを閉じる
　　　　 Exit Sub
　　 End If
>　　On Error GoTo 0
>End If

>Private Sub Hキャンセル1_Click()
>　　UserForm1.Hide ←●消えますが、これで合っているのでしょうか？
>End Sub
個人的には、
UNLOAD ME
の方が好みです。余分なものがメモリに残らないので。

[8] mixiユーザー 02月28日 22:05

ビリー様

レスありがとうございます。
2005年02月26日 23:40のSample1を見落としていました。。。
すみませんでした。

＞wkFileName = Dir()
　msgboxで見たら、１つ１つ入っていました！
　Dir() ←このカッコの中に入っていくんですね？
　（なんだか不思議なかんじです。）
　大変大変勉強になりました。ほんとにありがとうございました。

fileToOpen = "D:\test\Atest\Btest\" & Me.Hコンボ1.Text
If Not Dir(fileToOpen) = "" Then
On Error Resume Next
Workbooks.Open fileToOpen

If Not Err.Number = 0 Then
MsgBox "キャンセルされました。"
Unload Me 'ユーザーフォームを閉じる
Exit Sub
End If
On Error GoTo 0
End If

[9] mixiユーザー 03月01日 01:49

> MsgBox "キャンセルされました。"
にすると、間違ったパスワードを入れたときも、キャンセル
されましたと出てしまうんですけど。。。

[10] mixiユーザー 03月01日 17:59

ビリー様

> MsgBox "キャンセルされました。"
にすると、間違ったパスワードを入れたときも、キャンセル
されましたと出てしまうんですけど。。。

そうなんです。。
間違ったパスワードを入れたときは、
エラーメッセージもしくはmsgbox"間違ったパスワードが入力されています・・・"と出て、
パスワードダイアログのキャンセルを押したときは、
エラーメッセージではなく、msgboxで"キャンセルされました。"と、出したいのですが、方法がわかりません。
少し考えてみたいと思います。
また質問（かなりとんちんかんな）させていただくと思いますが、
どうぞよろしくお願い致します。

[11] mixiユーザー 03月01日 22:24

う～ん。
ぱっといい方法が思い浮かばないので、むちゃくちゃ泥縄ですが、、、
ブックのオープンに失敗する可能性があるのが、パスワード関係くらいしかないのであれば、

　　If Not Err.Number = 0 Then
　　　　MsgBox "キャンセルされました。"
　　　　Unload Me 'ユーザーフォームを閉じる
　　　　Exit Sub
　　End If

の部分を

　　If Not Err.Number = 0 Then
　　　　If Left(Err.Description, 19) = "'Open' メソッドは失敗しました:" Then
　　　　　　MsgBox "キャンセルしました。", vbCritical
　　　　Else
　　　　　　MsgBox Err.Description, vbCritical
　　　　End If
　　　　Unload Me 'ユーザーフォームを閉じる
　　　　Exit Sub
　　End If

にしてもいいかもしれません。

[12] mixiユーザー 03月03日 21:08

ビリー様

＞If Left(Err.Description, 19) = "'Open' メソッドは失敗しました:" Then

レスありがとうございます！
こんなLeftの使い方ができるんですね！
目からウロコです。

前回のでいけそうなんですが、
（パスワードを間違えたら、エクセルが勝手に「間違ったパスワードが・・・とダイアログを出してくれました）
ビリーさんの回答を拝見できてよかったです！
ほんとに長々とお付き合いくださってありがとうございました。
また、質問させていただくことがあると思いますが、
どうぞよろしくお願い致します。

[13] mixiユーザー 03月06日 08:29

補足までに。
On Error ステートメントでトラップしたエラーは上記のコーディング例で出てる通り、Errオブジェクトでエラー番号が拾えます。
On Error　ステートメントを仕込まない状態で処理を実行し、エラーを発生させると、どのエラー番号でエラーが起こっているのかわかりますので、そのエラー番号にしぼって分岐をすると特定のエラーが発生した時の処理を追加する事ができますよん♪

上記のLeftの部分に関しては文字列検索関数のInstrを使うのも手ですな。
こっちやと結構動的に取れますんで。

[14] mixiユーザー 03月07日 20:11

[羽]e-bow様

＞そのエラー番号にしぼって分岐をすると特定のエラーが発生した時の処理を追加する事ができますよん♪
＞上記のLeftの部分に関しては文字列検索関数のInstrを使うのも手
そうですね！ご親切にありがとうございます。
その時がきて、ここで使う！ってことに気がつければいいのですけど。。笑
On Error ステートメントの記述のみ知ってるだけで、使えず～の状態から早く抜け出したいです。。。（ホント！）
また、質問させていただくことがあると思いますが、
どうぞよろしくお願いいたします　ｍ(＿＿)ｍ

[15] mixiユーザー 03月07日 20:28

上記のコーディング例でのココの部分の分岐条件

If Not Err.Number = 0 Then
'なんぞの処理
End If

ご覧の通り、Err.Numberが0でない時に処理します。
On Err はエラーが発生するとGotoの後に書いたラベル等へジャンプします。
この時、Err.Numberを使用するとで何が原因のエラーかを判別するエラーコードが帰ってきます。
たとえば、エラー未発生なら0、オーバーフローやと6ってな具合ですな。
そんな事も中々ないと思いますけど、エラーの中でもオーバーフローが起こった時に何か特別な事をやらせたい場合

エラーラベル:
if Err.Number=6 then
'オーバーフロー用の何ぞの処理
End If

だと、いろんなエラーの中でもオーバーフローの時のみ通るロジックを組める事がお分かりいただけると思います。
単純に思いつく使い道としてはエラーメッセージのカスタマイズなんかでしょうかね？
んなトコでまず。

[16] mixiユーザー 03月10日 22:23

[羽]e-bow様

なるほど！
すごくわかりやすい説明、ありがとうございます！
このスレ～全部メモ帳に落として、保存版にしておきました！
分岐条件がなかなか浮かばないことが多く（っていうより経験不足）、大変助かります。
また何かありましたらよろしくお願い致します。

ログインすると、みんなのコメントがもっと見れるよ