Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(macOS 11.0 : Excel 2016)
シートが最後に挿入されない
投稿日時: 22/04/24 04:12:31
投稿者: のらねこ

サブフォルダ内のブックをブックをブックを拡張子不問でエクセルブックとして開いて、操作ブックに集約・作業するVBAを作っています。
ファイルを集約するところまで記述したのですが、VBAを実行するとws(開いたブックをコピペするシート)がThisWorkbook内の最後のシートになりません。
 
■やりたいこと
wsChack、サブフォルダ内のブック1、サブフォルダ内のブック2、サブフォルダ内のブック3・・・
■実際の挙動
・・・サブフォルダ内のブック3、サブフォルダ内のブック2、サブフォルダ内のブック1、wsChack
原因と対策についてアドバイスをお願いいたします。
 
================
 
Option Explicit
 
Sub ファイル結合()
 
Dim i As Long
Dim sFolder As String
Dim sFileName As String
Dim wb As Workbooks
Dim ws As Worksheet
Dim wsChack As Worksheet: Set wsChack = ThisWorkbook.Worksheets("項目チェック")
 
Application.ScreenUpdating = False
 
wsChack.Range("A:A").ClearContents
sFolder = ThisWorkbook.Path & "/集約ファイル/"
sFileName = Dir(sFolder & "*.*")
i = 1
Do While sFileName <> ""
    wsChack.Cells(i, 1) = sFileName
    With Workbooks.Open(FileName:=sFolder & sFileName)
        Set ws = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(Sheets.Count))
        ws.Name = wsChack.Cells(i, 1).Value
 
   '///ここに作業内容を記述する///
 
        .Close savechanges:=False
    End With
    i = i + 1
    sFileName = Dir()
Loop
 
Application.ScreenUpdating = True
 
End Sub

投稿日時: 22/04/24 04:15:10
投稿者: のらねこ

誤:ファイルを集約するところまで
正:ファイルをコピペするシートを挿入するところまで
でした。

回答
投稿日時: 22/04/24 05:57:07
投稿者: simple

Set ws = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(Sheets.Count)
それは、
Set ws = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
と書いたことになるからです。
今アクティブなブックは開かれたブックであり、ThisWorkbookではありません。
Set ws = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ですね。

回答
投稿日時: 22/04/24 08:53:23
投稿者: WinArrow
投稿者のウェブサイトに移動

余計な心配かもしれませんが・・・・
 
> ws.Name = wsChack.Cells(i, 1).Value
シート名が、拡張子を含んだブック名と同じになるが、その仕様でよいですか?

回答
投稿日時: 22/04/24 09:12:13
投稿者: simple

シートを新規に作成してそこに転記しているようですが、
転記元のシート自体を直接シートコピーする方法もあると思います。
そのほうが内容の転記も不要で効率的な気がしますが、どうなんでしょうか。
古い形式(xls形式)のシートがあっても、問題なくシートコピーできそうです。
全体ではなく、そのうち一部だけを集約するということなんですかね。
 
当面の課題は先ほどの回答でクリアーすると思いますが、
そのあたりが少し気になりました。

投稿日時: 22/04/25 07:54:38
投稿者: のらねこ

>simple さん
ご丁寧な解説・解決策のご提案ありがとうございました!
考え方が理解できました。
 
また、ご指摘いただきました「新規シートの挿入ではなく、開いたブックのシート1をそのまま挿入すれば良いのではないか」については最初その方向で考えていたのですが、コピーしたシートをオブジェクト変数に入れる方法が分からずにこの方法をとっていました。
そもそも、開くブックも次のように変数に入れたかったのですが、これもうまくいきませんでした。
 
set wb=workbooks.open(filename:=・・・)
 
こちらについてはもう少し自分で解決策を考えてみて、それでも解決できなければ改めて投稿させていただきたいと考えています。
 
>WinArrow
拡張子については、ひとまずは大枠のコードを書いてから細部を詰めるつもりでしたが、拡張子がシート名に含まれると何かデメリットがあるのかが自分の中で答えが出ず、今は「そのままファイル名」をセットしている状態です。
 
こんなに早く解決すると思っていませんでした。
ありがとうございました!