エクセルマクロについて質問です ファイルを開くマクロをワイルドカードを使用して作成したいです

質問

エクセルマクロの初心者です
ファイルを開くマクロを作成したいです。

対象のファイルは、
　商品コード（5桁の数字）　スペース　商品名
　たとえば　40157　ベーコンスライス500ｇ
という名前が付いており、100個以上同一のフォルダに入っています。商品コードのみを入力して、マクロを実行すると目的のファイルが開ける。という形式で作りたいです。
商品名まで入力し、開きたいファイル名と完全に一致させれば

Sub 栄養計算()
'
Workbooks.Open Filename:="D:\ekuseru\" & Range("E4").Value & ".xls"

End Sub

で実行できたのですが商品名まで入力するは大変です。
そこで、ワイルドカードを使えばコードのみの入力でできるカナと思い試してみたのですがうまくいきませんでした。一応調べてみて　Like関数を使うとできるという話を見つけたのですが、私が未熟なのかうまくできませんでした。
とても初歩的な質問で恐縮なのですが、自分では完全に行き詰ってしまいましたので、皆様の力をお借りしたいです。お願いします。

回答No.2

mt2008 · ベストアンサー

ワイルドカードでやるなら、Dirで該当ファイルがあるか調べて開いては？
ワイルドカードで無い場合でも、ファイルを開く前に存在するか否かを確認するのは基本です。

Sub 栄養計算()
　Dim sTarget As String
　sTarget = Dir("D:\ekuseru\" & Range("E4").Value & "*.xls")
　If sTarget <> "" Then
　　Workbooks.Open Filename:="D:\ekuseru\" & sTarget
　Else
　　MsgBox ("該当ファイル無し")
　End If
End Sub

商品コードで始まるファイルが複数ある場合は考慮していません。

お礼

回答ありがとうございます。
このコードでできました。
他の回答者様の回答を拝見し、正直わからないことだらけでお恥ずかしい限りです。他の意見も十分に検討して今後に役立てるようにしたいと思います。
もっとマクロについて勉強しようと思います。

回答No.7

Wendy02

#3 の回答者です。

私は、別にファイル名を確認したかっただけなのですが……。
ファイル名にスペースが入っているものは、通常はありえないからです。それでは、入力がしにくくてしょうがないから、疑問に思っただけです。

>このファイル名は私が入社する前からこの形式で管理されていて、現在数百アイテムが存在します。

私のところは、フォルダがいくつもいくつも作られて、中には、まったく関係のないファイルまで入っていました。しかし、それを全部一覧化して、使うものに対して、開くたびにカウントを数えるようにし、ソートを掛けて使うものを一番上にするようにしてしまいました。結局、私自身は、自分で作った一覧表からでないと、エクスプローラ等では、どこにファイルがあるのかさっぱりわかりませんでした。

>この場合は一覧を作るのが大変なので今回の質問にいたりました。

そういうものは、Dir で取ればよいのです。
数百～数千であっても、1分も掛からないと思います。

'----------------------------
Sub filedir()
Dir i As Long
Dim myPath As String
Dim myFname As String
'フォルダ名
myPath = "C:\Documents and Settings\[UserName]\My Documents\"　'必ず \ を最後に入れてください。
myFname = Dir(myPath & "*.xls")
i = 1
Cells(i, 1) = "ファイル名前"
Do While myFname <> ""
　　i = i + 1
　　Cells(i, 1).Value = myFname
　　myFname = Dir()
Loop
End Sub

'----------------------------
そして、出てきたファイル名に対して、
他の方もご指摘の、ワークシートの一覧に、

B列に、
=HYPERLINK(A2)

だけでも十分だと思います。
ワイルドカードで出てくるようなプログラムも出来ますが、初心者とおっしゃるなら、この際は、あまり凝ったものは作らないほうがよいかと思います。

お礼

回答ありがとうございます。
＃３様のご質問の意図が理解しきれず申し訳ございませんでした。商品名は例のとおりです。スペースが入っているのは商品名の頭に数値が来るものがあり、見にくいから入っているようです。
＃２さんのコードで解決しましたが、こちらのコードも自分のスキルアップのために今後試してみたいと思います。再度の丁寧なご回答ありがとうございます。

回答No.6

imogasi

商品コードが決まる（全桁入力される。商品コードの一部でないですよね）ーー＞商品名が一意に決まるーー＞その商品名でファイルをオープン
でよいのでしょう。
なぜワイルドカードの仕組みを使う必要が有るのか。見当亜ずれではないか。
商品コードから対応する、商品名を検索すればよい。
商品コードー商品名のわかるコンピュター資料（＝ファイル）は既に有るのか、今後作るつもりなのか。
これはどうしても必要。
ーー
そして検索は、対応表がエクセルシートに有る場合は（なければシートに対応表を作り）
（１）ＶＢＡのWorksheetFunctionのＶＬＯＯＫＵＰを使う
（２）Ｆｉｎｄメソッドを使う
（３）表を総当たりして聞く。
（４）表を商品コード順にソートしておいて、２分探索
などの方法が考えられる。

お礼

回答ありがとうございます。
会社のコンピューター資料はあるのですが、ちょくちょく商品名が変更になってしまうので、自分の部署で作っている資料のファイル名と完全に一致しないものがあるという困った状況になっています。コードは変わらないのでワイルドカードを使えばできるかなと考えた次第です。他の方法もあるのでしょうが、何分未熟者なもので…
＃２さんのコードで解決しましたが、今回いろいろなアドバイスをいただいたので、ひとつずつ今後試してみたいと思います。

回答No.5

hallo-2007

差し出がましいようですが、私も他の方と同じような意見です。
Sub 栄養計算()
Workbooks.Open Filename:="D:\ekuseru\" & Range("E4").Value & ".xls"
End Sub
でいちいちE4セルに商品コード入力してファイルを開くより、一覧表でリンクさせる。
或いは、入力規則などで商品名を選択したらファイルが開くほうが便利で間違いがないと思います。
既に、E列に商品コード、F列に商品名がデータとして準備されているのであれば
ハイパリンク関数で=HYPERLINK(E4&"　"&F4,F4) と入れてコピィすれば
その列をクリックするだけでファイルが開きます。
もし、コードと商品名の対応表がなければ、
>ファイル名の入った一覧を作って更新する（日々増えるので）のがちょっと大変なので
なら下のスレのコード参考にしてください。
http://oshiete1.goo.ne.jp/qa4480927.html?ans_count_asc=20
フォルダ内のファイルへの一覧表とリンクを作成します。
エクセルファイルの絞り込む必要あれば　A1セルに .xls と入れて実行してみてください。
ただ、日々ファイルが増えるのであれば１年で２～３００個？
将来的には大変なことになると思います。

お礼

回答ありがとうございます。
アイテムは日々増えますが、アイテムカットもあるので総数はそれほど増えません。
＃２さんのコードで解決しましたが、こちらのコードも自分のスキルアップのために今後試してみたいと思います。

回答No.4

mitarashi

この間似たようなのを作ったなと思って、いじっていたら、#3さんの案を実現した形になってしまいました。
Sheet1の、B1にフォルダー名（末尾に\をつけること）
B2にファイル名のワイルドカード　例：20081205*.xls
を入力して、次のマクロを実行すると、C4以下にリストが得られます。
Sub test()
Dim fileName As Variant, folderName As String
Dim destRange As Range

Sheets("Sheet1").Columns("c").ClearContents
Set destRange = Sheets("Sheet1").Range("c4")
folderName = Sheets("Sheet1").Range("b1").Value
fileName = Dir(folderName & Sheets("Sheet1").Range("b2").Value, vbNormal)
Do While fileName <> ""
destRange.Value = folderName & fileName
Set destRange = destRange.Offset(1, 0)
fileName = Dir()
Loop
End Sub

Shee1のイベントマクロは
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Columns("c")) Is Nothing Then Exit Sub
Workbooks.Open fileName:=Target.Value
End Sub
イベントマクロの使い方については参考URLをご覧下さい。

参考URL：

http://hp.vector.co.jp/authors/VA016119/hajimete/event1.html

お礼

回答ありがとうございます。
＃２さんのコードで解決しましたが、こちらのコードも自分のスキルアップのために今後試してみたいと思います。

回答No.3

Wendy02

こんばんは。

Excelですよね？
ファイル名はどうなっているのですか？
例えば、
「40157　ベーコンスライス500ｇ.xls」
なんてなっていたら、管理上かなり扱いにくいと思います。

私なら、Excelのワークシートにリストを作っておいて、ダブルクリック・イベントで、目的のファイルを開くように作ります。ファイル名などを選んで、ダブルクリックすればよいわけです。

ただ、栄養計算で規定の場所に書いてある情報を呼び出すだけなら、わざわざファイルを開く必要はないように思います。

補足

回答ありがとうございます
このファイル名は私が入社する前からこの形式で管理されていて、現在数百アイテムが存在します。ですので、私の一存で管理方法を変えるのは非常に困難なのです。別のファイルでWendy02様のおっしゃるように管理しているものも実はあるのですが、この場合は一覧を作るのが大変なので今回の質問にいたりました。

回答No.1

hana-hana3

＞商品コードのみを入力して～
商品名まで入力するは大変です。

ヒント・・・VLOOKUP関数って知ってますか？

補足

回答ありがとうございます
VLOOKUPでやることも考えたのですが、ファイル名の入った一覧を作って更新する（日々増えるので）のがちょっと大変なので、コードのみでやれればそれがベストかなと思っています。