[[20070811230129]]　『郵便番号ファイルを自動で開きたい』（ノム）　ページの最後に飛ぶ

『郵便番号ファイルを自動で開きたい』（ノム）

郵便番号から住所を自動で表示させようとして悩んでいます！

郵便番号のデータサイズが大きいため別ファイル（csv）としています。

「〒」ボタンをクリックすると、

\x{fffd}@ファイルが開いていないときはメッセージを出して呼び出し（ファイルオープン）させる。

\x{fffd}Aファイルが開いているときは何も表示させない。

このようなマクロコードを教えて頂きたいのですが？

どうぞ宜しくお願いします。

（ノム）

[エクセルのバージョン]
Excel2003

[OSのバージョン]
WindowsXP

 この手のものは、ネット上にたくさんあると思います。
 参考になれば・・・
　　　(SHIOJII）

 http://www.google.co.jp/search?sourceid=navclient&aq=t&hl=ja&ie=UTF-8&rlz=1T4GGLD_ja___JP212&q=VBA+%e9%83%b5%e4%be%bf%e7%95%aa%e5%8f%b7%e3%80%80%e4%bd%8f%e6%89%80

(SHIOJII）早速回答ありがとうございます。

郵便番号を取り込む方法は下記のとおりVLOOKUP関数で別ファイル（〒.csv）から取り込むようにしています。

=IF($I$31="","",IF(ISERROR(VLOOKUP($I$31, 〒.csv!$A$2:$B$14212,2, FALSE)),"",VLOOKUP($I$31,〒.csv!$A$2:$B$14212,2, FALSE)))

今回の質問は、別ファイルが開いているときは何もせずに、開いていないときは「〒番号ファイルが開いていません！　開きますか　Ｙ／Ｎ　」

というメッセージを表示させて「Yes」をクリックすると（〒.csv）ファイルを開きたいのです。

これを何とかしたいのですが・・・・・

（ノム）

 こんなので上手く行きますか？
 「ファイルを開く動作」の所へは
 マクロの記録を取った物を差し込んでください。
　
Sub 〒CSVを開く()
    Dim kbn As Workbook
    Dim gbn As String, hbn As String
        Application.ScreenUpdating = False
        gbn = ActiveWorkbook.Name
        hbn = "〒.csv"
　 
'★１．「〒.csv」と、現在開いているブック名を一つずつ比べる。
    For Each kbn In Workbooks
        If kbn.Name = hbn Then  '同じだったら
            Exit Sub            'マクロ終了
        End If
    Next
　 
'★２．メッセージボックスを出して　ファイルを開くか確認
'　　　（途中で終了していない ＝ 〒ファイルは開いていない　と言う事なので。）
    If MsgBox("〒番号ファイルが開いていません！" & Chr(10) & "開きますか？" _
                    , vbYesNo + vbExclamation, "開くの？") = vbNo Then
        Exit Sub                'Noが選ばれたらマクロ終了
    Else
        On Error GoTo err       'エラーが出たら、errの行に移動
　 
'★３．ファイルを開く
            '//////////////////////////////
            '//                          //
            '//　　ファイルを開く動作　　//
            '//                          //
            '//////////////////////////////
    End If
　 
'★４．最初に開いていたブックをアクティブにする
    Windows(gbn).Activate
　 
'★５．エラーの時の処理
err:
    If err Then
        MsgBox "ファイルが開けませんでした。"
    End If
        Application.ScreenUpdating = True
End Sub

 (HANA)

 (HANA)さん　ありがとうございました。

私のイメージの通りでした。

日曜日の貴重な時間で回答頂き大変ありがとうございました。

感謝！

（ノム）

 主要部分の変更ではないのですが
「Application.ScreenUpdating」の２行を追加しました。

 [〒]ボタンを押すことで、確認をして開く　と言う作業は
 　○通常は開いておきたくない
 のですかね？

 もしも、途中でハタと気づいて「開きたい」なんて事が無い場合で
 　○気をつけて開く様にしてるんだけど、忘れるんだよね。
 なんて時は「auto_open」を使うのも良いかもしれませんね。
https://www.excel.studio-kazu.jp/mag2/backnumber/mm20040817.html
 【エクセル・マイスター】より『ブックが開いたとき自動で』

 (HANA)

 (HANA)さん　色々とありがとうございました。

[〒]ボタンを押すことで、確認をして開く　と言う作業は

 　○通常は開いておきたくない
 のですかね？

　については、起動を少しでも早くしたいため、ボタン選択としました。
　また、「auto_open」も実行していたのですが、

やはり起動に時間がかかるため中止しました。
　今回作っているものは、色々な人に使ってもらうため、クイックレスポンスをとりました。

最終的には下記のようになりました。

Sub 〒CSVを開く()

    Dim kbn As Workbook
    Dim gbn As String, hbn As String
        gbn = ActiveWorkbook.Name
        hbn = "〒.csv"
    For Each kbn In Workbooks
        If kbn.Name = hbn Then
            Exit Sub
        End If
    Next
    If MsgBox("〒番号ファイルが開いていません！" & Chr(10) & "開きますか？" _
                    , vbYesNo + vbExclamation, "開くの？") = vbNo Then
        Exit Sub
    Else
        On Error GoTo err

    ChDir "C:\Documents and Settings\nomu\デスクトップ\定期検査"
    Workbooks.Open Filename:="C:\Documents and Settings\nomu\デスクトップ\定期検査\〒.csv"

    End If
    Windows(gbn).Activate
err:
    If err Then
        MsgBox "ファイルが開けませんでした。"

        Else

    Range("I24:R25").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R23C9="""","""",IF(ISERROR(VLOOKUP(R23C9, 〒.csv!R2C1:R14212C2,2, FALSE)),"""",VLOOKUP(R23C9,〒.csv!R2C1:R14212C2,2, FALSE)))"
    Range("I24:R25").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    End If
End Sub

 話しの流れとしては
 １．「〒.csv」と、現在開いているブック名を一つずつ
 　　同じ名前が有るまで比べる。
 　　見つかった時点で終了。見つからなければ
 ２．メッセージボックスを出して　ファイルを開くか確認
 　　「NO」が選択されれば終了。それ以外なら
 ３．ファイルを開く
 ４．最初に開いていたブックをアクティブにする
 ５．エラーの時の処理
 となっています。
 「On Error GoTo err」で、エラーが起きたときには
 その間をすっ飛ばして「err:」の行まで移動します。
 何事もなければ、順調に処理が進み「err:」の行以降が
 実行されます。

 VLOOKUP関数は、ファイルを開いたら実行すればよいので
 ４番の後が良さそうな気がします。
 ・・・単なる好みでしょうけど。

 最初に載せたときに「Application.ScreenUpdating」を
 入れていなかったのですが、コレを入れると
 画面のちらつきが無くなります。
 現在は、順調にいくと
 最初のブックが表示された状態から
 １．〒.csvが表示され
 ２．最初のブックに戻り
 ３．VLOOKUP関数を入れる処理でアクティブセルが動く
 と画面が移っていくと思います。
 Application.ScreenUpdating = False
 を入れると、画面が移らずに処理だけが行われます。

 VLOOKUP関数は、I24:R25の範囲を選択した状態で行われていますが
 結局、I24セルにしか入力されてませんよね？
 I23セルが空欄でないとき、I24セルに結果を表示すればよいので有れば
 こんな感じのはどうでしょう。
　
Sub 〒CSVを開く()
    Dim kbn As Workbook
    Dim gbn As String, hbn As String
        gbn = ActiveWorkbook.Name
        hbn = "〒.csv"
        Application.ScreenUpdating = False
    For Each kbn In Workbooks
        If kbn.Name = hbn Then
            Exit Sub
        End If
    Next
    If MsgBox("〒番号ファイルが開いていません！" & Chr(10) & "開きますか？" _
                    , vbYesNo + vbExclamation, "開くの？") = vbNo Then
        Exit Sub
    Else
        On Error GoTo err
        Workbooks.Open Filename:="C:\Documents and Settings\nomu\デスクトップ\定期検査\〒.csv"
    End If
    Windows(gbn).Activate
    Range("I24") = _
        "=IF(COUNTIF(〒.csv!$A$2:$B$14212,I23),VLOOKUP($I$23,〒.csv!$A$2:$B$14212,2,FALSE),"""")"
    Range("I24").Value = Range("I24").Value
err:
    If err Then
        MsgBox err.Number & ":" & err.Description
        err.Clear
    End If
        Application.ScreenUpdating = True
End Sub

 これだと、番号を入れ替えて新しく住所を反映させたい場合
 一度「〒.csv」を閉じる必要がありますが、それでも良いのですかね？

 (HANA)

 (HANA)さん　ご丁寧に解説頂き短変恐縮です。

(HANA)さんのアドバイスで問題解決できたものと思い、今日は海水浴に行ってまいりました。

帰宅して、もう一度機能確認したところ、〒.csvが開いている状態でボタンクリックすると何も動かないではありませんか！

自分の未熟さを暴露してしまいました。

このＨＰを見て (HANA)さんが追加指導されていることに、また感激です。

ご指摘が沢山あり整理します。

VLOOKUP関数は、I24:R25の範囲を選択した状態で････

　については、実はここはセルを結合しているのでI24:R25となっているものです。

番号を入れ替えて新しく住所を反映させたい場合一度「〒.csv」を閉じる必要がありますが････

　については、下記のようにしました。

　同じ命令が二度使っていてカッコ悪いのですが、私にはこれを改善することができません！

　下記が訂正したものです。

Sub 〒CSVを開く_訂正()

    Dim kbn As Workbook
    Dim gbn As String, hbn As String
        gbn = ActiveWorkbook.Name
        hbn = "〒.csv"
        Application.ScreenUpdating = False
    For Each kbn In Workbooks

    Range("I24:R25").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R23C9="""",""〒No_None"",IF(ISERROR(VLOOKUP(R23C9, 〒.csv!R2C1:R14212C2,2, FALSE)),"""",VLOOKUP(R23C9,〒.csv!R2C1:R14212C2,2, FALSE)))"
    Range("I24:R25").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

        If kbn.Name = hbn Then
            Exit Sub
        End If
    Next
    If MsgBox("〒番号ファイルが開いていません！" & Chr(10) & "開きますか？" _
                    , vbYesNo + vbExclamation, "開くの？") = vbNo Then
        Exit Sub
    Else
        On Error GoTo err

    ChDir "C:\Documents and Settings\nomu\デスクトップ\定期検査"
    Workbooks.Open Filename:="C:\Documents and Settings\nomu\デスクトップ\定期検査\〒.csv"
    End If
    Windows(gbn).Activate
err:
    If err Then
        MsgBox "ファイルが開けませんでした。"

    Else

    Range("I24:R25").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R23C9="""",""〒No_None"",IF(ISERROR(VLOOKUP(R23C9, 〒.csv!R2C1:R14212C2,2, FALSE)),"""",VLOOKUP(R23C9,〒.csv!R2C1:R14212C2,2, FALSE)))"
    Range("I24:R25").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    End If
End Sub

 海水浴ですか、良いですね。
 しっかり焼けてきましたか？

 上でも書きましたが、VLOOKUP関数を２カ所に入れるなら
 　１．〒ファイルが開いていることが確認出来た後（Exit Subする前）
 　３．〒ファイルを開いた後（エラー時処理の前）
 が良いような気がします。
（上手く行かないのですかね？）

 ２カ所に書くのを避けたいなら、ファイルが開いていることが分かった後
「Exit Sub」するのではなく、「GoTo ○○」としてみるのはどうでしょう。
 グーグル等で「エクセル　Then GoTo」等のワードで検索すると
 使用例がご覧になれると思います。

 ≪追記≫
 今回のご質問とは離れますが、住所表示が関数ではなく値になっているので
 住所が表示された状態で、郵便番号を変えた場合　〒ファイルが開いていても
 上のマクロを実行するまでは整合性のない状態ができあがります。
 これとは別に、「郵便番号が変わったら住所をクリア」のマクロを
 用意しておくのが良いかもしれません。

 (HANA)

お盆は親戚、兄弟が集まり酒を飲んだり・・・何かと集中できません！

合間を見ては (HANA)さんのアドバイスを何度かトライしているのですが・・・

どうもよくわかりません！

また、〒.csv名を違う名前にした場合、〒番号ファイルが開いていません！開きますか？のメッセージが出る前にダイアログ（値の更新：〒.csv）が出てきます。

もう、どうしてよいのやら？？？？？？

ご教授頂けませんか？

 お盆ですしねぇ、ぼちぼち行きましょうよ。

 とりあえず
 ＞どうもよくわかりません！
 が、何がよく分かっておられないのか、分かりません。

 「VLOOKUP関数を入れるなら」なぜその場所が良いと言っているのか
 と言う事ですかね？
 それとも「何となく全体的に」よく分からないのですかね？
 「いろいろやってみるけど、どうして上手く行かないのかが分からない」
 のですかね？

 ＞（値の更新：〒.csv）が出てきます。
 これは「〒CSVを開く_訂正」を実行した時の話しですよね？
 そうであれば
 ＞〒.csv名を違う名前にした場合
 と言う行為が問題を起こしたとは思えません。
 同じ名前にもどして実行してみてください。
 〒ファイルが開いていないときに実行した場合
 きっとメッセージが出ると思いますよ。

 どの段階で何を行っているのか
（どこからどこまでが　一区切りなのか）
 最初のコードに書き込みました。
 これだと、値の更新をするか聞かれる理由が分かりますかね？

 ちなみに、↓のコードの動作確認をしていただいて宜しいでしょうか？
 上手く動けば、自信をもって　話を進めさせてもらいたいと思います。
　 
Sub 〒CSVを開く_2()
    Dim kbn As Workbook
    Dim gbn As String, hbn As String
        gbn = ActiveWorkbook.Name
        hbn = "〒.csv"
        Application.ScreenUpdating = False
    For Each kbn In Workbooks
        If kbn.Name = hbn Then GoTo look
    Next
    If MsgBox("〒番号ファイルが開いていません！" & Chr(10) & "開きますか？" _
                    , vbYesNo + vbExclamation, "開くの？") = vbNo Then
        Exit Sub
    Else
        On Error GoTo err
        Workbooks.Open "C:\Documents and Settings\nomu\デスクトップ\定期検査\〒.csv"
    End If
    Windows(gbn).Activate
look:
    If Range("I23") = "" Then
        Range("I24") = "〒No_None"
    Else
        If Application.CountIf(Workbooks("〒.csv").Sheets("〒").Range("A2:A14212"), Range("I23")) = 0 Then
            Range("I24") = "該当データ無し"
        Else
            Range("I24") = _
                "=VLOOKUP($I$23,〒.csv!$A$2:$B$14212,2, FALSE)"
            Range("I24").Value = Range("I24").Value
        End If
    End If
err:
    If err Then
        MsgBox err.Number & ":" & err.Description
        err.Clear
    End If
        Application.ScreenUpdating = True
End Sub

 (HANA)

 (HANA)さん　本当に申し訳ありません。

盆休みも終わり、親戚も兄弟も帰って行きました。

私も今日から出社です。 (HANA)さんのアドバイスに集中します！

＞どうもよくわかりません！

という質問をして申し訳ありませんでした。

自分なりにGoto文でチャレンジしたのですが･････

何れにせよ、 (HANA)さんのご指導の下、最初からやり直してみようと思います。

早速

＜　↓のコードの動作確認をしていただいて宜しいでしょうか？

について、早速試してみました。

しかし、コピペした時点で下記のコードが赤文字となりました。

実行すると、（コンパイルエラー：構文エラー）となります。

 Range("I24") _
                "=VLOOKUP($I$23,〒.csv!$A$2:$B$14212,2, FALSE)"

ご指導お願いします。

（ノム）

 そ・・・そうですよねぇ。
 「=」が抜けてました。  ↓
            Range("I24") = _
                "=VLOOKUP($I$23,〒.csv!$A$2:$B$14212,2, FALSE)"
 追加したのでもう一度お願いします。

 私のマクロが動くかどうかは別として（笑）
 たぶん、全体の流れがつかめれば
 すっきりご理解いただけると思いますよ。

 (HANA)

 (HANA)さん　お手数をおかけしました。

私の思い通りに動き大変感動しております。

〒.csvファイルが存在しないときのエラー表示にも感動です。

ここまでずいぶん時間をかけご指導頂いたことに感謝致します。

ありがとうございました。

（ノム）

 えとですね、私のコードが動いたのであって
 ノムさんのコードが動いた訳ではありませんよ。
 今後のために「どこが悪かったのか」は
 明確にしておいた方が良いと思います。

 先のコードは
 ＞２カ所に書くのを避けたいなら、ファイルが開いていることが分かった後
 ＞「Exit Sub」するのではなく、「GoTo ○○」としてみるのはどうでしょう。
 の方法で書いたものです。

 ノムさんが作られた「〒CSVを開く_訂正」が
 なぜ思いがけないところで　メッセージを出してくるのか
 は分かりましたかね？

 すこしだけ、研究してみて下さいね。

 (HANA)

 (HANA)さん　色々とご指導ありがとうございました。

私のレベルは昔のBasicを素人レベルで勉強した程度です。

gotoなどはBasicであれば行番号へ直接飛ばしその行の処理をさせれば良いのですが、

今回はそれと同じような処理をさせた後ファイル処理で〒.csvが開いているにもかかわらず

「ファイルが開けませんでした」と表示してこの場所から回避さすことができなかったものです。

EXCELはマクロ記録ができるのでBasicよりは便利な部分がありその組み合わせで

それなりにできるのですが、今回のような問題にぶち当たると行き詰ってしまいます。

EXCELでまだまだやりたいことが沢山あるので、基本を勉強しなくては！

また、質問することがあると思いますが、ご指導宜しくお願いします。

（ノム）

コメント返信：

[ 一覧(最新更新順) ]

[[20070811230129]] 『郵便番号ファイルを自動で開きたい』（ノム） ページの最後に飛ぶ

[[20070811230129]]　『郵便番号ファイルを自動で開きたい』（ノム）　ページの最後に飛ぶ