VBAコードでシート名を取得したい。

質問

下記のコードは、指定したExcelブックにある情報をVBAコードを設置したブックに
情報を取得するコードになります。

質問なんですが・・・指定したExcelブックは、予めSheet名までをVBAコードで指定し、
そのSheetの内容を取得していますが、この部分を指定したSheet名ではなく、
Sheet名を取得し、取得したSheet名から自分で選択したSheetを選んび、
そのSheetの内容を取得する様に変えたいと考えています。

取得する側のSheet名が一定の名前になっていない為、
今までSheet名を変えて情報を取得する様にしていたのを
変えずに情報を取得したいと思ったからなんですが・・・

どの様に変えればできるのか初心者のためよろしくお願いします。

できましたら・・・UserForm2を使ってComboboxに取得するSheet名を
一覧化し、そのComboboxからSheet名を選べる様にしたいと思います。

-----------------------------------------------------------------------------------

Public Day As String '全プロシージャーで有効な変数

Sub CommandButton1()
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") = 7 Then Exit Sub

Dim LstWb As Workbook
Dim LstWs As Worksheet
Dim OutWs As Worksheet
Dim LstDt As Variant
Dim EndRow As Long
Dim Day0 As String
Dim Day1 As String
Dim i As Long
Dim j As Integer
Dim k As Long

Set LstWb = Workbooks.Open(ThisWorkbook.Path & "\テストファイル.xlsm")
Set OutWs = ThisWorkbook.Sheets("Sheet1")
Set LstWs = LstWb.Sheets("Sheet1")
EndRow = LstWs.Cells(Rows.Count, 1).End(xlUp).Row
With LstWs
LstDt = .Range(.Cells(1, 1), .Cells(EndRow, 5))
End With
LstWb.Close
Set LstWb = Nothing
Set LstWs = Nothing

Load UserForm2
With UserForm2
Day0 = LstDt(2, 4)
.ComboBox1.AddItem Day0
For i = 2 To EndRow
With .ComboBox1
Day0 = .List(.ListCount - 1)
Day1 = LstDt(i, 4)
If Day0 <> Day1 Then
.AddItem Day1
End If
End With
Next i
.ComboBox1.Value = .ComboBox1.List(0)
End With
UserForm2.Show
For i = 1 To 5
OutWs.Cells(1, i).Value = LstDt(1, i)
Next i
k = 2
For i = 2 To EndRow
Day0 = LstDt(i, 4)
If Day = Day0 Then
For j = 1 To 5
OutWs.Cells(k, j).Value = LstDt(i, j)
Next j
k = k + 1
End If
Next i
Set OutWs = Nothing
LstDt = Empty
UserForm1.Show (vbModeless)End Sub

回答No.8

piroin654 · ベストアンサー

strSheetNameは標準モジュールに宣言しているので
UserForm1のCommandButton2に宣言する必要はありません。
If strSheetName = "" Then以下の文の位置の変更。
以下です。

※まず標準モジュールに・・・
Public Day As String
Public strSheetName As String

Sub CommandButton1()
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") = 7 Then Exit Sub

　　If strSheetName = "" Then
　　MsgBox "シートが選択されていません"
　　UserForm1.Show (vbModeless)
　　Exit Sub
　　End If

Dim LstWb As Workbook
Dim LstWs As Worksheet
Dim OutWs As Worksheet
Dim LstDt As Variant
Dim EndRow As Long
Dim Day0 As String
Dim Day1 As String
Dim i As Long
Dim j As Integer
Dim k As Long
UserForm1.Hide

Set LstWb = Workbooks.Open(ThisWorkbook.Path & "\テストファイル.xlsm")
Set OutWs = ThisWorkbook.Sheets("Sheet1")
Set LstWs = LstWb.Sheets(strSheetName)

　　EndRow = LstWs.Cells(Rows.Count, 1).End(xlUp).Row
With LstWs
LstDt = .Range(.Cells(1, 1), .Cells(EndRow, 5))
End With
LstWb.Close
Set LstWb = Nothing
Set LstWs = Nothing

　　Load UserForm2
With UserForm2
Day0 = LstDt(2, 4)
.ComboBox1.AddItem Day0
For i = 2 To EndRow
With .ComboBox1
Day0 = .List(.ListCount - 1)
Day1 = LstDt(i, 4)
If Day0 <> Day1 Then
.AddItem Day1
End If
End With
Next i
.ComboBox1.Value = .ComboBox1.List(0)
End With
UserForm2.Show 'modelessでは上手く動きませんので
For i = 1 To 5
OutWs.Cells(2, i).Value = LstDt(1, i)
Next i
k = 2
For i = 2 To EndRow
Day0 = LstDt(i, 4)
If Day = Day0 Then
For j = 1 To 5
OutWs.Cells(k, j).Value = LstDt(i, j)
Next j
k = k + 1
End If
Next i
Set OutWs = Nothing
LstDt = Empty

MsgBox "取り込み完了です。" & vbCrLf & "" & vbCrLf & "良ければ『OK』をクリックして下さい。"
UserForm1.Show (vbModeless)
End Sub

※UserForm1のCombobox1には、標準モジュールを実行
Application.OnTime Now, "CommandButton1"

※UserForm1のCombobox2には、
Private Sub CommandButton2_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Integer

strFileName = "テストファイル.xlsm"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing
End Sub

※UserForm1のCombobox1には
Private Sub ComboBox1_Change()
strSheetName = ComboBox1.Text
End Sub

これで確認してみてください。

補足

早速～変更しました。希望した動作を実行することが出来ました。
ありがとうございます。
それと質問なんですが・・・テストファイルのシート名をコンボボックスに表示させたときに、同じ動作を、またした場合に取得したシート名がダブってコンボボックスに表示されるんですが、タブって表示されないようにするには、どうしら良いでしょうか？

回答No.12

piroin654

訂正です。

フォームを開くときに、

Private Sub UserForm_Initialize()
Me!CommandButton2.Enabled = False
Me!CommandButton3.Enabled = False
End Sub

次に、UserForm1の各ボタンのクリック時の
イベントを以下のように。

Private Sub CommandButton1_Click()
Me!TextBox1 = "マクロを実行しますか"
Me!CommandButton2.Enabled = True
Me!CommandButton3.Enabled = True
End Sub

Private Sub CommandButton2_Click()
Me!CommandButton2.Enabled = False
Me!CommandButton3.Enabled = False
Me!TextBox1 = "メールデータを取込みます。よろしいですか？"
'マクロの呼び出し
Call CommandButton
End Sub

Private Sub CommandButton3_Click()
Me!CommandButton2.Enabled = False
Me!CommandButton3.Enabled = False
Me!TextBox1 = "マクロを実行を中止します"
End Sub

としてください。ボタンの番号が間違っていました。

お礼

ご回答ありがとうございます。
早速～マクロを実行させてみました。きちんと希望した動作ができるようになりました。基本的にマクロを実行する時の選択ですので、助かりました。

回答No.11

piroin654

遅くなりました。

UserForm1にテキストボックスを一つ、
ボタンを三つ設定します。
テキストボックスはコメントの表示用
なので、既にある場合は実際に合わせて
変更してください。また、ボタンの名前も
実際に合わせて変更してください。
一応

TextBox1
CommandButton1　　Captionを「実行」
CommandButton2　　Captionを「Yes」
CommandButton3　　Captionを「No」

とします。

フォームを開くときに、

Private Sub UserForm_Initialize()
Me!CommandButton1.Enabled = False
Me!CommandButton2.Enabled = False
End Sub

次に、UserForm1の各ボタンのクリック時の
イベントを以下のように。

Private Sub CommandButton1_Click()
Me!TextBox1 = "マクロを実行しますか"
Me!CommandButton1.Enabled = True
Me!CommandButton2.Enabled = True
End Sub

Private Sub CommandButton2_Click()
Me!CommandButton1.Enabled = False
Me!CommandButton2.Enabled = False
Me!TextBox1 = "メールデータを取込みます。よろしいですか？"
'マクロの呼び出し
Call CommandButton
End Sub

Private Sub CommandButton3_Click()
Me!CommandButton1.Enabled = False
Me!CommandButton2.Enabled = False
Me!TextBox1 = "マクロを実行を中止します"
End Sub

次に、標準モジュールのマクロの先頭部分で、

Sub CommandButton1()

　　If strSheetName = "" Then
　　MsgBox "シートが選択されていません"
　　UserForm1.Show (vbModeless)
　　Exit Sub
　　End If

Dim LstWb As Workbook
Dim LstWs As Worksheet
Dim OutWs As Worksheet
Dim LstDt As Variant
　　以下省略

のように、
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") = 7 Then Exit Sub

を取り除きます。

次に、
※UserForm1のCombobox1には、標準モジュールを実行
Application.OnTime Now, "CommandButton1"

のところを停止しておく。

以上のような方法で、たぶん意図したようになると思います。

なお、この方法ではマクロのCommandButton1を
実行することにしか対応、していないので、
設定たコマンドボタンを使いまわすには
さらに工夫が必要になります。

回答No.10

piroin654

メッセージボックスで出来ないことを
したいというのであれば、可能なこと
であれば、出来る、ということになります。

メッセージボックスは便利なのでなるべくなら
使いこなすようにして、その上でどうしても、
ということならUserFormを使うことも
いいのでは、と思います。

どのようなことがしたいのですか？

補足

メッセージボックスを使わずUserFormをメッセジボックスと同じ動作ですが、
詳しい詳細が、今回、質問したマクロですが、UserForm1が操作パネルとして作成しております。
このUserForm1にテキストボックスを作成し作成したテキストボックスを、
マクロ動作の確認コメントを表示させる様にしました。
例えば・・・以前の回答にありました・・・
>1　マクロの開始
>2　他のブックの特定のシートからデータを取得
>3　UserForm2を開き取得したデータを元に処理
>4　UserForm1を開く
>5　マクロの終了
上記の “1 マクロ開始”の時にテキストボックスに「処理を実行します！」と
コメントが出るようにし、
また、“2 他のブックの特定にシートからデータを処理”の時に「データを抽出します！」とテキストボックスにコメントが出るように作成をしました。

この時に、MsgBox""を使う事も考えたんですが、
このコメントを疑問文で作成すると必ず「OKかNG」また「YESかNO」の選択が必要となります。

この時に、UserFormに設置したコマンドボタンで「YESかNO」の選択をし
次の動作を実行させる様にできればと考えました。

分かりづらい説明かもしれませんが、こんな感じでしたいと思っています。

回答No.9

piroin654

状況によっていろいろな対応をしますが、
この場合は、以下のところで

※UserForm1のCombobox2には、
Private Sub CommandButton2_Click()
Me.ComboBox1.Clear
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Integer

strFileName = "テストファイル.xlsm"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing
End Sub

のように
Me.ComboBox1.Clear
をいれて、まずコンボボックスの内容をクリアして、
それからコンボボックスに所定のデータを設定するように
すればいいのでは、と思います。
これで確認してみてください。

お礼

回答ありがとうございます。試して見ました。
動作前に一度、クリアをすることで以前のデータがクリアされる様に
すれば良かったんですね！
ありがとうございました。

補足

今回ので、質問しました内容が、完璧にでき上がりましたました。
他に別のコードで聞きたいことがありますが・・・
再度質問しなおした方がいいでしょうか？

内容にもよると思いますが・・・
メッセージボックスに関して何ですが・・・メッセージボックスを使わず
UserFormでメッセージボックスと同じようにUserFormで作成したいと
思っていますが、できますでしょうか？

回答No.7

piroin654

遅くなりました。
No1の捕捉で、
＞If ComboBox1.Text = "" Then ←の部分でエラーになってしまいます。
＞なんででしょうか？
とありますが、これはマクロ自体はUserFormに書かれたものでは
ないからです。

Application.OnTime Nowでの自動実行があったりするので、
一つの案ですが。

1）　標準モジュールに

Public strSheetName As String

　を設定。

2）　UserForm1を開くときに、変数の設定などの
　あとに、

　strSheetName = ""

　としてstrSheetNameの初期化をしておく。

3）　UserForm1にコマンドボタンとコンボボックス
　を設定し、
　コマンドボタンのクリックイベントに

Dim wkb As Workbook
Dim wks As Worksheet
Dim strFileName As String
Dim i As Integer

strFileName = "テストファイル.xls"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing

　を設定する。

　コンボボックスの変更イベントに

strSheetName = ComboBox1.Text

　を設定する。ComboBox1という名前は実際に
　合わせて変更して置いてください。

4）マクロ　Sub CommandButton1()　の中で、

Sub CommandButton1()
If strSheetName = "" Then
MsgBox "シートが選択されていません"
UserForm1.Show (vbModeless)
Exit Sub
End If
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") =

7 Then Exit Sub

とし、

Set LstWs = LstWb.Sheets("Sheet1")

を

Set LstWs = LstWb.Sheets(strSheetName)

とする。

というのはどうでしょう。

補足

ありがとうございます。一連のコードですが・・・こんな感じになります。
すいませんが間違っている所がありましたら教えてください。

※まず標準モジュールに・・・
Public Day As String
Public strSheetName As String

Sub CommandButton1()
If MsgBox(Space(6) & "メールデータを取込みます。よろしいですか？", vbYesNo, "継続確認") = 7 Then Exit Sub

Dim LstWb As Workbook
Dim LstWs As Worksheet
Dim OutWs As Worksheet
Dim LstDt As Variant
Dim EndRow As Long
Dim Day0 As String
Dim Day1 As String
Dim i As Long
Dim j As Integer
Dim k As Long
UserForm1.Hide

Set LstWb = Workbooks.Open(ThisWorkbook.Path & "\テストファイル.xlsm")
Set OutWs = ThisWorkbook.Sheets("Sheet1")
Set LstWs = LstWb.Sheets(strSheetName)

　　If strSheetName = "" Then
　　MsgBox "シートが選択されていません"
　　UserForm1.Show (vbModeless)
　　Exit Sub
　　End If

　　EndRow = LstWs.Cells(Rows.Count, 1).End(xlUp).Row
With LstWs
LstDt = .Range(.Cells(1, 1), .Cells(EndRow, 5))
End With
LstWb.Close
Set LstWb = Nothing
Set LstWs = Nothing

　　Load UserForm2
With UserForm2
Day0 = LstDt(2, 4)
.ComboBox1.AddItem Day0
For i = 2 To EndRow
With .ComboBox1
Day0 = .List(.ListCount - 1)
Day1 = LstDt(i, 4)
If Day0 <> Day1 Then
.AddItem Day1
End If
End With
Next i
.ComboBox1.Value = .ComboBox1.List(0)
End With
UserForm2.Show 'modelessでは上手く動きませんので
For i = 1 To 5
OutWs.Cells(2, i).Value = LstDt(1, i)
Next i
k = 2
For i = 2 To EndRow
Day0 = LstDt(i, 4)
If Day = Day0 Then
For j = 1 To 5
OutWs.Cells(k, j).Value = LstDt(i, j)
Next j
k = k + 1
End If
Next i
Set OutWs = Nothing
LstDt = Empty

MsgBox "取り込み完了です。" & vbCrLf & "" & vbCrLf & "良ければ『OK』をクリックして下さい。"
UserForm1.Show (vbModeless)
End Sub

※UserForm1のCombobox1には、標準モジュールを実行
Application.OnTime Now, "CommandButton1"

※UserForm1のCombobox2には、
Private Sub CommandButton2_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim strFileName As String
Dim i As Integer

strFileName = "テストファイル.xlsm"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing
End Sub

※UserForm1のCombobox1には
Private Sub ComboBox1_Change()
strSheetName = ComboBox1.Text
End Sub

回答No.6

piroin654

またまた夜中の回答になりますが。
コードの大まかな流れは、

1　マクロの開始
2　他のブックの特定のシートからデータを取得
3　UserForm2を開き取得したデータを元に処理
4　UserForm1を開く
5　マクロの終了

のような感じですが、最後に開くUserForm1の役割が
何も記載してないので、補足の

＞No.4で行ったようにUserform1にComboboxを設置し、
＞そこに取得したSheet名を表示させるようにでも構いません。

というのは、状況を複雑にしかねないので、確認ですが。

(1)
質問のマクロ
＞Sub CommandButton1()
は、実際にはどのUserFormでのイベントでしょうか。
あるいはマクロとして標準モジュールに登録して
あるものでしょうか。

(2)
マクロの最後にUserForm1を呼び出していますが、
UserForm1は何らかの役割をもっているので、
よびだされているものでしょうか。

補足

回答ありがとうございます。
大まかな流れは、その通りです。
1　マクロの開始←(UserForm1のCommandbutton1に記述)
2　他のブックの特定のシートからデータを取得←(テストファイル.xlsm)
3　UserForm2を開き取得したデータを元に処理
4　UserForm1を開く←(操作パネルの役割で使用)
5　マクロの終了

4番目のUserForm1の役割ですが、このUserForm1には、他のマクロの動作も
記述されています。その記述内容は、ブックを保存して閉じるとか非保存で閉じる
とかです。UserForm1はマクロの動作のコントロールパネル見たいな感じで作成してました。

(1)の回答ですが、Commandbutton1は標準モジュールに記述されていまして
Application.OnTime Now, "CommandButton1"でUserForm1から操作してます。
(2)の回答ですが 4番目の回答に含まれています。

お手数おかけしますがよろしくお願いします。

回答No.5

piroin654

No4です。
（質問のコードをサラーっと眺めただけなので)

質問のコード中にComboBox1は使われている
みたいなので、回答のコンボボックスは追加した
コンボボックスの名前に変更してください。

なお、質問のマクロはどこで実行されているのか
わからないし、

＞できましたら・・・UserForm2を使ってComboboxに取得するSheet名を
＞一覧化し、そのComboboxからSheet名を選べる様にしたいと思います。

ということで、UserForm1とUserForm2の関係もほとんど考慮していませんので
No4の回答の、ボタンのクリックイベントの中身だけは、つまり
シート名をコンボボックスにセットする方法はそのまま使えると
思います。
夜中の回答でかなりあやしいですが・・・。

補足

私が質問した内容で・・・
＞できましたら・・・UserForm2を使ってComboboxに取得するSheet名を
＞一覧化し、そのComboboxからSheet名を選べる様にしたいと思います。
このUserform2を使ってComboboxに取得出来るようにと書きましたが、
返って紛らわしい事になってしまうのならば、
No.4で行ったようにUserform1にComboboxを設置し、
そこに取得したSheet名を表示させるようにでも構いません。

回答No.4

piroin654

UserFormにボタンとコンボボックスを設定し、ボタンの
クリックイベントに以下を設定します。

Private Sub CommandButton1_Click()
Dim wkb As Workbook
Dim wks As Worksheet
Dim strFileName As String

strFileName = "テストファイル.xlsm"

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & strFileName, , True)
With ComboBox1
For Each wks In wkb.Worksheets
.AddItem wks.Name
Next
End With

wkb.Close
Set wks = Nothing
Set wkb = Nothing
End Sub

続いて、UserFormに二個目のボタンを設定し、
質問のSub CommandButton1()の中身を設定
します。
設定したら、

Set LstWs = LstWb.Sheets("Sheet1")

のところを、

If ComboBox1.Text = "" Then
MsgBox "コンボボックにデータが表示されていません"
Exit Sub
Else
MsgBox ComboBox1.Text
Set LstWs = LstWb.Sheets(ComboBox1.Text)
End If

に置き換えます。

あと、質問のコードの中でファイルを開くとき、
ReadOnlyにするのか、とか、開いたファイルは
最後に閉じておくとか、オブジェクトの破棄を
しておくとか、いろいろあるようですが、
とりあえずこれで試してみてください。

補足

回答ありがとうございます。

私の説明が解りにくい為にご迷惑をおかけしました。
Userform1とUserform2の件ですが、
Userform1は、私が記載しましたコード(Commandbutton1)のみ
設置していました。
Userform2ですが、Userform1で動作した時に
Userform2が出現しUserform2内にあるCombobox1で最初にオープンした
Sheet名の情報を選択し基のSheetに貼り付けする内容になっています。
Userform2のCommandbutton1ですが、下記のコードが入っています。

Private Sub CommandButton2_Click()
Day = Me.ComboBox1.Value
Unload Me
End Su

ご提示頂きましたコードを試してみました。
最初のコードでテストファイルの情報をCombobox1に表示される事が
できました。

この部分を Set LstWs = LstWb.Sheets("Sheet1")
書き換えてみました。
If ComboBox1.Text = "" Then ←の部分でエラーになってしまいます。
なんででしょうか？

回答No.3

crowswork

まずは、x-1919さんが回答されているコードが何をしようとしているものなのかを理解できないと、教えられた箇所にこのコードを追加してもご希望通りの動作にならないと思います。

x-1919さんが、回答して頂いたコードは、BookにあるSheet名を一つずつ取得するためのループ文です。
awmoriさんは、Sheet名を取得してコンボボックスに追加したいんですよね？
であれば、x-1919さんのコードを応用して、さらにコンボボックスに追加するためのコードも記載しないとご希望の動作にはなりません。
また、このコードを追加することにより既存コードの部分もいくらか変更を加えないといけません。
たとえば、Sheet名を指定している部分は必要なくなりますよね？

このようなことを一つずつ理解しながら解決していくべきではないかと思います。

補足

回答が遅くなりまして申し訳ありません。
初心者ではありますが、動作の内容は把握しているんですが、
希望の動作をしたいと思っても・・・なかなかうまく行きません。

回答No.2

akina_line

こんにちは。

　ExcelVBAを使いこなすには、Excelのオブジェクトを把握しないとなりません。

　Visual Basic Editorのヘルプから「Visual Basicヘルプ」の「Excel Visual Basicリファレンス」を呼んでみてください。

　さて、ワークシートの名前を取得するには、「Worksheets コレクション」を使うとよいでしょう。

では。

補足

回答ありがとうございます。
ヘルプを見ても・・・どの様にして工夫したら良いのかわかりません。
お手数おかけしますが、どこをどの様に変えたらできるか教えて頂けますでしょうか？すいませんがよろしくお願いします。