データ転記マクロ

VBA初心者向けブログみたいなタイトルなのに、全然初心者向けじゃなかったので、ちょっと初心者の頃を思い出して書く。

私がVBAにハマるきっかけになったマクロです。

f:id:akashi_keirin:20170307221218j:plain

こんな個票のデータを、

f:id:akashi_keirin:20170307221204j:plain

こんな集約表に転記していく、という作業です。

f:id:akashi_keirin:20170307221224j:plain

集約用のExcelファイルと、データ個票のExcelファイルは、この「集約テスト」フォルダの中に、

f:id:akashi_keirin:20170307221309j:plain

こんな風に収まっているという想定で。

「データ集約用.xlsm」の標準モジュールに以下のコードを書く。

Option Explicit
Sub sendDataVer1()
  '作業フォルダパスを変数に格納
  Dim folderPath As String
  folderPath = ThisWorkbook.Path
  'アクティブシートを変数にセット
  Dim objSheet As Worksheet
  Set objSheet = ActiveSheet                                          '……(1)
  '個票ブックのファイル名を変数にセット
  Dim objFileName As String
  objFileName = objSheet.Parent.Name                                  '……(2)
  'データの転記
  With ThisWorkbook.Worksheets("集約")                                '……(3)
    Dim tgtRow As Integer
    tgtRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1                  '……(4)
    .Range("A" & tgtRow).Value = objSheet.Range("B3").Value 'No     '……(5)
    .Range("B" & tgtRow).Value = objSheet.Range("B7").Value '級
    .Range("C" & tgtRow).Value = objSheet.Range("C7").Value '班
    .Range("D" & tgtRow).Value = objSheet.Range("F3").Value '氏名
    .Range("E" & tgtRow).Value = objSheet.Range("F5").Value '期別
    .Range("F" & tgtRow).Value = objSheet.Range("B5").Value '登録
    .Range("G" & tgtRow).Value = objSheet.Range("F7").Value '戦法
  End With
  '個票ファイルを閉じてフォルダ移動
  objSheet.Parent.Close False                                         '……(6)
  Name folderPath & "\" & objFileName As _
       folderPath & "\処理済\" & objFileName                      '……(7)
End Sub

かなり細かくコメントを入れているので、説明不要かも知れないけど、一応説明。

(1)の

Set objSheet = ActiveSheet

でアクティブシートを変数にセットしている。このマクロは、個票ファイルのシートを開いた状態で実行するので、データが入っているシートを変数にセットしておく。ActiveSheetのままだと、途中でActiveSheetが切り替わってしまったときに思いもかけない結果になることがあるので一応。

(2)の

objFileName = objSheet.Parent.Name

で、個票ブックのファイル名を取得しておく。後の転記のことを考えて、シートを変数にセットしたので、ブック名はParentプロパティから取得している。子オブジェクトからさかのぼって親オブジェクトの値を取得することができるというのはちょっとした便利ワザかも。

(3)の

With ThisWorkbook.Worksheets("集約")

で、ThisWorkbook.Worksheets("集約")というWorkheetオブジェクトに関する記述をまとめているので、以後10行下のEnd Withまでの間、「.」(ピリオド)で書き始めると、ThisWorkbook.Worksheets("集約")が省略されているとみなされる。

(4)の

tgtRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1

で、転記先(集約シート)のデータ記入済み最終行の次の行の番号を割り出している。このやり方は手が勝手に動くレベルまでマスターしておくべき。データ転記系の処理ではやたらよく使うので。

いちおうコードを翻訳しておくと、

1列目(A列)の全行数行目(要するに最終行)から上方向にデータが入っているセルに突き当たった行の行番号+1を変数tgtRowに代入せよ

ということ。

(5)からの7行

.Range("A" & tgtRow).Value = objSheet.Range("B3").Value 'No.'
.Range("B" & tgtRow).Value = objSheet.Range("B7").Value '級'
.Range("C" & tgtRow).Value = objSheet.Range("C7").Value '班'
.Range("D" & tgtRow).Value = objSheet.Range("F3").Value '氏名'
.Range("E" & tgtRow).Value = objSheet.Range("F5").Value '期別'
.Range("F" & tgtRow).Value = objSheet.Range("B5").Value '登録'
.Range("G" & tgtRow).Value = objSheet.Range("F7").Value '戦法'

で一つ一つデータを転記している。すげー原始的な書き方だけど、初心者のうちはこれで良いと思う。

(6)の

objSheet.Parent.Close False

は超重要。個票ファイルを保存せずに閉じている。この「閉じる」という手順を抜かすと、次のNameステートメントの実行でエラーが出る。

(7)の

Name folderPath & "\" & objFileName As _
     folderPath & "\処理済\" & objFileName

がフォルダ移動の処理。初心者にはとっつきにくく感じる処理かも知れない。普通、フォルダ移動って「切り取って貼り付ける」ってイメージだから。でもファイルシステム的にはファイルフルパスを書き換えたらディレクトリが変わるってことだから、こうなる。

f:id:akashi_keirin:20170307221322j:plain

まず、集約用ブックを開いておいて、クイック アクセス ツールバーに今回のマクロを登録しておく。

f:id:akashi_keirin:20170307221341j:plain

個票ファイルを開いたら、クイック アクセス ツールバーのアイコンをクリック!

f:id:akashi_keirin:20170307221351j:plain

ほれ、一瞬でデータが転記されておる。

f:id:akashi_keirin:20170307221402j:plain

次のファイルを開いて、クイック アクセス ツールバーのアイコンをクリック!

f:id:akashi_keirin:20170307221412j:plain

ほれ、この通り。

f:id:akashi_keirin:20170307221422j:plain

この段階で、フォルダ内はこの通り。処理済みのファイルはもうここにはない。

f:id:akashi_keirin:20170307221432j:plain

同じように、個票ファイルを開いてはクイック アクセス ツールバーのアイコンをクリック、を繰り返すと、転記完了。

f:id:akashi_keirin:20170307221440j:plain

んで、「処理済」フォルダの中はこの通り。

コード面ではツッコミどころ満載ですが、データ転記系の業務が多い場合は、これをマスターしたらかなり楽になると思う。