【Excel VBA】ファイルとシートを選択してコピーする処理

Question

はじめまして。Excel勉強中の者です。
Excel VBAで
マクロボタンを押した後、
ユーザーに別のファイルとシートを選択して
選択したシートをコピーするという処理をしたく
作成中です。
色々なサイトを見ながら作ってみたのですが
現在のわたしの力では
シートを手入力する処理までしか作れないのと
キャンセル後の処理がうまくできないようです。

現在は以下のようになってます。

------------------------
Sub test()

 Dim ret As Boolean
 Dim myPath, msg, shName As String
 Dim wb_A As Workbook, Sh As Worksheet

'ファイルを選んでもらう
　myPath = Application. GetOpenFilename ("Microsoft Excelブック, *.xls?")

    If myPath = "False" Then Exit Sub
    Set wb_A = Workbooks. Open (myPath)
    For Each Sh In wb_A. Worksheets
      msg = msg & Sh. Name & Chr(10)
    Next
      msg = msg & "シート名を入力してください"
    Do

 ShName = InputBox (msg, "シート選択" )

    For Each Sh In wb_A. Worksheets
      If shName = Sh. Name Then Flag = True
    Next
      If Not Flag Then shName =""

 Loop While Len (shName) = 0

  wb_A. Worksheets(shName). Copy before := ThisWorkbook. Sheets(1)
  wb_A. Saved = True
  wb_A.Close
  Set wb_A = Nothing

End Sub
-----------------------

です。(誤字があったら申し訳ございません)
現在マクロは一応動くのですが
シート名を手入力するのではなく、選択できるように変更したいのと(For Eachでダイアログボックスに入れる？)
フォルダの選択をキャンセルした時に
キャンセルしましたの文言をmsgBoxで出したいのと
シートの選択をキャンセルすると、
なにか入力するまで永遠に入力欄が表示され続けるのをどうにかしたいのですが
色々工夫してもどうにもうまく処理できず
わたしの技術ではここまでが限界でした。

どうか、お力を貸していただければと思います。
よろしくお願い致します。

Qchan1962 · Accepted Answer

＞UserFormを教えていただけたら嬉しいです。
先ずコードから
標準モジュールに
Option Explicit
Public Buf As String
Sub test()
Dim ret As Boolean, Flag As Boolean
Dim myPath, msg, shName As String
Dim wb_A As Workbook, Sh As Worksheet
　 'ファイルを選んでもらう
　 myPath = Application.GetOpenFilename("Microsoft Excelブック, *.xls?")
　 If myPath = "False" Then
　　  MsgBox ("キャンセルしました")
　　  Exit Sub
　 End If
　 Set wb_A = Workbooks.Open(myPath)
　 Buf = ""
　 With UserForm1
　　  For Each Sh In ActiveWorkbook.Worksheets
　　　　.ListBox1.AddItem Sh.Name
　　  Next
　　  .CommandButton1.Caption = "キャンセル"
　　  .Caption = "ワークシート選択"
　　  .Show
　 End With
　 If Buf = "" Then
　　  MsgBox ("シート選択がキャンセルされました")
　 Else
　　  wb_A.Worksheets(Buf).Copy before:=ThisWorkbook.Sheets(1)
　 End If
　 wb_A.Saved = True
　 wb_A.Close
　 Set wb_A = Nothing
End Sub

Formモジュールに
Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_Click()
　 Buf = ListBox1.List(ListBox1.ListIndex)  'ワークシート名を取得
　 Unload Me
End Sub

UserForm1は、添付図のような感じで

いかがでしょう？

Qchan1962 · Answer

＃２です。
ユーザーフォームは作成されたと言う事でよろしかったでしょうか
一応、参考サイト　https://www.sejuku.net/blog/37407

ユーザーフォームを挿入して　ツールボックスで
リストボックス　と　コマンドボタンを　作成します。
UserForm1やListBox1などの数字部分は、自動的に作成されますが、念のため確認してください。
＞.ListBox1のところでコンパイルエラー
上のUserForm1でエラーではないので
ListBox1オブジェクト自体を取得できていない可能性が高いと思います。

一度、下記でUserForm1が表示されリストボックスとコマンドボタンが表示されるか試してください。

Sub test1()
UserForm1.Show
End Sub

fujillin · Answer

こんにちは

Formを使わなくてすむような、簡単な方法はないものかと考えてみました。

エクセルには「シート選択ダイアログ」があるので、GetOpenFilenameと同様に、これを利用できないものかと考えましたが、どうやら組み込みダイアログにはリストアップされていないようです。
「シート選択ダイアログ」を表示するのに、以下の方法をみつけましたが、戻り値が無いため、残念ながらCancelを判別することはできません。
とは言っても、シートを選択する際に十分利用できるのではと思います。

以下は、シート選択部分のみの例ですが、ご参考にでもなれば。
（アクティブなブックに対して処理されます）

' シート選択
　With CommandBars.Add(Temporary:=True)
　　.Controls.Add(ID:=957).Execute
　　.Delete
　End With

Qchan1962 · Answer

こんばんは、
 InputBoxでは、シート選択が出来ないのでUserFormを使うのが簡単ですね
UserForm作りますか？　取り敢えずエラー対策を付け足してみました。

Sub test()
Dim ret As Boolean, Flag As Boolean
Dim myPath, msg, shName As String
Dim wb_A As Workbook, Sh As Worksheet
    'ファイルを選んでもらう
    myPath = Application.GetOpenFilename("Microsoft Excelブック, *.xls?")

If myPath = "False" Then
        MsgBox ("キャンセルしました")
        Exit Sub
    End If
    Set wb_A = Workbooks.Open(myPath)
    For Each Sh In wb_A.Worksheets
        msg = msg & Sh.Name & Chr(10)
    Next
    msg = msg & "シート名を入力してください"
    Do
        shName = InputBox(msg, "シート選択")
        If shName = "" Then GoTo sheet_cancel
        For Each Sh In wb_A.Worksheets
            If shName = Sh.Name Then Flag = True
        Next
        If Not Flag Then shName = ""
    Loop While Len(shName) = 0
    wb_A.Worksheets(shName).Copy before:=ThisWorkbook.Sheets(1)
sheet_cancel:
    If Flag = False Then MsgBox ("シート選択がキャンセルされました")
    wb_A.Saved = True
    wb_A.Close
    Set wb_A = Nothing
End Sub

【Excel VBA】ファイルとシートを選択してコピーする処理

＞UserFormを教えていただけたら嬉しいです。

＃２です。

こんにちは

こんばんは、

似たような質問が見つかりました

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

関連するカテゴリからQ&Aを探す

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

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング