Q&A
すいませんエクセルの機能だけでできると思ったのですが、マクロが必要な気がして他のところで質問しようと思ったのですが、一番PCに詳しそうなここへ投稿しました。他の所でユーザー数が多いテック系の掲示板ってないですかね？ヤフー知恵袋はあまり詳しくない人が多いので・・・
セルのデータを外部ファイルで参照して、相対パスで表示する事はできますでしょうか？
色々と試した結果、「できない」という結論に達してます。
もしくはファイルを動かしてもセルのデーターを逐一参照して欲しいのですが、
最終的は「ファイルを動かさない」という事になるのでしょうか？
やりたいことは顧客のデータベース化です。
回答4件
0
「やりたいこと」が「顧客のデータベース化」で、
かつ、「セルの数式自体にはこだわらない」、
かつ、もし「外部ファイル（Excelファイル？）」が、「同じフォルダ＝同じ階層 にある」というケース限定なら、その「フォルダ」ならば移動させても・・・、
(a)QueryTableオブジェクト（SQLにて）や、
(b)ADO、DAO、（ループ処理での読み込みやSQLにて）
・・・などを使えば、好きなExcelファイルに「外部ファイル」のシート内容を取り込めるのではないでしょうか？
（そういうことをしたいわけではなかったり、顧客のデータベース化よりも「セル参照」のほうが重要でしたらごめんなさい！また、もうすでにお試しだったら本当にごめんなさい！）
「同じフォルダにないExcelファイルまで、その外部ファイルの場所を変えても、自動的に読み込めるようにする」というのはわかりませんけど・・・。もしそうしたいのでしたら後述のサンプルでは動きませんので無視してください。
例えば以降に挙げたサンプルコードは「同じフォルダ」にある「test01.xls」というファイルの「Sheet1」の表を覗きに行って、取り込みたいExcelファイルのSheet2に表示させます。
「1シートにつき1つの表」、が前提ですけど・・・。
（※コメントがやたら長いので、不要なら全部消して見てみてください。コード自体はかなり少ないです。 あと、「xlsmやxlsx」を読みに行くときは、"DriverId=790;"を"DriverId=1046;"に書き変えたほうがいいかもしれません。）※こちらのテストでは、xlsにも、xlsmに対しても、覗きにいくことができました。 （ダメだったらすみません！）
リボンやクイックツールバーにて「ボタン化」すれば、押すたびに同じ抽出結果が得られます。
あるいはSQLを書き換えれば異なる結果が得られます。
もちろん、外部ファイルのほうでデータが追加されたり、内容の編集があれば、そのボタンを押すたびに最新の状態が反映されます。
覗きに行く「Sheet1」に対する抽出内容は、サンプルコードの
「"SELECT * FROM Sheet1$"」
のところで指定しています。
この内容を書き換えれば、異なる抽出結果が得られます。
長いSQL文になってしまう場合は、各句ごとに、Arrayにてカンマで区切って、CommanText（配列）に格納します。
もし、CommanTextが配列じゃなかったら、カンマで区切らずに1行の長い命令文として格納します。
ひとつの要素あたりで何文字以上何文字未満かなどの制約の状況は未確認です。
細かくする分にはいんじゃないのかと思います。わかりませんけど・・・。
CommanTextが配列かそうじゃないかは、一つ何らかの複雑な条件のMicrosoftQueryの結果の表を手動で作ってみて、それのCommanTextの内容をVBAでイミディエイトに表示させ、句ごとか、あるいは文字数ごとに複数行に分かれて出てきたら配列と思っていいです。
1行だけなら配列じゃないかもです。
配列だったらArreyを使うし、配列じゃなかったら、Arreyは要りません。
SQLを使う場合、複数の条件であっても、1つ1つが複雑でないとか、リレーション（VlookUp関数のような紐つけ）も同時に使いながらとか、でしたら、通常のVBAでデータ抽出（取り込み）するよりははるかにラクだとは思います。
また、リレーションが使えるので、たとえば
・「顧客マスタと売上明細を、”顧客ID”の値で紐付けて、その横結合結果（購入履歴）を表示」とか、
・「顧客IDごとに商品カテゴリごとにグループ化して売り上げ集計」とか、
・「いつからいつまでのどの地域のお客様の売上集計」とか、
いろんなリストアップや集計ができます。
SQL内容を書き換えれば、いろんな抽出結果が得られると思います。
詳しくは、Webや先輩など、SQLのことがよく分かる人などに聞いてみてください。
※SQLの内容を、「MicrosoftQuery」の「方言（？作法？）」で書くと、「MicrosoftQuery」の画面からも操作できます。そして、抽出結果をいろいろいじれます。
その際のSQLの主な方言（？作法？）は、
(01)SQL文の最後の「;」（セミコロン）をつけない
(02)テーブル名は「`」（バッククォート）で囲む。角カッコで囲むことはしない。
などです。
下記サンプルもそういう作法で書いてあります。
なので、できた結果の表を右クリックして「クエリの編集」から、Accessの「クエリ」のようにGUIでSQL生成などができます。
VBA
1'################################################# 2'好きなファイルのSheet2に（B2セルを起点に）、 3'同じフォルダの「test01.xls」のSheet1の表データを 4'QueryTableオブジェクトにて 5'SQLで取り込んで表示する例 6'################################################# 7 8Sub test304() 9 10 Dim o_Qtbl01 As QueryTable 11 Dim s_Cnn01 As String 12 Dim s_FName01 As String 13 14 15 '自ファイルをターゲットにするときに使うだけの設定 16 s_FName01 = ThisWorkbook.Path & "\" & ThisWorkbook.Name 17 18 19 'いったんシートのクリア 20 Sheets("Sheet2").Cells.Clear 21 22 23 '##################################################### 24 'ターゲットの外部ファイルへの接続設定。 25 '##################################################### 26 27 '接続文字が横に長くて見にくいので、見やすく扱いやすくする 28 29 s_Cnn01 = s_Cnn01 & "ODBC;" 30 s_Cnn01 = s_Cnn01 & "DSN=Excel Files;" 31 s_Cnn01 = s_Cnn01 & "DBQ=" & ThisWorkbook.Path & "\" & "test01.xls;" 32' s_Cnn01 = s_Cnn01 & "DBQ=" & s_FName01 & ";" '自ファイルのデータを取り込むときはこちらを使う 33' s_Cnn01 = s_Cnn01 & "DefaultDir=D:\test88;" 'ファイルが見つからなかったときに出てくる「ファイル参照ダイアログ」のデフォフォルダ？ 34 s_Cnn01 = s_Cnn01 & "DefaultDir=" & ThisWorkbook.Path & ";" '同上？ 35' s_Cnn01 = s_Cnn01 & "DefaultDir=;" '仮のルート？ 36 s_Cnn01 = s_Cnn01 & "DriverId=790;" 37 s_Cnn01 = s_Cnn01 & "MaxBufferSize=2048;" 38 s_Cnn01 = s_Cnn01 & "PageTimeout=5;" 39 40 'このサンプルで書き換えるのは基本、 41 '「DBQ=・・・」のフルパス（ターゲットのパス）と、 42 '「DefaultDir」のフォルダパスと、 43 'のちの「Destination・・・」のシート名や表の起点のセル（あとのほうのRangeのところ） 44 'あと、次段階の「.CommandText = Array（・・・）」の行のそのSQLの内容 45 'でOKかと思います。 46 ' 47 48 '##################################################### 49 'SQLの実行と結果表示 50 '##################################################### 51 52 53 '前段階の設定を使って、Sheet2にMicrosoftQueryの結果の表を作成します。 54 '設定の「DBQ=」のフルパスは恐らくUNCパス、つまり、他のPCの共有フォルダのxlsでも 55 '良いのではないかと思います。（ダメだったらすみません！） 56 57 58 Set o_Qtbl01 = Worksheets("Sheet2").QueryTables.Add( _ 59 Connection:=s_Cnn01, _ 60 Destination:=Worksheets("Sheet2").Range("B2")) 61 62 '今回のこの例（上記コード）では、今開いているブックの「Sheet2」シートに対して、 63 '「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。 64 65 'この時点では、QueryTableオブジェクトとしては作成されているのですが、 66 'まだSQL文での条件設定とかをしてないので、Sheet2には何も表示されません。 67 68 'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、 69 '現在表示しているシートに結果の表が生成されます。 70 ' 71 'また、SQL文（表操作の命令文）の中では、 72 'test01.xlsのSheet1（＝システムテーブル）のことを「`Sheet1$`」と 73 '表現しているのですが、これは「システムテーブルと認めさせるために、 74 'シート名に「$」を末尾に付けている・・・」ということになります。 75 'また、Microsoft QueryのSQLの仕様として、そのテーブル名を 76 '「`」バッククォートで囲んでいます。[ ]（角カッコ） で囲んでもOKです。 77 'バッククォートはShiftキーを押しながら「@」を押します。 78 79 80 81 'QueryTablesオブジェクトのSQLを書き換え。 82 '（毎回、空っぽの状態のQueryTablesオブジェクトにSQL文が入ります。） 83 84 o_Qtbl01.CommandText = Array("SELECT * FROM `Sheet1$`") 85 86 87 88 '書き換えたSQLを実行して表に反映。 89 '空っぽのMicrosoftQueryのオブジェクトの中に、SQL文で指定した内容の 90 '表ができあがる・・・みたいな雰囲気です。 91 'この段階ではじめて、表が表示されます。 92 93 o_Qtbl01.Refresh 94 95 96 97 '##################################################### 98 '連続で２つのSQLを実行するときはエラー対策 99 '##################################################### 100' '連続で２つのSQLを実行するときはエラーが出るので、これが必要っぽい。 101' Do While ActiveSheet.QueryTables(1).Refreshing 102' DoEvents 103' Loop 104' '３つの場合はどうなるのか？必要か不要か？未確認。 105 106 107End Sub 108
投稿2019/04/12 13:47
編集2019/04/15 01:14
総合スコア94
0
ファイルを動かしてもセルのデーターを逐一参照して欲しい
基本、エクスプローラー等のエクセルの管理外でファイルを操作した場合、
変更が反映されません。
数式のあるファイルと参照されているファイルをエクセルで一旦開いて、
参照されているファイルを新しく名前を付けて保存で、期待するフォルダーに保存したら、
参照が変わると思います。
（あとで、不要になったファイルはエクスプローラーで削除すれば、最終結果は期待した結果になるのでは？）
まぁ、基本「だめ」ということにしておかないと、面倒なことになると思います。
個人的には、1つのフォルダーに関連するファイルはまとめておいて、
フォルダーごと移動するようにして、
参照式が変わって欲しい場合は、参照式を更新するマクロを作るかと思います。
（出来れば移動ではなく一旦コピーして、数式を更新したあと、
不要なフォルダーを削除するようにした方が管理が楽かなと思います。）
というか、プログラミングの質問ではなくて、エクセルの使い方の質問ですよね？
掲示板のテーマからはずれていると思います。
今一度、質問ポリシーをご確認された方がよいと思います。
それから、エクセルのことは、
エクセルの掲示板で聞いた方が、より良いアドバイスが出てくるかもしれませんよ。
エクセルVBA専門の（専門じゃなくてもVBAの質問も受け付けている場合もあり）掲示板も、
いくつかありますので、検索してみては？
投稿2019/04/12 11:27
総合スコア2167
テック系ってなんですか？
やりたいことが出来たらいいのでは？
難しい言葉を使う前に、
誰にでもわかる言葉で、やりたいことが説明できるようにならないと、
どこの掲示板に行っても解決には至らないと思いますよ。
で、ちなみに僕の説明したやり方では希望の結果がでなかったですか？
それとも、こちらが勘違いしてますかね？
それとも、説明が解らなかったですかね？
それくらいは、返答するのは礼儀ではないのですかね？
他の掲示板の件ですが、
マイクロソフトの製品なのだから、
マイクロソフトのサイトを探せば質問できるようなフォーラムのリンクが
見つかると思います。
そこなら、日本に200人だか300人だかしかいないような、
マイクロソフトから称号をもらっている人（試験等で貰えるものではないけど、興味ないので覚えてないです。）も回答されているようなので、
そっちが安心感は多少あるのかな？
他は、質問して答えてもらうんだから、
「Excel Q&A」など（カタカナにしてみるのもあり？）で、
検索したら見つかるのでは？
賑わってるサイトなんてほぼないかな。
どこのサイトも30分や1時間で回答が貰えるようなことはほぼなさそうです。
回答の内容はどこでも大差ないです。
ですが人によりいろいろなアイデアがあるので、
目から鱗の案が出てくることがあります。
なので、そういう情報を求めて皆さん巡回しているので、
回答者が重複していることが多いです。
ですが、いろいろなサイトで、あちこち聞きまくるのは、
真剣に回答を考えてくれる人に対して失礼なので、
止めましょう。
それが質問掲示板のマナーです。
ここのサイトを検索出来たなら、他のサイトなんてすぐ見つかるとおもいますが？（ここのサイトを検索して見つける方が難しいと個人的には思ってます。）
職業としてプログラマーを目指すんだったら、知っておかなければならない事が沢山あります。失礼ながらWindowsだけだったら大した事はないんでしょうけど、人よりは知ってます。大事なことは聞くことより調べることだ！と言いたいのでしょうけど、誰もが知ってるわけではないのです。回答貰って偉そうな事言ってすみません。でも、長文で諭される様な文体ではないと思います。でも回答はありがとうございます。
>すいませんエクセルの機能だけでできると思ったのですが、
あぁ、、いま、思いつきました。
やりたいことは、別ブックを参照する数式を、
さらに他のブックに変更したいということですよね？
ならば、
データタブ→リンクの編集→リンク元の変更
で、出来ると思います。
0
相対パスで表示する事はできますでしょうか？
ここが良く分かりませんがエクセルで相対パスで外部参照する方法はあります。
自身のパスを取得して、そこにプラスするかSplit()を行い分割すれば一応は...
VBA
1myDir = ActiveWorkbook.Path
もしくはファイルを動かしてもセルのデーターを逐一参照して欲しいのですが
起点より下の階層にないとダメというのが欠点ですが、ファイルの階層読み込みで追うというのもあります。特定の名前や特定の拡張子などの条件がありますが、出来ない事はないと思います。
私のプロフィールからプログラムをダウンロードできますので、良ければご利用下さい。
投稿2019/04/12 03:16
編集2019/04/12 03:17
総合スコア3307
0
エクセルでのデータベース管理はやめたほうがよいと思われます。
作業効率が悪いです。
MySQLのようなデータベース管理システムを導入されるか、小規模であるならばグーグルスプレッドシートがよいと思います。
クラウドなのでファイル間のやり取りがスムーズです。（オフラインでは参照できなくなりますが）
投稿2019/04/12 02:00
総合スコア58
MySQLやAccessは学習に時間がかかるので、今需要がある方法を即実行したいと思います。
グーグルスプレッドでデータベースは構築できますかね？データベースで入力してそれを
抽出できますかね？データベース初心者だったら何をするべきですかね？
顧客の履歴と外部シートのセルの参照がしたいのです。
補足：顧客の訪問履歴
どのようなデータベースなのか、カラムはどんな種類があるのか、値はどのようなものを想定しているのか
（できれば運用方法も、手動でフィルターかけて抽出できればそれで十分なのかなど）
を記載いただけるともう少し助言もしやすくなるかと思います。
自分は顧客リストについてあまり詳しくないので、訪問履歴のデータがどのようなかたちなのかちょっとわかりません。。すみません。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/05/23 09:40
2019/05/24 02:57