エクセルのブック内にある複数のシート名を判断してシート内のデータを行ごとに抽出する方法

質問

エクセルブックの中からシート名が"TABLEnnn"(nnnは000から999の間)のものを対象に
データを抽出し、一定の文字列と連結し(SQL文)、同じブックのシートに書き出す処理を考えています。

（DBのTABLEの英語列名、日本語列名、備考欄があるのでそれらを取得・連結し、COMMENT ON文を作成したい）

仕様は以下のとおり考えてみたのですが、

【COMMENT ON作成支援 仕様】

※ブック内の最初のシートに移動

(1)　シート名が"TABLEnnn"(nnnは000から999の間)のシート名を取得。
　　シート『comment_on』に"--シート名"を書き出す。
　　取得できないとき『終了しました』を出力し終了。

(2)　(1)のシート内のＡ列の値が"項目名称"の行に移動。

(3)　現在位置の行で列値が"備考"の列番号を取得。

(4)　現在行の次の行から、Ａ列の値、Ｂ列の値(1～60byte)、(3)の列の値(1～1000byte)を取得。
　　※(3)の列の値の改行コードはSPACEに置き換える。

(5)　(1)、(4)で取得した各値を用いて
　　COMMENT△ON△COLUMN△(1)の[シート名].(4)の[Ａ列の値]△'(4)の[Ｂ列の値]:(4)の[(3)の列の値]';
　　となるよう文(SQL文)を作成する。
　　※△はスペース一桁

(6)　(5)の値をシート『comment_on』のデータのある最後の行の次の行に書き出す。

(7)　(1)のシートのＡ列にデータがなくなるまで(4)～(6)を実行する。

(8)　シート『comment_on』にNULL行を追加し、次のシートに移り、(1)～(7)を実行。

"項目名称"の行番号、"備考"の列番号が一定でなく、シート名も関係ないものが多く、
(7)に関係するレコード数(行数)も一定でないのでどうしたものかわかりかねています。
できるかどうかも含めてなにかお分かりになる方、よろしくお願いします。

回答No.2

papayuka · ベストアンサー

出来るかという問いならVBAで可能だと思います。

> ※ブック内の最初のシートに移動

これは複数シートがあるブックの全シートを対象に調査するなら不要でしょう。

> (1)　シート名が"TABLEnnn"(nnnは000から999の間)のシート名を取得。
> 　　シート『comment_on』に"--シート名"を書き出す。
> 　　取得できないとき『終了しました』を出力し終了。

これはループしながらシート名を判定させれば良いと思います。
判定をどこまでの基準でやる必要があるかもありますが、「"TABLE"で始まるシート」くらいで良ければ、

Sub Test()
Dim ws As Worksheet, Cnt As Integer
Cnt = 0
For Each ws In Worksheets
　If ws.Name Like "TABLE*" Then
　　Cnt = Cnt + 1
　　'処理
　　MsgBox ws.Name > 例
　End If
Next ws
If Cnt = 0 Then MsgBox "ない"
End Sub

> (2)　(1)のシート内のＡ列の値が"項目名称"の行に移動。

Findメソッドで探し、Rowの値を取得します。

> (3)　現在位置の行で列値が"備考"の列番号を取得。

上記の Row を元にして、Findメソッドで探し、Columnの値を取得します。

> (4)　現在行の次の行から、Ａ列の値、Ｂ列の値(1～60byte)、(3)の列の値(1～1000byte)を取得。
> 　　※(3)の列の値の改行コードはSPACEに置き換える。

Replaceメソッド等を使って vbLf(又は vbCrLf?) を " " に置き換えれば良いと思います。

> (5)　(1)、(4)で取得した各値を用いて
> 　　COMMENT△ON△COLUMN△(1)の[シート名].(4)の[Ａ列の値]△> (4)の[Ｂ列の値]:(4)の[(3)の列の値]> ;
> 　　となるよう文(SQL文)を作成する。
> ※△はスペース一桁

&で繋げて変数にでも代入すればＯＫです。

> (6)　(5)の値をシート『comment_on』のデータのある最後の行の次の行に書き出す。

Range("A65536").End(xlup).Row + 1 で最終行を判定出来ます。

> (7)　(1)のシートのＡ列にデータがなくなるまで(4)～(6)を実行する。

空白まで上記をループさせれば良いですね。

> (8)　シート『comment_on』にNULL行を追加し、次のシートに移り、(1)～(7)を実行。

追加というのが判りませんが、End(xlup).Rowで最終行が判るので +2 の行から次の書込みを開始すれば良いでしょう。

お礼

ありがとうございます！！

さっそく実装してみます。
VBAは人の作ったのを利用するくらいで
プログラムとしては普段使わないので
なにかとっかりがあれば・・・と思って投稿したのですが
このままでいけそうですね。

つかえるものが組めたら報告します。

ありがとうございました。

補足

> (8)　シート『comment_on』にNULL行を追加

は、ご指摘のとおり＋２行目から書き出せばいいんですね。

(普段テキスト形式のファイルに書き出したりすることが多いので
ついその感覚で書いてしまいました)

回答No.1

pippy

投げやりなアドバイスで申し訳ありません。

こちらに投稿した方が返答が早いかと思います。

参考URL：

http://www.moug.net/

お礼

ありがとうございます。
さっそく見てきます。