VBAでFileDialogを利用してファイル参照したいのですが

質問

EXCEL2002、SP2上での質問です。
ユーザーフォームをVBAで作成中です。
主な機能は、区分をコンボボックスで選択させた上で、
取込みたいファイルを参照し、取込実行ボタンを押すという感じに
作っています。
質問は、取込みたいファイルを参照させ選択したあと、すぐに処理を
行うのではなく、一度参照した結果のフォルダー名とファイル名を
別の窓（ツールボックスのどのコントロールがベストチョイスか不明）
に表示させ、取込実行ボタンでコンボボックスで選択した内容と
参照したファイルを取得するようにしたいのですが、別の窓に参照結果
を表示させる方法がわかりません。
説明が長くてわかりにくいと思いますが、参照ボタンの記述を下記に
記載しますので、参照後の処理をどのようにしたらよいか教えて
下さい。
Private Sub 参照_Click()
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "テキスト", "*.csv;*.txt", 1
If .Show = 0 Then Exit Sub
→ここに参照した後の処理を追加したいです。
End With
End Sub

回答No.6

Wendy02 · ベストアンサー

こんばんは。

お仕事、大変ですね。Excelは、Excel独特の使い方のようなものがありますね。どちらかというと、今回の場合は、UserForm は、あまり使われないような気がします。何か、このままで行くと、とても、複雑になりつつあるような気がします。

>表示されているだけだと、ファイル名が指定されていませんに行ってしまいます。記述がたりないのでしょうか？

気が利かなくてすみません。たた、ひとつだけ表示されていたら、そのまま、指定されたことにして、file_name に格納してしまうことでしょうか？（ご存知だと思いますが、ファイル名は、複数選択できますね。私は、最初から複数選択していると思っていたので、そこに違いがありました。）

Private Sub CommandButton2_Click()
　Dim file_name As String
　　If ListBox1.ListCount = 0 Then
　　　　MsgBox "ファイル名が指定されていません", vbInformation
　　ElseIf ListBox1.ListCount = 1 Then
　　　　file_name = ListBox1.List(0)
　　　　MsgBox file_name
　　ElseIf ListBox1.ListCount > 1 And ListBox1.Text = "" Then
　　　　MsgBox "ファイル名をひとつ選択してください", vbInformation
　　End If
End Sub

>配列を作って一度ファイルの中身を格納後データを探してコピーしていますが、処理が遅くなることが見え見てです。

それと、配列を作って？
テキストファイルですよね。テキストファイルを直接配列にしてしまえばよいと思います。もしかしたら、Excelを通していませんか？それは、遅いです。

ただ、行と列がどの程度の大きさになるのかが問題ですね。分からない場合は、キメウチで大きさを決める以外は、2次元配列にはできませんから、1次元配列にして、1行ずつ貼り付ける方法もあります。実際にやってみると、そんなにストレスは感じないで、済むはずです。

Application.ScreenUpDating =False
'セルに入れるコード
Application.ScreenUpDating =True
とすればよいです。

たとえば、こんなスタイル（他のアプリケーションで、"" の文字列の括り記号が入っているので、抜いています。"" がなければ、WorksheetFunction.Substitute はいりません。）

Dim FileNo As Integer
Dim FileName As String
Dim TextLine As String
Dim myArray As Variant
Dim i As Long

　　FileNo = FreeFile()
　　If FileName <> "False" Then
　　Open FileName For Input As #FileNo
　　Do While Not EOF(FileNo)
　　　　Line Input #FileNo, TextLine
　　　　i = i + 1
　　　　If Len(TextLine) > 1 Then
　　　　TextLine = WorksheetFunction.Substitute(TextLine, """", "")
　　　　 myArray = Split(TextLine, ",")
　　　　Cells(i, 1).Resize(, UBound(myArray) - LBound(myArray) + 1) = myArray
　　　　End If
　　Loop
　　End If
Close #FileNo

結局、Excelの場合は、ここで、ADO を使うにしても、ADO自体を保持しているわけではないので、ワークシートを経由しているのと、あまり大して違わなくなってしまいます。

お礼

Wendy02さん
丁寧な解説ありがとうございます。

自分でもなぜユーザーフォームに拘っていたのか、ユーザーフォームを
使わなくても、ファイル名が指定できれば処理できるので、作りを変えることにしました。

入力フィールドには、リストボックスで選択させることで対応できそうです。
参照したファイルの中には、４列・数行のデータしたないことがわかり
それならば選択したファイルを、Workbookに読み込みVLOOKUPで十分
対応できることがわかりました。
Const 範囲 As String = "Sheet1!A1:D100"
のような記述をしておくとファイルが変わっても応用が利くので
記述する行が減って、結果的に処理スピードも上がった気がします。

＞Application.ScreenUpDating =False
'セルに入れるコード
Application.ScreenUpDating =True
とすればよいです。

これも追加したら体感的に早くなりました。

いろいろな選択肢があるわけですが、難しく作りすぎていたようです。
ご指摘を受けて冷静に処理を考えることができました。
まだ改造途中なため、クローズせずにおきたいと思います。
ひとまずお礼を先に申し上げておきたいので。ありがとうございました。

回答No.7

Wendy02

こんばんは。

お忙しいところですから、あまり、かき回してしまってはいけないと思いつつも、思い出したことなのですが、ひとつだけ古いワザを書いておきます。決してお勧めではないのですが、時には、役に立ちます。以下の中から気に入ったものを、ユーザー定義型 Function にしておいて、それをループで使えば、かなり使えます。

これは、MS-DOS時代からの方法で、もともと、Excelのファイル構造が、シーケンシャルではなくて、たぶんランダムファイルのようになっているからだと思うのです。だから、Excelファイルは、直接、データベースファイルとしても使えるわけです。

『ファイルを開けないで、その値をとる方法』
もちろん、ADOなら、取得だけでなく、変更も可能なのですが、これは、それよりも原始的な取得するだけの方法です。

Sub DirectGetValues()
Dim MyDir As String
Dim MyFile As String
Dim MySheet As String
Dim MyStr As String
Dim i As Integer
Dim MyAddress As String
Dim Ar(9, 0) As Variant '0-9 までの10個格納
'
'下の３つの変数は任意で実際のものに変更して下さい。
MyDir = Application.DefaultFilePath
MyFile = "test.xls"
MySheet = "Sheet1"
'
'あるセルの値。（ここではA1）
'//R1C1 形式で書くこと //

MyStr = "'" & MyDir & "\[" & MyFile & "]" & MySheet & "'!R1C1"
MsgBox Application.ExecuteExcel4Macro(MyStr)
''Debug.Print MyStr ' 分からなくなったら、Debug.Print でみてください。
'
'
'ある範囲の数値の合計。（ここではA2:A11）
MyStr = "SUM('" & MyDir & "\[" & MyFile & "]" & MySheet & "'!R1C1:R10C1)"
MsgBox Application.ExecuteExcel4Macro(MyStr)

'セルひとつずつから取得する方法　A1:A10
Application.ScreenUpdating = False
For i = 1 To 10
MyStr = "'" & MyDir & "\[" & MyFile & "]" & MySheet & "'!R" & i & "C1"
Cells(i, 1).Value = Application.ExecuteExcel4Macro(MyStr)
Next i
Application.ScreenUpdating = True

'配列変数で取る方法 (速い） A1:A10

MyAddress = Range("A1:A10").Address(1, 1, xlR1C1) 'A1をR1C1 方式に転換する方法

MyStr = "'" & MyDir & "\[" & MyFile & "]" & MySheet & "'!" & MyAddress
For i = 1 To 10
Ar(i - 1, 0) = Application.ExecuteExcel4Macro("INDEX(" & MyStr & ", " & i & ",)")
Next i
Cells(1, 2).Resize(10).Value = Ar()
End Sub

お礼

Wendy02さん
No7のワザ、私にはとっても理解しやすかったです。
ＤＯＳで育ってきた私には、こういう考え方のほうがわかりやすいです。
今回は、No6の回答を元に作り直しました。最初の質問はユーザーフォームで
ファイル名を選択させて、それを画面に表示させてからマクロを実行する
ように作っていましたが、これは、マクロの中でFileOpenさせユーザーフォームの使用をやめました。
入力規制をかけてセルに必要事項を選択させるようにすれば、わざわざ
ユーザーフォームを使うまでもなかったので、
ご指摘いただくまで、難しく作ろうとしていました。

処理自体の記述が減り、結果的に処理は早く終わるようになりました。
まだ改造の余地はありそうですが、ひとまずこの質問はこれにて終了とさせて
いただきます。ありがとうございました。

回答No.5

Wendy02

こんにちは。Wendy02です。
もし、良かったら、こちらの時間の許す限りのことはします。

>ユーザーフォームを使うのは初めてに近く、分厚い本をがっつり見ても遅々として進みません

私がExcel VBAを習っていて、思ったことですが、

・ひとつは、UserFormの詳しいマニュアルが存在しないこと。
・もうひとつは、いまひとつ英語のプロパティがしっくりと覚えられないこと。＋ヘルプが思うように出てこないことと、そのヘルプが良く分からないこと。

が挙げられるのではないでしょうか？VB6の学習書のようにフォームのコントロールは、懇切丁寧には、どの本も書かれていないので、分かったつもりでも、実践では、UserFormを作成中に行き詰まってしまうのです。4年目に初めて、初歩的な使い方を知るなんていうこともありました。

それと、前回、私が書いたコードのExcelではあまり区別がありませんが、　ListBox1.Value と、ListBox1.Text では、どちらかというと、[ListBox1.Text] がベターでした。ごめんなさい、ついうっかりしていました。（^^;基本は、Text プロパティでした。

>If IsNull(ListBox1.Text) Then

Accessの使い方ですね。でも、Excelでは、IsNull のNull 値は、Variant 型の属性ですから、未格納の値でも、Text 値にはありません。ListBox1.Valueなら可能ですが、基本は Text 値なので、この初期値は、「""」（長さ0の文字列）です。

If ListBox1.Text ="" Then になります。（この違いって、ややこしいです(--;)）

'参照ファイルの読み込み
If ListBox1.Text ="" Then
MsgBox "ファイル名が指定されていません", vbInformation
Else
file_name = ListBox1.Text
End If

**
, vbInformation こういうものを足して、ちょっとイメージを付けたします。

それから、

>振込銀行名と参照で開いたファイルの中身（日付、会員番号、金額）を該当会員番号にセットするというものです。

今は、やってみないとはっきりいえないのですが、もしかして、これ自体をファイルの中から探していませんか？言い換えると、手作業で探すのではないかと。

私の単なる想像でしかありませんが、
ファイルを探す　-> ファイルを開ける　-> その中のデータを探す　->コピー　->
元のファイルに貼り付け　-> 開けたファイルを閉じる

という一連の動作ではないでしょうか？
もし、そうなら、一気にする方法があるような気がします。偉そうに言って、私自身が、どこまでできるかなっていう感じがしますが。どちらかというと、リレーショナル・データベースの使い方のようですね。

補足

ありがとうございます。助けていただけると心強いです。＾＾

> vbInformation こういうものを足して、ちょっとイメージを付けたします。

雰囲気変わりますね。今後こういう気遣いを付加していきたいと思います。ところで、ファイル名が表示されている場合、ファイル名がクリックされている（青い帯がかかっている）状態の時には、拾えるのですが
表示されているだけだと、ファイル名が指定されていませんに行って
しまいます。記述がたりないのでしょうか？

Wendy02さんの想像通りの処理を行おうとしてます。
>リレーショナル・データベースの使い方のようですね。
余談ですが、
もともと、私自身ACCESSのクエリー操作が得意で、過去こういった物は
ACCESSで作っているのですが、今回の機能はOfficePersonalが入って
いる人の端末で、ACCESSでは作れないのです（泣）また、EXCEL操作も初級者なので作ったはいいが手離れしない状況です。
EXCELのユーザーフォームを利用して処理しようとしたのも
そういった背景からで、あまりEXCEL操作に長けていなくても
処理ができるようにするために作っています。
なので、エラー処理は必ず盛り込む必要があって、逆にファイルを
開いて確認させずにできるだけ入力もさせずに、マクロを実行させたい
という思惑です。ACCESSでできればすぐなんですが・・・

ファイルを開いたら、中身は全部無条件に拾います。
なので、配列を作って一度ファイルの中身を格納後データを探して
コピーしていますが、処理が遅くなることが見え見てです。
よい方法があれば教えてください。
よろしくお願いします。

回答No.4

Wendy02

こんにちは。

私は、Excelのユーザーフォームは苦手です。（^^;
すごく、雑多というか、自分でいろんなコントロールは付けたしできるのですが、仕様書がありませんからね。だから、VB6のテキストを使わなくては良く分からない部分って、多いですね。

>ファイルの種類は、CSVかTXTのどちらかなのでOpenTxtはCSVの時に使用できませんよね？
それは、使えます。ただ、それは、本来の目的ですよね。

今試したわけではないのですが、ヘルプには、
「DataType: の　xlDelimited がデフォルトで、この引数を省略すると、ファイルを開いたときにデータの形式が自動的に決められます」と書いてあるのですが、

CSV の場合は、[区切り位置]の設定のデフォルト状態（もし、起動後の使った場合は、その設定）にしたがって、切り分けてしまいます。だから、もしも、特殊な切り分けの場合や、起動後にデフォルトの方法以外で使ってしまった場合は、CSV の拡張子の場合には、デリミタ（「,（コンマ）」 区切り）等の指定を、改めて設定してあげないといけなかったと思います。とりあえず、試してみてからにしてください。

Excelで開く目的でないなら、メモ帳を使ってしまっても良いのではありませんか？

こんなものができます。（ただ、あまり大きなものはダメです。大きなものは、フリーのテキストエディタに換えたほうがよいです）

たとえば、こんな風にしてみました。個人的には、これって意外に使えるなって思いました。私の好みを言ってももしょうがないけれども（^^;。。

'ダブルクリックをして、メモ帳で開く
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
　　Shell "Notepad.exe " & ListBox1.Value, vbNormalNoFocus　'メモ帳で開く
　　'Workbooks.OpenText ListBox1.Value　　'Excelで開く
End Sub

'内容を確認したら、右クリックで、Excelでオープン
Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
　If Button = 2 Then '右クリックしたら
　　Workbooks.OpenText ListBox1.Value
　End If
End Sub

補足

ご回答ありがとうございます。
私も、これまでいくつかマクロを作ってきましたがユーザーフォームを
使うのは初めてに近く、分厚い本をがっつり見ても遅々として進み
ません（T_T)

今作成しているのは、参照したファイルの中にある日付で始まるレコー
ドを別ファイルに格納していこうという機能です。
実際には、日付と会員番号と金額を格納するのですが、参照した
ファイルは今のところ開かなくても大丈夫なので、この追記は後日に。
上記記述は、出来上がったあとで追加したい機能ですが・・・。

参照したファイル名はリストボックス上にファイル名が表示されていて
取込ボタンを押すと、別のコンボボックスで先に選択しておいた
振込銀行名と参照で開いたファイルの中身（日付、会員番号、金額）を
該当会員番号にセットするというものです。

基本的な質問だと思いますが、リストボックスにファイル名が表示
されていなければエラーにしたいのですが、表示されていてもエラー
処理されています。
どう記述したら解決しますか？
'参照ファイルの読み込み
If IsNull(ListBox1.Text) Then
MsgBox ("ファイル名が指定されていません")
Else
file_name = ListBox1.Text
End If

回答No.3

Wendy02

こんばんは。

>参照ボタンの横に空欄のテキストボックスを用意しておき

通常は、そういう場合は、リストボックスを使い

For i = 1 To .SelectedItems.Count
　　　　　 ListBox1.AddItem .SelectedItems(i)
Next i

こんな感じにしますね。それで、リストボックスのプロパティのColumnWidths を、多めにとります。（例：400 pt) とすると、スクロールバーが現れますので、リストボックスの大きさは変えずに、コンパクトな場所に納めることができます。

ご質問の最初のコンボボックスのご説明は良く理解できませんが、このように作っておいて、リストボックスに、DoubleClick イベントで、開けるようにします。

補足

Wendy02さん、的確なご回答ありがとうございます。
ファイル名の表示が長かったので、ご指摘のようにColumnWidthsを
設定してフォルダ名とファイル名を全て表示することができました。

ところで、私はユーザーフォームの作成自体あまりやったことが
ないので、理解できていない状況なのでさらに質問させていただくと

>リストボックスに、DoubleClick イベントで、開けるようにします。

この処理を追加するべく記述したのですがうまくいきません。
ファイルの種類は、CSVかTXTのどちらかなのでOpenTxtはCSVの時に使用
できませんよね？
また、TXTファイルを指定した時、ダブルクリックで開く方法を
教えてください。
よろしくお願いします。

回答No.2

pkh4989

テキストボックスに表示するなら、以下のようにすればいいのではないでしょうか。

Private Sub 参照_Click()
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "テキスト", "*.csv;*.txt", 1
If .Show = 0 Then Exit Sub
Me.TextBox1.Text = .SelectedItems(1) '←テキストボックスに表示する
'
End With
End Sub

お礼

やりたいことができました！
.SelectedItems(1)の左辺の記述がよくわからなかったので
実際やってみて、思ったとおりにできたのでこれからも
いろいろ試してみたいと思いますが、今回取得したファイル名は
サーバー上のファイルを参照するため、表示されるファイル名が
長く、テキストボックスではなくリストボックスで対応いたしました。

どうもありがとうございます。

回答No.1

pkh4989

こんにちは。
メッセージボックスにしたのですが、
こんな感じでしょうか。

Private Sub 参照_Click()
Dim wMsg As String
With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "テキスト", "*.csv;*.txt", 1
If .Show = 0 Then Exit Sub
wMsg = "参照ファイル(" & .SelectedItems(1) & ")を取込しますか？"
If MsgBox(wMsg, vbQuestion + vbYesNo) = vbYes Then
'取込処理
End If
End With
End Sub

補足

メッセージボックスを使用せずに、表示させたいと考えています。
というのも、このユーザーフォームには、コンボボックスから選択した
区分によって、取込ファイルの格納先を変更するようにしたいので
コンボボックスで選択した内容と、参照で表示したファイル名が
同一ユーザーフォーム上で表示され、その後取込ボタンで処理される
ようにしたいと考えています。
例えば、参照ボタンの横に空欄のテキストボックスを用紙しておき
参照によって指定されたファイル名を表示する場合の表記は
どのようになりますか？