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.1

x-1919

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
　msgbox sht.Name
Next

補足

回答ありがとうございます。
ご指定いただきましたコードをどの箇所に記述すればいいのでしょうか？
私が提示したコードは、作って頂いたものでして・・・　
どこをどうすれば良いのか教えて頂けますでしょうか？