http://d.hatena.ne.jp/ken3memo/20091127/1259307176 からの続きです。
問題点
２．ひな型(テンプレート)ファイルの罫線が引かれていない場所にも転送される。
データ数が多いと、テンプレートの枠を越えます。
ワクを事前に大きく取ると今度は少量のデータ転送時に困ってしまう。
データの数に合わせて、罫線を作ってほしい。
対策・処置として、
頭の行(4行・5行目の書式)をコピーして、
データ数分だけ書式を貼り付ける
そんな処理で対応してみたいと思います。
そんな操作・動作をマクロ記録すると、下記のように記録されます。
Sub Macro1() ' ' Macro1 Macro ' マクロ記録日 : 2009/11/28 ユーザー名 : user2000 ' ' Rows("4:5").Select Range("B4").Activate Selection.Copy Rows("6:21").Select Range("B6").Activate Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range("A4:A5").Select End Sub
まぁ、余計なコードも付いていますが、これをAccessへ貼り付けます。
と、同時に、
Set oApp = CreateObject("Excel.Application")
などで起動しているので、頭に変数oApp.を付け
oApp.Rows("4:5").Select
oApp.Range("B4").Activate
oApp.Selection.Copy
oApp.Rows("6:" & y - 1).Select
oApp.Range("B6").Activate
oApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
oApp.Application.CutCopyMode = False
oApp.Range("A4:A5").Select
みたいな感じで貼り付けます。
で、テスト実行すると、
えっ、コンパイルエラー 変数が定義されてません。
ガクッ、xlPasteFormats が定義されていないと、怒られコンパイルエラーで止まります。
oApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
の
xlPasteFormats と xlNone が Excel側のVBA,定数なので、
Accessでは定義されていないとエラーになってしまいます。
(冷静に考えれば、xlXXXXと xlが頭に付いたExcel側の定数なので。)
原因が、定数が定義されていないので、定数を使わない方法があります。
※定数をExcel側で書き出し直接値を書きます。
書き出す方法は、Excel側 VBAのイミディエイト ウインドウで
? xlPasteFormats
- 4122
? hex(xlPasteFormats)
FFFFEFE6? hex(xlNone)
FFFFEFD2
と、? xl定数 や ? hex(xl定数) で16進数で値を表示させ、AccesのVBAに書き込みます。
oApp.Selection.PasteSpecial Paste:=&HFFFFEFE6, Operation:=&HFFFFEFD2, _
SkipBlanks:=False, Transpose:=False
↑こんな感じで、格好悪いけど、直接値を書き込みます。(&Hで16進数です。)
完成したソース(長いけど・・・)
Private Sub コマンド10_Click()Dim oApp As Object 'Excelアプリの参照用
Dim strWORK As String '文字編集用のワーク変数
Dim i As Integer
Dim strMDBPATH As String 'MDBの保存場所、フォルダー・ディレクトリ
Dim strXLSFILE As String 'テンプレートファイルの名前、e:\xxx\yyyy\テンプレート.xlsDim rs As New ADODB.Recordset 'ADOのレコードセットです。
Dim y As Integer 'セットする行番号です
'印刷FLGがYesのデータを集める。
rs.Open "select * from 社員テーブル where 印刷FLG=Yes", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then '選択件数のチェック
MsgBox "転送データが選択されていません。"
Exit Sub '↑メッセージを表示して関数を抜ける
End If'Excelファイルを開く
'Accessの起動位置を取得 CurrentDb.NameにD:\xxxx\yyyy\zzz.mdbが入っている
strWORK = CurrentDb.Name
'後ろから１文字単位で￥を探す
For i = Len(strWORK) To 1 Step -1
If Mid(strWORK, i, 1) = "\" Then Exit For '￥だったら抜ける
Next i
'D:\xxxx\yyyy\zzz.mdb --> D:\xxxx\yyyy\ にする
strMDBPATH = Mid(strWORK, 1, i)'Excelの元ファイルの名前を作成 D:\xxxx\yyyy\ ＋ テンプレート.xls
strXLSFILE = strMDBPATH & "テンプレート.xls"
'ファイルの存在をチェックする
If Dir(strXLSFILE) = "" Then
MsgBox strXLSFILE & " の存在を 確認して下さい"
Exit Sub 'エラーなので途中で抜ける
End IfSet oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True'テンプレートファイルを開く
oApp.Workbooks.Open Filename:=strXLSFILE
'テンプレートファイルをcopy 2009-11-28 追加
oApp.Windows("テンプレート.xls").Activate
oApp.Sheets("名簿").Select
oApp.Sheets("名簿").Copy
oApp.Windows("テンプレート.xls").Activate
oApp.ActiveWorkbook.Close
'AccessのデータをExcelへ代入する。
'ループ処理
y = 4 '4行目からセットします。
While rs.EOF = False 'いつものEOFが偽の間
oApp.cells(y, "A") = rs.Fields("社員番号") 'データをExcelへセットする。
oApp.cells(y, "B") = rs.Fields("氏名")
oApp.cells(y + 1, "B") = rs.Fields("ふりがな") '※２段目なのでy+1
oApp.cells(y, "C") = rs.Fields("生年月日")
oApp.cells(y + 1, "C") = rs.Fields("性別")
oApp.cells(y, "D") = rs.Fields("郵便番号") & " " & rs.Fields("住所")
oApp.cells(y, "E") = rs.Fields("電話番号")
oApp.cells(y + 1, "E") = rs.Fields("本籍")
oApp.cells(y, "F") = rs.Fields("配偶者有無")oApp.cells(y, "G") = rs.Fields("雇用年月日")
'H列に持っている資格複数をセットする。
oApp.cells(y, "H") = get資格(rs.Fields("社員番号"))
rs.MoveNext '次のレコードに移動しないと、とんでもないことに(笑)
y = y + 2 '1つの名簿データで2行使うので、＋２で次の行です
Wendrs.Close '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
Set rs = Nothing '変数も後始末しますか。使った器はキレイにしろって?
'書式の調整
'データの終わり、y-1まで、書式(罫線など)をコピーする
oApp.Rows("4:5").Select
oApp.Range("B4").Activate
oApp.Selection.Copy
oApp.Rows("6:" & y - 1).Select
oApp.Range("B6").Activate
oApp.Selection.PasteSpecial Paste:=&HFFFFEFE6, Operation:=&HFFFFEFD2, _
SkipBlanks:=False, Transpose:=False
oApp.Application.CutCopyMode = False
oApp.Range("A4:A5").Select
End Sub
まぁ、無事に完成したけど、Excelの定数を調べて直接値を毎回書くって変だよね。
それに、Excelの定数なのでめったに値の変更しないかもしれないけど・・・
まぁ、まぁ、そんなに怒らないでくださいよ。
普通のプログラマーさん達は、参照設定を行いますから。
Accessのツール・参照設定から、
Excel XX.X Object Library
を選択します。
↑参照設定すると、あら不思議、
oApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
で、普通にExcel VBAの定数が使えたりします。
参照設定する方法がスマート、自然ですよねぇ。。。
※まぁ、ExcelやAccessのバージョン問題などで、
公開用のソースでは使っていなかったりで、
Const xlPasteFormats = &HFFFFEFE6
Const xlNone = &HFFFFEFD2
とか、定数をConstで頭に書いている(直接の値は書きたくない後でコードの意味がわからなくなる)、そんな対応を取っている人もいると思います。
イロイロあった、試行錯誤・失敗の動作は↓を見てください。
操作・テストの動画をはる
www.youtube.com
http://www.youtube.com/watch?v=HCp_hUbe-Zg
