人に聞けない痔の悩み、これでスッキリ >>

Excelで質問です。ListBoxの項目を別bookから読込むには
VBA初心者です。
book-AのListBox1からbook-Bのセルの項目を読み込むことは可能なのでしょうか。
Excel2003です。

book-AのUserForm1に下記のコードを実行しても構文エラーになってしまいます。
book-Aとbook-Bは同じフォルダに入っています。出来ればbook-Bを閉じた状態で実行できれば良いのですが

Private Sub UserForm_Initialize()
ListBox1.RowSource =[book-B.xls]Sheet1!A1:A10
End Sub

このQ&Aに関連する最新のQ&A

A 回答 (5件)

●Book-Bを開かないで処理


'-------------------------------------------------
Private Sub UserForm_Initialize()
 Dim i As Long
 Dim myVal
 Dim myPath As String

myPath = "'" & ThisWorkbook.Path & "\[Book-B.xls]Sheet1'!"

 For i = 1 To 10
  myVal = ExecuteExcel4Macro(myPath & "R" & i & "C1")
  ListBox1.AddItem myVal
 Next i
End Sub
'--------------------------------------------------

●Book-Bを開くが非表示にして処理
'--------------------------------------------------
Private Sub UserForm_Initialize()
 Dim BK2 As Workbook
 Set BK2 = Workbooks.Open(ThisWorkbook.Path & "\Book-B.xls")
 ActiveWindow.Visible = False '●ブック非表示
 ListBox1.List = BK2.Sheets("Sheet1").Range("A1:A10").Value
 BK2.Close False
End Sub
'------------------------------------------------------

以上です。
    • good
    • 1
この回答へのお礼

早速の回答ありがとうございます。
望んだ通りの結果が得られました。
本当にありがとうございました。感謝いたします。

お礼日時:2010/10/07 17:42

>book-Aとbook-Bは同じフォルダに入っています。

出来ればbook-Bを閉じた状態で実行できれば良いのですが
そういうふうに言われると、裏技的になりますね。

>ListBox1.RowSource =[book-B.xls]Sheet1!A1:A10
RowSource っていうことはないのですが、ExecuteExcel4Macro で配列で取る方法はあっても、配列では出力しないのです。以下は、ROWSを使っているように、縦にデータがあります。

'//
Private Sub UserForm_Initialize()
Dim myFormula As String
Dim rowCount As Long
Dim i As Long, j As Long
Dim Ar() As Variant
'パス名(フルパスで)
Const myPath As String = "'C:\My Documents\[book-B.xls]Sheet1'!$I$1:$I$16"
'R1C1に変更(上記の数式が、R1C1なら以下は不要)
myFormula = Application.ConvertFormula(myPath, xlA1, xlR1C1, xlAbsolute)
rowCount = Application.ExecuteExcel4Macro("ROWS(" & myFormula & ")")
ReDim Ar(rowCount - 1)
For i = 1 To rowCount
 Ar(j) = Application.ExecuteExcel4Macro("INDEX(" & myFormula & "," & i & ",0 )")
 j = j + 1
Next
ListBox1.List = Ar
End Sub
    • good
    • 0
この回答へのお礼

回答ありがとうございます。
早速実行してみたのですが、うまく働かないようです。
コードは完璧でも私が未熟でうまく生かせない可能性が大きいです。
大変申し訳ありません。これからこのコードを理解できるように努力したいと思います。
ありがとうございました。

お礼日時:2010/10/07 17:52

とりあえず説明つきで簡単に書きますね^^


詳細があればいつでもPG変更して載せますよ♪♪
頑張れーーーー♪

Dim A_book As String
Dim B_book As String
Dim tmp As String

'現在アクティブなbook名を取得します
A_book = ActiveWorkbook.Name

'Workbooks.Open でファイル名を指定してbookを開きます
'ActiveWorkbook.Path は現在実行中のbookのディレクトリを意味します
Workbooks.Open Filename:=ActiveWorkbook.Path & "\book-B.xls"

B_book = ActiveWorkbook.Name '

'bookを開くとそこがアクティブになるのでbook-B.xlsの"A1"の値を取得します
tmp = Range("A1").Value

'bookを閉じます
Windows(B_book).Close

'コピー完了です
Range("A1").Value = tmp
    • good
    • 0
この回答へのお礼

回答ありがとうございます。
早速実行してみたのですが、うまく働かないようです。
コードは完璧でも私が未熟でうまく生かせない可能性が大きいです。
大変申し訳ありません。これからこのコードを理解できるように努力したいと思います。
ありがとうございました。

お礼日時:2010/10/07 17:53

下記で、対応可能と思います。



処理上、どうしてもファイルBを開く必要がありますが
裏で処理をさせれば、ユーザが意識する事は有りません。

いかがでしょうか?
コード内の★の部分を変更すれば、
他の条件でも対応可能です。



Dim B_FileName
Dim B_FILE_PATH

Private Sub UserForm_Initialize()
Dim xlApp
Dim xlA_Book
Dim xlB_Book
Dim xlA_Sheet
Dim xlB_Sheet

Dim arrTmp As Variant
Dim StrVal As String
Dim FormList As Variant
Dim lngI As Long

'==========================================
B_FileName = "\book-B.xls"    '★
B_FILE_PATH = ThisWorkbook.Path & B_FileName
'==========================================

Set xlApp = Excel.Application
Set xlA_Book = ThisWorkbook 'Workbook
Set xlA_Sheet = xlA_Book.ActiveSheet 'Worksheet

Workbooks.Open B_FILE_PATH
Set xlB_Book = ActiveWorkbook
Set xlB_Sheet = xlB_Book.Worksheets("Sheet1") 'Worksheet
ActiveWindow.WindowState = xlMinimized

'リスト情報を取得する
With xlB_Sheet
arrTmp = .Range(.Cells(1, 1), .Cells(10, 1))  '★
End With

'FileBを閉じる
xlApp.DisplayAlerts = False
xlB_Book.Close
xlApp.DisplayAlerts = True

' 'リストに表示するために置き換える
ReDim FormList(0)
For lngI = 1 To UBound(arrTmp, 1)
StrVal = arrTmp(lngI, 1) 'セル内の文字列を格納する
If StrVal <> "" Then '空白はリストに表示しない
FormList(UBound(FormList)) = StrVal
ReDim Preserve FormList(UBound(FormList) + 1) '配列を一つ増やす
End If
Next

'FileAをアクティブにする
xlA_Sheet.Activate

ListBox1.List = FormList

End Sub
    • good
    • 0
この回答へのお礼

長文による早速の回答ありがとうございます。
ListBox1に項目が表示されました。しかし、UserForm1を表示したと同時に、ウインドウが縮小される
現象が起こるようです。

お礼日時:2010/10/07 17:58

VBAが大好き社会人です笑



book-Bのセルの値をbook-Aのセルに持ってきたいって事でいいですよね?

すぐにPGを作成してあげるので返答よろしくです。

詳しく何をどうしたいか説明してくれれば全部作ってサンプルPGを載せますよ^^

私にまかせて下さい!!
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QVBAでシートからコンボボックスにデータを設定する方法

VBAにてフォーム起動時にシート内に設定した
値をコンボボックスに取り込みたいのですが・・。
たとえばA列に連続で入力されているデータを
取り込むなど・・。
設定データ数は動的に変化します。

Aベストアンサー

もうほとんど同じですが…

Private Sub UserForm_Initialize()
Dim i As Integer

 ComboBox1.Clear
 For i = 1 To Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  ComboBox1.AddItem Worksheets("sheet1").Cells(i, 1).Value
 Next
End Sub

QEXCELで他のファイルデータをドロップダウンさせるには?

EXCELでファイルAのデータをファイルBのセルでドロップダウンさせるにはどうしたらよいでしょうか?

例えば、ファイルAには受注データが入力されていて、受注品目のセル範囲を指定したファイルデータを利用して、ファイルBにドロップダウンによるリストの入力をしたいのですが教えてください。

ブック内での他シートからのリスト作成はわかるのですが・・・

Aベストアンサー

こんにちは。

以下、的外れだった場合はすみません。

「名前」の定義を利用すれば可能だと思います。

以下は設定の一例です。
※下記は、Excel2000での操作例です。他のバージョンのExcelで操作が
 異なる場合は、同機能の操作に置き換えて下さい。

■Excelブックの構成
 各Excelブックが下記の構成だったとします。

1)ExcelブックA
  ・データが参照される側のExcelブック
  ・ファイル名は、"BookA.xls" とします。

 <シート1の構成>
  シート名="Sheet1"

       A
  1  品目名
  2  かぼちゃ
  3  じゃがいも
  4  とうもろこし
  5  だいこん
  6  にんじん

2)ExcelブックB
  ・データを参照する側のExcelブック
  ・ファイル名は、"BookB.xls" とします。

 <シート1の構成>
  シート名="Sheet1"

       A
  1  品目選択
  2  XXXXX  ←このセルに下記の[入力規則]の設定を行います。

■名前の定義、入力規則の設定
 上記のExcelブックの構成で、ドロップダウンリスト(=入力規則)を設けたい
 ブック側(今回の場合は、"BookB.xls")で、下記のような<名前の定義>
 及び、<入力規則の設定>を行います。

 注)下記の設定は、リストデータを参照する側のExcelブックに設定します。

 <名前の定義>
 1)メニュー : [挿入] → [名前] → [定義]
 2)「名前の定義」のダイアログ操作
    名前    : 品目リスト  ←ここで任意の名前を入力します。
    参照範囲 : =[BookA.xls]Sheet1!$A$2:$A$6

    ※「名前」には、任意の名前を入力します。
    ※「参照範囲」には、"="に続けて参照する他ブックのセル範囲を指定します。
      上記の場合は、
        Excelブック : BookA.xls
        シート名  : Sheet1
        セル範囲  : $A$2:$A$6 (絶対参照のため、"$"を付けています)
      の指定となります。
 3)2)の操作後、[追加]ボタン(または[OK]ボタン)をクリックして「名前」の
  登録を行います。

 <入力規則の設定>
 1)入力規則を設定するセル(今回の場合は、A2セル)を選択します。
 2)メニュー : [データ] → [入力規則]
 3)「データの入力規則」のダイアログ操作
  ・「設定」タブの操作
    入力値の種類 : リスト
    元の値      : =品目リスト  ←ここで名前を指定します。
    ドロップダウンリストから選択する : チェックマークをONにする

    ※「元の値」には、"="に続けて、上記の<名前の定義>で設定した
     「名前」を入力します。
 4)3)の操作後、[OK]ボタンをクリックします。

上記の設定後、2つのExcelブック("BookA.xls"、"BookB,xls")を開いておき、
"BookB.xls"のシート"Sheet1"のセル"A2"をクリックすれば、リストデータ
("BookA.xls"のシート"Sheet1"のセル範囲"A2:A6")が、ドロップダウンで
表示されると思います。
注)データを参照する側のブック("BookB.xls")のみ開いただけでは、ドロップ
  ダウンリストは表示されません。

※添付画像は、上記設定を行ったExcelブックのキャプチャ画像です。

なお、上記の方法は下記サイトの内容を参考にさせて頂きました。

■参考サイト
Excel ドロップダウンリストの設定方法 別ブック参照の場合
http://plaza.rakuten.co.jp/pasodairy/diary/200506030000/

以上です。参考になれば幸いです。

こんにちは。

以下、的外れだった場合はすみません。

「名前」の定義を利用すれば可能だと思います。

以下は設定の一例です。
※下記は、Excel2000での操作例です。他のバージョンのExcelで操作が
 異なる場合は、同機能の操作に置き換えて下さい。

■Excelブックの構成
 各Excelブックが下記の構成だったとします。

1)ExcelブックA
  ・データが参照される側のExcelブック
  ・ファイル名は、"BookA.xls" とします。

 <シート1の構成>
  シート名="Sheet1"

      ...続きを読む

Qリストボックスの項目に、他のブックのセルの値を設定するには?

EXECL2003でユーザーフォームを作成しているのですが困っています。

リストボックス(またはコンボボックス)の項目の設定に、他のブックのセルの値を使用したいのですがわかりません。

具体的には「単価表.xls」「見積書.xls」と2つのブックがあり、「見積書.xls」にユーザーフォームを作成します。
そのユーザーフォームのリストボックスの項目に「単価表.xls」のセルの値を設定したいのです。

本を見たりWEBを検索しても、ブック内のシートのセルの値を設定する例しか見つけることができません。

何か手がかりでも教えていただければ助かります。
よろしくお願いいたします。

Aベストアンサー

別のブックでも以下のように参照が可能です。
 UserForm1.ListBox1.AddItem Workbooks("単価表.xls").Sheets("単価").Cells(i, 1).Value

ただし単価表.xlsが開いていなければなりませんので、単価表.xlsが開いていないことも想定すると次のようなマクロになるかもしれません
(「単価表.xls」「見積書.xls」は同じフォルダにあると想定)

Sub Macro3()
Dim wb As Workbook
Dim sw As Boolean
Dim i As Integer
For Each wb In Workbooks '単価表.xlsが開いているかチェック
 If wb.Name = "単価表.xls" Then
  sw = True
  Exit For
 End If
Next wb

If sw = False Then '開いていないときは開く
 Workbooks.Open Filename:="単価表.xls"
End If

UserForm1.ListBox1.Clear
For i = 1 To 10 'リストボックス項目を追加
 UserForm1.ListBox1.AddItem Workbooks("Book2.xls").Sheets("Sheet1").Cells(i, 1).Value
Next i
UserForm1.Show
End Sub

別のブックでも以下のように参照が可能です。
 UserForm1.ListBox1.AddItem Workbooks("単価表.xls").Sheets("単価").Cells(i, 1).Value

ただし単価表.xlsが開いていなければなりませんので、単価表.xlsが開いていないことも想定すると次のようなマクロになるかもしれません
(「単価表.xls」「見積書.xls」は同じフォルダにあると想定)

Sub Macro3()
Dim wb As Workbook
Dim sw As Boolean
Dim i As Integer
For Each wb In Workbooks '単価表.xlsが開いているかチェック
 If wb.Name = "単価表...続きを読む

QEXCEL VBAマクロ作成で、他のEXCELからデータを取り込みたい

メインプログラム(EXCEL VBA)より、
他のフォルダーにあるEXCELの項目の内容を取り込みたいです。
たとえば他のフォルダーのEXCELのRange("A2:A3").ValueをメインプログラムのRange("C2:C3").Valueにセットしたい時です。

・コマンドボタン押したら、どこのEXCELから取り込むかのポップアップ(?)は、表示はできてます。
・作業者が選んだパスとブックもMsgBoxで表示できてるので、もらう相手の場所も取得できてます。

・となると次はOPEN,INPUTですか?
テキストデータの取り込みですと、Inputでそのバッファを定義してるのですが、なんか違うような。。。

よろしくお願いします!

Aベストアンサー

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Cells(2, 2).Value ' 相手シートの B2 の値を自分自身の A1 に書き込む

readBook.Close False ' 相手ブックを閉じる
Set readSheet = Nothing
Set readBook = Nothing

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Ce...続きを読む

Qexcelのリストボックスで選択した項目をアクティブセルに入力方法

もしかしたら既出かもしれませんが・・・
フォームコントロールのリストボックスで入力範囲の指定したリストをリンクするセルをワークシート上にクリックしたセルに入力させる方法はありますか?
もし、不可能でしたら、ActiveXコントロールのリストボックスでも構いません。よろしくお願いいたします。

Aベストアンサー

そのリストボックスの、コードの表示で
Private Sub ListBox1_Click()
ActiveCell = ListBox1.List(ListBox1.ListIndex)
End Sub
と入れるだけ。
コントロールツールボックスのコントロールを、ワークシートに直接
貼り付けた場合。
WEBや解説書で、ListboxとかListindexなど調べましたか。

QVBA コンボボックスで選んだ値を取得するには

ユーザーフォーム上のコンボボックスから値を選択し、その値を変数として使いたいのですが、うまくいきません。

コンボボックスのコードで
Private Sub ComboBox1_Change()
moji1 = ComboBox1.Text
Range("A1").Value = moji1
のようにすれば、コンボボックスから値を選んだ時点でA1セルにその値をコピーできるのですが、同じユーザーフォーム上にあるコマンドボタンをクリックして実行する「マクロ1」にてこのmoji1という変数を使いたいのです。

マクロ1にて、上記と同じ
Range("A1").Value = moji1
というコードを記述しても、ユーザーフォームで選択した値が消えており、empty値となってしまいます。

原因をご存知の方はお教えください。

Aベストアンサー

原因については下記を参考にしてください。
http://pc.nikkeibp.co.jp/pc21/special/2007_gosa/eg5.shtml

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Qエクセル VBA ユーザーフォームを閉じる

ユーザーフォームを開く時は
UserForm1.Showですが
閉じる時は?
UserForm1.Close
だとコンパイルエラーになります。
End
にするしかないですか?

Aベストアンサー

Unload Me とか Unload UserForm1 でユーザーフォームを閉じることができます。

Qエクセル リストボックスにセルの値を表示したい

Sheet1に
A1 a
A2 i
A3 u
と入力されています。

フォームのリストボックスにこの3つのセルを表示したいのですが
Private Sub UserForm_Initialize()
UserForm1.ListBox1.ControlSource = Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 3))
End Sub
ではうまく行きません。

デバッグしてもなにも引っかからないのですが
実行すると
「実行時エラー13
型が一致しません」
となります。

「ControlSource」を「RowSource」に変えても同じでした。
※列が移動するため列番号を変数で取得したいのでCellsを使用しています。

ご教授よろしくお願いします。

Aベストアンサー

回答4、onlyromです。
大事なことを言い忘れ。。

ROWSOURCEプロパティを使うときは、リストに使う値がアクティブシートにない場合は、シート名を付けないとエラーになります。
ということで改めて回答です。


R = 5

UserForm1.ListBox1.RowSource = "Sheet1!A1:A" & R

UserForm1.ListBox1.RowSource = "Sheet1!" & Range(Cells(1, 1), Cells(R, 1)).Address

UserForm1.ListBox1.List = Sheets("Sheet1").Range("A1:A" & R).Value

UserForm1.ListBox1.List = Sheets("Sheet1").Range(Cells(1, 1), Cells(R, 1)).Value
 
このどれかを使えますが、セルの値を使うときは、Listプロパティをお勧めします。
 


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング