Excelで質問です。ListBoxの項目を別bookから読込むには

質問

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

回答No.5

myRange · ベストアンサー

●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
'------------------------------------------------------

以上です。

お礼

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

回答No.4

Wendy02

>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

お礼

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

回答No.3

mazide_ktkr

とりあえず説明つきで簡単に書きますね＾＾
詳細があればいつでも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

お礼

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

回答No.2

mitsuru714

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

処理上、どうしてもファイル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

お礼

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

回答No.1

mazide_ktkr

ＶＢＡが大好き社会人です笑

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

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

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

私にまかせて下さい！！