ken3memo (三流君)

挨拶・自己紹介:
失敗続きのAB型の変わり者 :三流プログラマー Ken3です
フリーのエンジニア・個人事業主です・・と書くと聞こえはイイが(それとなくカッコよく聞こえるが)、 現在は小さな案件の受注請負 と 短期派遣 で 日々つつましく?ほそぼそと暮らしてます。
(※詳細は[三流君 三流プログラマーとは?]を見てください)




[記事一覧、バックナンバーを見る]

2009-11-28

AccessからExcelの操作 書式をコピーして罫線・形を整える xl定数の処理ほか

http://d.hatena.ne.jp/ken3memo/20091127/1259307176 からの続きです。

5.5.2.2 罫線などの書式を最後の行までコピーする

問題点

2.ひな型(テンプレート)ファイルの罫線が引かれていない場所にも転送される。

データ数が多いと、テンプレートの枠を越えます。

  ワクを事前に大きく取ると今度は少量のデータ転送時に困ってしまう。

  データの数に合わせて、罫線を作ってほしい。

対策・処置として、

頭の行(4行・5行目の書式)をコピーして、

データ数分だけ書式を貼り付ける

そんな処理で対応してみたいと思います。

f:id:ken3memo:20091128174800j:image

そんな操作・動作をマクロ記録すると、下記のように記録されます。

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 が定義されていないと、怒られコンパイルエラーで止まります。

f:id:ken3memo:20091128174759j:image

oApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

xlPasteFormatsxlNone が Excel側のVBA,定数なので、

Accessでは定義されていないとエラーになってしまいます。

(冷静に考えれば、xlXXXXxlが頭に付いたExcel側の定数なので。)

原因が、定数が定義されていないので、定数を使わない方法があります。

※定数をExcel側で書き出し直接値を書きます。

書き出す方法は、Excel側 VBAのイミディエイト ウインドウで

f:id:ken3memo:20091128174758j:image

? 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\テンプレート.xls

    Dim 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
    
    '後ろから1文字単位で¥を探す
    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 If

    Set 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("ふりがな") '※2段目なので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行使うので、+2で次の行です
    Wend

    rs.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

を選択します。

f:id:ken3memo:20091128174757j:image

↑参照設定すると、あら不思議、

oApp.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

で、普通にExcel VBAの定数が使えたりします。

参照設定する方法がスマート、自然ですよねぇ。。。

※まぁ、ExcelやAccessのバージョン問題などで、

 公開用のソースでは使っていなかったりで、

Const xlPasteFormats = &HFFFFEFE6

Const xlNone = &HFFFFEFD2

とか、定数をConstで頭に書いている(直接の値は書きたくない後でコードの意味がわからなくなる)、そんな対応を取っている人もいると思います。

イロイロあった、試行錯誤・失敗の動作は↓を見てください。

操作・テストの動画をはる

D

http://www.youtube.com/watch?v=HCp_hUbe-Zg


↓ここまでのやりかけのファイル、解凍して使ってみてください。

Test1128.zip 直

↑Access2003のdb1128.mdbとテンプレート.xlsが入ってます

Access の 名簿データをExcelへ出力:[ツリー表示]←で全体を見る。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証





[記事一覧、バックナンバーを見る]








ブログトップ 記事一覧 ログイン 無料ブログ開設