Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2013)
ファイルダイアログオブジェクトと値の更新ダイアログ
投稿日時: 17/08/07 16:43:29
投稿者: japanmap

あるデータを1つのファイルに集計したいのですが、集計元ネタが複数のファイルに分かれています。
対象の元ネタファイルをファイルダイアログオブジェクトで指定した上で、Formulaプロパティを使って
数式で転記しようとしています(転記後にどのファイルからどのように転記したかを数式を見て
分かるようにしたいため)が、マクロを実行すると、ファイルダイアログオブジェクトでファイルを
指定した後に、値の更新ダイアログが出てきて消えません。
 
解決する方法を教えて頂けないでしょうか?
 
以下が実際のコードを簡易化したものです。
 
集計するファイル: file1
集計元ネタファイル: file2, file3
 
Sub filedialog()
    Dim file2 As String 'file2のファイルパス
    Dim file3 As String 'file3のファイルパス
    With Application.filedialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Clear
    .Title = "転記元ファイルを選択してください"
    .InitialFileName = "file1のファイルパス"
        If .Show = True Then
            file2 = .SelectedItems(1)
            file3 = .SelectedItems(2)
        Else
            MsgBox "キャンセルされました"
        End If
    End With
    Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[file2]Sheet2!$C$4:$J$9,MATCH([file2]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
    Workbooks("file1").Worksheets("BBB").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[file3]Sheet2!$C$4:$J$9,MATCH([file3]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
    MsgBox "データ転記完了"
End Sub

回答
投稿日時: 17/08/07 19:15:11
投稿者: WinArrow
投稿者のウェブサイトに移動

file2
file3
は、変数ですから
> Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[file2]Sheet2!$C$4:$J$9,MATCH([file2]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"

    Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[" & file2 & "]Sheet2!$C$4:$J$9,MATCH([" & file2 &"]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
のように修正してみましょう。

投稿日時: 17/08/07 22:34:23
投稿者: japanmap

WinArrowさん
 
コメントありがとうございます!
アドバイス頂いた通り、修正したところ、「実行時エラー 1004 アプリケーション定義またはオブジェクト定義のエラーです」とエラーメッセージが出てしまいました。
 
デバッグすると、以下が黄色くなります。
 
Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[" & file2 & "]Sheet2!$C$4:$J$9,MATCH([" & file2 &"]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
 
マクロでなく普通に数式のリンクを貼ったら、このようになりましたが、[ ]の位置や ' の有無が問題なので
しょうか?教えてください。
 
=IFERROR(HLOOKUP(C$4,'C:\Users\xxxxx\xxxxx\xxxxx\[xxxxx.xlsx]Sheet2!$C$4:$J$9,MATCH([" & file2 &"]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"

回答
投稿日時: 17/08/08 04:29:41
投稿者: simple

>問題なのでしょうか?教えてください。
ご自分のトライはないのですか?面倒だから、ですか?
 
>=IFERROR(HLOOKUP(C$4,'C:\Users\xxxxx\xxxxx\xxxxx\[xxxxx.xlsx]Sheet2!$C$4:$J$9,MATCH([" & file2 &"]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
もう少し正確に書きましょう。'が閉じられていません。file2が残っています。
何か安直ですね。
 
フルパスを元に、上記形式(シート指定の前まで)に変換する
ユーザー定義関数を作るのでしょうか。
例:

Function getAddress(s As String) As String
    Dim p As Long
    Dim s1 As String
    Dim s2 As String
    
    p = InStrRev(s, "\")

    s1 = Left(s, p)
    s2 = Mid(s, p + 1)
    getAddress = "'" & s1 & "[" & s2 & "]"
End Function

それ以前の話として、数式を使った変換よりも
・他のブックとのやりとりは、単純な数値のコピーペイストだけに留め
・それを使った表の作成は、纏め表のほうで実行
するといったほうがよくないですか?
 
それを一つでやるのは、作成やその後の維持管理も大変でしょう。、
他ブック参照の複雑な式が残るのは、
Excelにとっても、人にとっても負荷が残ることになりませんか?

回答
投稿日時: 17/08/08 11:25:39
投稿者: WinArrow
投稿者のウェブサイトに移動

WinArrow さんの引用:
file2
file3
は、変数ですから
> Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[file2]Sheet2!$C$4:$J$9,MATCH([file2]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"

    Workbooks("file1").Worksheets("AAA").Range("C5:J9").Formula = "=IFERROR(HLOOKUP(C$4,[" & file2 & "]Sheet2!$C$4:$J$9,MATCH([" & file2 &"]Sheet2!$B5,$B$4:$B$9,0),FALSE),0)"
のように修正してみましょう。

 
安易なレスをしてしまい申し訳ありません。
 
File2変数の中の
ファイル名だけの部分を「[]」で括る必要があるので、
file2の文字列を分解する必要があります。

回答
投稿日時: 17/08/08 12:56:46
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レスです。
 
file2の文字列分解→再形成のコードです。
 
     file2 = Left$(file2, InStrRev(file2, "\")) & "[" & Mid$(file2, InStrRev(file2, "\") + 1) & "]"

投稿日時: 17/08/08 22:36:05
投稿者: japanmap

simpleさん
 
コメントありがとうございます。
自分でも試してみましたがうまく動かなかったために質問させて頂きました。
アドバイス頂いたFunctionプロシージャやInStrRevが、vba初心者でコードを
書き始めたばかりなので分からなかったので、調べた上で再度トライしてみます。
 
WinArrowさん
 
いえいえ、ヒントを頂けて助かります。
また、追加のコードもありがとうございます。
試してみます。
 

回答
投稿日時: 17/08/09 06:59:04
投稿者: simple

>自分でも試してみましたがうまく動かなかったために質問させて頂きました。
どのようなコードを書いてトライしたのか書いて頂いていれば、もっと良かったと思います。
質問しているんだからそんなこと当然で書くまでもないと思うかも知れないが、
そのことが大切なことなんです。問題点をきちんと意識することにつながるのです。
 
質問の最初に戻ってコメントします(これからのトライにも関係するので書くのですが)。
私は、 "変数" 問題とでも呼称したいと思いますが、
ダブルクォーテーションのなかに変数をそのまま書いてしまって、
なぜ動作しないのか、という質問は、かなりの頻度で寄せられます。
 
・中に入れてしまうと単なる文字列であって、変数の意味は失われてしまいます。
・変数は外に出して、& という文字列連結演算子で 連結してください。
 
変数 = "山田太郎"
MsgBox "私は変数です"
ではなく、
MsgBox "私は" & 変数 & "です"
とします。
 
この基本さえ見失わなければ、あとは文字列をいかに操作するかのテクニックです。
InStrRevは文字列の後ろから特定文字列を探して、ヒットした位置を求める関数ですし、
Left,Mid関数は文字列操作の基本的関数ですからこの際よくおさらいしてください。
がんばってください。

トピックに返信