VBAでのシート作成

Question

VBAでテキストファイルを開いてCSVに変換するというプログラムを書いています。
まだ途中ですが、
新規シートを作成する際に、
Worksheet.Add After:=Worksheets(Worksheets.Count).Name = sheet_name
のところで、「オブジェクトが必要です」とのエラーが出てしまいます。
対応策のご教示をいただきたいです。

型の指定の仕方に問題があるのでしょうか？

コード

Private Sub DataConvertion_Click()

Dim buf As String, cnt As Long
Dim file_add As String
Dim path As String, n As Long, i As Long
Dim file_path As String

file_add = Cells(3, 4).Value

path = file_add & "\"
buf = Dir(path & "*.txt")
file_path = file_add & "\" & buf

Do While file_path <> ""
    cnt = cnt + 1
    Cells(cnt, 1) = buf
    
    Dim sheet_name As String
    sheet_name = Left(buf, 14)
    Worksheet.Add After:=Worksheets(Worksheets.Count).Name = sheet_name
    
    'データをCSV化
    Open file_path For Input As #1
        Do Until EOF(1)
            Line Input #1, file_path
            n = n + 1
            Cells(n, 1) = file_path
            Loop
    Close #1
    
    buf = Dir()
Loop

End Sub

Answer 1

Sub テキスト取り出し()

Dim buf As String, cnt As Long
Dim file_add As String
Dim path As String, n As Long, i As Long
Dim file_path As String
Dim sheet_name As String
Dim a As String
Dim ba As String '読み込み用

On Error GoTo myerror
a = ActiveSheet.Name
file_add = Cells(3, 4).Value

path = file_add & "\"
buf = Dir(path & "*.txt")
file_path = file_add & "\" & buf

Do While file_path <> ""    'ファイル名が有る限り
cnt = cnt + 1              'セルカウンター
Worksheets(a).Cells(cnt, 1) = buf 'シート名を印字

sheet_name = Left(buf, 14)  'Txtﾌｧｲﾙ名
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = sheet_name  '追加最終右側ｼｰﾄへ名前

'データをCSV化
Open path & buf For Input As #1

Do Until EOF(1)

Line Input #1, ba
n = n + 1
Cells(n, 1) = ba
Loop
n = 0

Close #1
buf = Dir()
Loop

myerror:

End Sub

Answer 2

sheet_name As String
sheet_name = Left(buf, 14)
Sheets.Add After:=ActiveSheet
Sheets(Sheets.Count).Name = sheet_name
'データをCSV化

Comments

[hatena19]

そのコードだと、アクティブなシートの右に新規シートを挿入後、最右端のシートの名前を変更するという処理になりますね。アクティブシートが最右端のシートならいいですが、そうでないとまずいですよね。

最右端にシートを追加するというのが質問者さんの希望と思われますので、下記になると思います。

sheet_name As String
sheet_name = Left(buf, 14)
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = sheet_name

[syousuke.33]

hatena19様
指摘していただき　ありがとうございました。
質問者のuimaro様のアクションが何もないけど理解されたのでしょうか？
テキストファイル４枚作って　テストしてみましたが又エラーが出ました。
「ファイル名または番号が不明です」表示された。
Open file_path For Input As #1 　ここで黄色ラインになっていた
file_pathにカーソルを置くとシートパスでなく、テキストの文字列が
表示されていた。解決に時間かかった
参考までに構文を記載しました。
シート最終右側に余計なシートが表示された！

[uimaro]

返答が遅くなりすみません。
下記の様に、別のシートをコピーする方法にしたらエラーは出なくなりました。
Call Worksheets("FORMAT").Copy(After:=Worksheets(Worksheets.Count))
結局根本的な部分とは少し違った形での解決となりましたが、ご協力いただきありがとうございました。

Answer 3

- Worksheet.Add After:=Worksheets(Worksheets.Count).Name = sheet_name
+ Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheet_name

上記の修正でいいのですが、追加と名前変更を1行で記述するのは読みづらいので、下記のように分けた方が分かり安いかもしれません。

  Worksheets.Add After:=Worksheets(Worksheets.Count)
  ActiveSheet.Name = sheet_name