ファイルを開かずリストを参照したい

ブックAのシート上にコンボボックスを置いています。
そこに表示させる選択肢はブックBのSheet1にあります。

今は入力範囲を「ブックB.xlsm!Sheet1」としているのですが、
この方法だとブックBを開いたままにしておかなければなりません。

ブックBを開かずに済む方法を教えていただけませんでしょうか？
AddItemで一つずつ登録してみたりもしたのですが、
選択肢は増えたり減ったり、内容が変わることがありますので、
できれば別な方法を教えていただけましたら幸いです。

よろしくお願いいたします。

ttyp03

2020/05/27 02:06

ブックAを開いている間にブックBの内容が変わることがあるのでしょうか？ブックAを開いたときのみブックBからコンボボックスが生成されればよいのでしょうか？マクロは使っていいのでしょうか？

mi_ku

2020/05/27 08:07

ttyp03さん、ありがとうございます。ブックAを開いているときにブックBの内容が変わることはあります。ブックAを開いたときのみブックBの選択肢が使いたいです。マクロは使っても大丈夫です。よろしくお願いいたします。

ttyp03

2020/05/27 23:57

ブックAを開いているときにブックBの内容が変わるのであれば、ブックBは開きっぱなしにしておくしかないのでは。ブックAを開いたときにブックBの最新の情報を使うのでよければ、開いて読み込んですぐに閉じれば済みますが。

mi_ku

2020/05/28 00:26

ご回答ありがとうございます。＞ブックAを開いたときにブックBの最新の情報を使うのでよければ、開いて読み込んですぐに閉じれば済みますが。こちらのほうで問題ありません。その場合はどのようにしたらいいのか教えていただけませんでしょうか？

ttyp03

2020/05/28 01:42

コンボボックスはフォームコントロールですか？ActiveXコントロールですか？

mi_ku

2020/05/28 05:33

コンボボックスはフォームコントロールで、シート上に直接配置しております。

行動規範の内容に同意します

回答2件

こちらが参考になるんではないかと。
ブックを開かないでセルのデータを読む

投稿2020/05/27 01:36

kaputaros

総合スコア1844

mi_ku

2020/05/27 08:05

kaputarosさん、ありがとうございます。こちらも検討したのですが、これをどう選択肢に使うのかわかりませんでした。

kaputaros

2020/05/28 01:11

VBAあまり詳しくはないのでなんとなくになってしまうのですが、 ExecuteExcel4Macro("'C:[Book1.xls]Sheet1'!R" & i & "C1") 変数になってるのでわかりにくいかもですが、 'C:[Book1.xls]Sheet1'!R1C1　(i=1と仮定) 　↓↓ 'パス＋[ブック名]シート名'!参照セルこのR1C1が何を表すのか分かるならその範囲指定ができれば値を引っ張れそうで、マクロの関数か何かを作ってそこでリストの値を返すようにしてコンボボックスに指定したら行けそうな気がします。

mi_ku

2020/05/28 05:37

ブックを開かずに処理を行うことについてネットで検索したらよくこちらのコードが出てきました。何とか範囲を指定するところまではできそうですが、その先をどうしたらよいのかわかりません。

kaputaros

2020/05/28 05:44

この辺見たらわかりそうですが。。。検索キーワード：コンボボックス中身取得　VBA https://www.sejuku.net/blog/37777

mi_ku

2020/05/28 09:22

アドバイスありがとうございます。参考にさせていただきます。

行動規範の内容に同意します

ベストアンサー

ブックAを開いたときにブックBの最新の情報を使う

この方法のサンプルコードです。
ThisWorkbookに貼り付けてください。
ファイル名とかシート名とかコンボボックス名とかは適宜修正してください。
あとブックBのA列1行目から下に向かって空セルまでをコンボボックスに登録しているので、こちらも適宜修正してください。

VBA
1Private Sub Workbook_Open()
2
3    Dim r As Long
4    Dim shA As Worksheet
5    Dim bkB As Workbook
6    Dim shB As Worksheet
7    Set shA = Worksheets(1)
8    Set bkB = Workbooks.Open("c:\temp\ブックB.xlsx")
9    Set shB = bkB.Worksheets(1)
10    
11    r = 1
12    With shA.DropDowns("Drop Down 1")
13        .RemoveAllItems
14        Do While shB.Cells(r, 1).Value <> ""
15            .AddItem shB.Cells(r, 1).Value
16            r = r + 1
17        Loop
18    End With
19    
20    bkB.Close
21    
22End Sub