• ベストアンサー
  • 暇なときにでも

エクセルで他のBookのデータを入力規則のリストに…

  • 質問No.1669601
  • 閲覧数2101
  • ありがとう数0
  • 気になる数0
  • 回答数6
  • コメント数0

お礼率 40% (10/25)

エクセル(Excel2002)の入力規則についての質問です。
仮にBook1のsheet1のA列1~10行に元の値となるデータを作成し,これに「データ」と名前をつけます。
このデータをBook1とは異なるBook2のA列1行において入力規則の元のデータに設定しドロップダウンリストから選択することは可能でしょうか?
Book1とBook2は同一フォルダにあります。
ご教示ください。

質問者が選んだベストアンサー

  • 回答No.6
  • ベストアンサー

ベストアンサー率 62% (785/1258)

#5 です。

バグ発見です。すみません。

#5 のコードを次のように訂正します。

(誤)
  'データ範囲の名前をチェック
  Set rngDat = WB.Names("データ").RefersToRange

(正)
  'データ範囲の名前をチェック
  Set rngDat = WB.Names(DatRangName).RefersToRange

その他の回答 (全5件)

  • 回答No.5

ベストアンサー率 62% (785/1258)

こんにちは。KenKen_SP です。

お返事遅くなり、すみません。

> やはりVBAを使わなければ無理ですか…。

いえ、#1 の方の方法を使えば、VBA なしでも可能なのですが、今回の
場合、リンクを張っていくのにとにかく根気が必要でしょう。

> 自分がやろうとしていることは,ExcelではなくAccessでやるべきこと
> なのでしょうか!?

Access を使っても良いのですが、現実的には 「Excel の方が慣れて
いるので、できれば Excel で、、」との声があがってきそうです。
また、Access VBA も決して簡単ではありませんし、今回の場合、もっと
難易度の高いものになりそうです。

それならば、Excel VBA を使って可能な限り楽をしようかと、、

#3 のコードはイマイチ不完全燃焼なので、書き直してみました。コー
ドが長いのはエラー処理を充実させたためですから、実際にやっている
ことはたいした事ではありません。

マクロ(VBA)が使用可能な状況がどうか分かりませんが、手順などを
記載しておきますので、よろしければ一度お試し下さい。

【使い方】

ブックを開くと「リストを更新するか?」と聞いてきますので、[OK]
をクリックすると参照先ブックからデータを読み取って、入力規則を
自動設定します。即時更新が必要ならマクロ「リスト更新」をその時
に実行して下さい。

【注意】
 ・参照先ブックは同一フォルダ内において下さい。
 ・参照先ブック名やシート名などを予め決めておく必要があります。
  コード中の Setting Param を必要なら修正し、調整して下さい。
 ・まずはテスト用ブックで動作確認して下さい。つまり、自己責任で
  お願いします。(←ここ重要)

【手順】

1. 入力規則を設定するブックのみを開く(他は閉じて下さい)
2. [Alt]+[F11]キー押下で Visual Basic Editor が開く(以下 VBE)
3. VBE のメニューから[挿入]-[標準モジュール]をクリック
4. 開いたスペースに以下のコードをコピー&ペースト
5. VBE 閉じる
6. ブックを保存し、一度閉じます
7. 再度ブックを開いて動作を確認します

’以下コード

Sub Auto_Open()

  Call リスト更新

End Sub

Sub リスト更新()

  Dim Dummy  As Variant
  Dim WB    As Workbook
  Dim SH    As Worksheet
  Dim rngDat  As Range
  Dim rngCel  As Range
  Dim lngR   As Long
  Dim sAddress As String
  
  '--------------------------------------------------- Setting Param -------
  '参照元ブック名
  Const DatBookName As String = "Book1.xls"
  '参照元ブックデータ範囲の名前
  Const DatRangName As String = "ListData"
  '作業用シート名
  Const sTempShName As String = "_ListTempData"
  '入力規則を設定するシート名
  Const sTargetName As String = "Sheet1"
  '-------------------------------------------------------------------------
  
  '更新確認
  lngR = MsgBox("リストを最新の情報に更新しますか?", vbInformation Or vbOKCancel, "確認")
  If lngR = vbCancel Then
    Exit Sub
  End If
  
  On Error Resume Next
  Application.ScreenUpdating = False
  'ダミーアクセスでブックの状態をチェック
  Dummy = Workbooks(DatBookName).Sheet1.Range("A1").Value
  If Err.Number > 0 Then
    'エラー発生ならブックを開く
    Err.Clear
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DatBookName
    If Err.Number > 0 Then
      strMes = "参照先ブック[ " & DatBookName & " ]が見つかりません"
      GoTo ErrorHandler
    End If
  End If
  Err.Clear
  Set WB = Workbooks(DatBookName)
  
  'データ範囲の名前をチェック
  Set rngDat = WB.Names("データ").RefersToRange
  If Err.Number > 0 Then
    strMes = "参照先ブック[ " & DatBookName & " ]に名前[ " & DatRangName _
         & " ]の定義がありません"
    GoTo ErrorHandler
  End If
  Err.Clear

  'ダミーアクセスで作業用シートチェック
  Dummy = ThisWorkbook.Sheets(sTempShName).Range("A1").Value
  If Err.Number > 0 Then
    'エラー発生なら作業用シート追加
    Err.Clear
    With ThisWorkbook.Sheets.Add(Before:=Sheet1)
      .Name = sTempShName
    End With
  End If
  On Error GoTo 0
  Set SH = ThisWorkbook.Sheets(sTempShName)
  SH.Visible = xlSheetVisible

  '初期化
  SH.Cells.Clear
  lngR = 1

  '参照先ブックのデータにリンク、、と思ったけど
  'やはり値を転記させた方が良いかと。
  For Each rngCel In rngDat
    If Not IsEmpty(rngCel.Value) Then
      'SH.Cells(lngR, 1).Formula = _
      '  "=[" & DatBookName & "]" & _
      '  rngCel.Parent.Name & "!" & rngCel.Address
      SH.Cells(lngR, 1).Value = rngCel.Text
      lngR = lngR + 1
    End If
  Next rngCel
  
  'リストをソート
  SH.Columns("A:A").Sort Key1:=SH.Range("A1"), Order1:=xlAscending, Header:=xlGuess
  '作業用シートをVeryHidden で隠す
  'ユーザーに再表示させたくないなら xlVeryHidden で
  SH.Visible = xlHidden
  
  'リンクされたセルのアドレス取得
  sAddress = SH.Range(SH.Cells(1, 1), SH.Cells(lngR - 1, 1)).Address

  '入力規則を設定するシートのA列に入力規則を設定
  With ThisWorkbook.Sheets(sTargetName).Columns(1).Validation
    .Delete
    .Add Type:=xlValidateList, _
       Formula1:="=INDIRECT(""" & sTempShName & "!" & sAddress & """)"
    .IgnoreBlank = True  '空白値の入力を許可
    .InCellDropdown = True 'ドロップダウンリスト表示
  End With

Terminate:
  Set rngCel = Nothing
  Set rngDat = Nothing
  Set SH = Nothing
  On Error Resume Next
    '参照先ブックを自動的に閉じないのであれば
    '次行をコメントアウト
    WB.Close
  On Error GoTo 0
  Set WB = Nothing
  Exit Sub
ErrorHandler:
  Application.ScreenUpdating = True
  MsgBox strMes, vbCritical
  GoTo Terminate
End Sub
  • 回答No.4

ベストアンサー率 28% (4489/15980)

#2です。#2の補足などについて。
値リストの値について、VBAでBook2の対象箇所を読んで、カンマで区切って、リスト
文字列を作ってFormula1にセットすれば、できますが、
(1)操作する人が、利用する場合に20程度のアイテム数を超えると、スクロールが発生し、使い勝手が悪くなります。
(2)VBAは(イベントプロシージュアー式にしないと)そのプログラムを実行した時の状態で固定されてしまう。変更のつど実行も面倒。
まあエクセルを開いた都度更新(#3のご回答?)ぐらいで我慢できるかどうかですが。
(3)(1)のことを考えると、リストに出す「アイテムを多段式に絞る」
(県名を指定すると、その県内だけの市区町村が出るような)ことが求められますが、これはエクセルでも、範囲を使った方法が2度ほど挙がっていますが複雑です。
この辺の問題の解決は、プロがSQLを使って、納入ソフトに組み入れる分野だと思う。少なくともアクセスVBAでやるのが、技巧に走らなくてできる
方向だと思います。
  • 回答No.3

ベストアンサー率 62% (785/1258)

こんにちは。KenKen_SP です。

下記の VBA コードで一応ですが、実現できました。

しかし、コードのコメントにも書いておきましたが、入力規則のリストで
セル範囲を指定するのではなく、値指定できる長さには制限があるみたい
です。あまり長いリストは設定できません。

ですから、”一応”になってしまうのですが、、、

例外処理もいくつか含めておきましたので、参考にはなるかもしれません。

下記コードを標準モジュールにコピー&ペーストして下さい。


Sub Auto_Open()

  Call SetValidation

End Sub

Sub SetValidation()

  Dim Buf   As Variant
  Dim WB    As Workbook
  Dim aryDat() As String
  Dim strDat  As String
  Dim strMes  As String
  Dim i    As Long

  Const DatBookName As String = "Book1.xls"
  Const DatRangName As String = "データ"
    
  On Error Resume Next
  'ダミーアクセスでブックの状態をチェック
  Workbooks(DatBookName).Activate
  If Err.Number > 0 Then
    'エラー発生ならブックを開く
    Err.Clear
    Application.ScreenUpdating = False
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DatBookName
    If Err.Number > 0 Then
      strMes = "参照先ブック:" & DatBookName & "が見つかりません"
      GoTo ErrorHandler
    End If
  End If
  Err.Clear
  
  '名前「データ」の値を配列にバッファ
  Set WB = Workbooks(DatBookName)
  Buf = WB.Names("データ").RefersToRange.Value
  If Err.Number > 0 Then
    strMes = "名前:" & DatRangName & " が定義されてません"
    GoTo ErrorHandler
  End If
  WB.Close
  On Error GoTo 0
  
  '一次元配列に格納し直して、さらに文字列で連結(汗)
  ReDim aryDat(UBound(Buf) - 1)
  For i = 0 To UBound(Buf) - 1
    aryDat(i) = Buf(i + 1, 1)
  Next i
  strDat = Join$(aryDat, ",")
  
  'シート1のA列に入力規則を設定
  With ThisWorkbook.Sheets("Sheet1").Columns(1).Validation
    .Delete
    '次行でリストを設定するのですが、、
    '配列を受け付けないので、カンマ区切りの文字列で設定しています。
    'リストが大きすぎるとエラーになりますので注意。
    .Add Type:=xlValidateList, Formula1:=strDat
    .IgnoreBlank = True  '空白値の入力を許可
    .InCellDropdown = True 'ドロップダウンリスト表示
  End With

Terminate:
  Set WB = Nothing
  Exit Sub
ErrorHandler:
  Application.ScreenUpdating = True
  MsgBox strMes, vbCritical
  GoTo Terminate
End Sub
補足コメント
gogogo000

お礼率 40% (10/25)

KenKen_SP様;
おはようございます。ご丁寧なご回答をいただきありがとうございます。
やはりVBAを使わなければ無理ですか…。
今回,元の値となるデータは,13列分,1列あたり50~60種類,1リストは短くても10文字程度から最長で80文字程度の文字列です。このデータを100個ほどのExcelファイル(Book)で使いたく質問させていただきました。
またデータ(リスト)は頻繁に更新されます。
自分がやろうとしていることは,ExcelではなくAccessでやるべきことなのでしょうか!?
投稿日時:2005/09/25 08:14
  • 回答No.2

ベストアンサー率 28% (4489/15980)

このニーズは
・別ブックのデータがあり、そのデータ範囲の増減を即時反映したい。
・当ブックへコピーするのが面倒
・他ブックのリストデータが行数が多い。
などのために、他ブックのデータを使いたいのでしょうか
最初のケースだと、更なる難題を抱え込む。
ーーー
入力規則のリストの許容状況を総括すると
・入力規則は他シートのセル範囲を番地では指定できない。
・ただし名前を定義して、名前を指定すればできる
・名前の定義はブックレベルの名前定義が標準で、シートレベルの名前定義もできる。
・ということは通常では、当ブックを超えた他ブックの名前を参照できない。
・あとは、他ブックのセルを、当ブック(別シート)にリンクさせて、当ブックに値が存在するかのようにして、そこに名前をつけて参照できるかどうかです。
適当例がすぐ作りにくいので、質問者でやってみてください。
・VBAで何かよい方法はないか考えると
Sub Macro3()
Range("A1:A11").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=範囲1"
End With
End Sub
の xlBetween, Formula1:="=範囲1"
の部分の指定で、ウルトラC技がないかどうかだが、なさそう。
(注、値指定の際はFormula1:="a,s,d,f"のようにする。)
ご参考になれば幸いです。
補足コメント
gogogo000

お礼率 40% (10/25)

imogasi様;
ご回答ありがとうございます。
今回の質問のニーズは貴殿がご指摘なさる「他ブックのリストデータが行数が多い」から…です。
行数もさることながら,ひとつのSheetに7~8列,それぞれ別のデータを参照しなければなりません。
また「そのデータ範囲の増減を即時反映したい」のも理由のひとつです。データを参照しつつも適宜新しいデータが発生しますので,データは増える一方です。
投稿日時:2005/09/24 12:35
  • 回答No.1
例えば,Book2 の
B列1~10行 に

=[Book1.xls]Sheet1!A1
=[Book1.xls]Sheet1!A2
=[Book1.xls]Sheet1!A3
=[Book1.xls]Sheet1!A4
=[Book1.xls]Sheet1!A5
=[Book1.xls]Sheet1!A6
=[Book1.xls]Sheet1!A7
=[Book1.xls]Sheet1!A8
=[Book1.xls]Sheet1!A9
=[Book1.xls]Sheet1!A10

と書いておいて,
Book2 のA列1行の入力規制のリストの元の範囲を

=$B$1:$B$10

とすれば,できることはできますが,入力規制自身の範囲に

=[Book1.xls]Sheet1!$A$1:[Book1.xls]Sheet1!$A$10

のように別のBookを指定するのはできないみたいです。
結果を報告する
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。
AIエージェント「あい」

こんにちは。AIエージェントの「あい」です。
あなたの悩みに、OKWAVE 3,600万件のQ&Aを分析して最適な回答をご提案します。

関連するQ&A

その他の関連するQ&Aをキーワードで探す

ピックアップ

ページ先頭へ