三流君 ken3のmemo置き場

三流プログラマーのメモ書きです。主にVBAやWindowsの話題が多いです

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

よく検索されるキーワード: [質問回答XXXXさんへ] [CreateObject] [VBA] [JRA競馬オッズ]

Excel シートのコピーをマクロ記録で作成 Access側で使ってみる

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

5.5.2.1 ひな型(テンプレート)ファイルを開いた後にコピーする

問題点
1.Excelへ出力を繰り返すことができる(まぁ、これはこれでいいのかも)
  でも、転記後、ひな型ファイルを保存することができる。(壊すことができる)

↑に対応するために、
ひな型(テンプレート)ファイルを開いた後にコピーする。
そんな処理にチャレンジしてみたいと思います。

処置として、
一つの案が、テンプレートにデータを流し込んでから、
そのまま保存すると白紙のひな型(テンプレート)が汚れてしまうので、
ひな型(テンプレート)、元のxlsファイルを開いたらすぐに、
シートのコピー 新規のブック で 新しいブックにコピーします。
で、ひな型(テンプレート)は何もしないで閉じる、そんな処理を追加してみます。

まずは、Excelで操作のマクロを記録します。
マクロ記録で
ア.シートを右クリック、移動またはコピーを選択
イ.新規ブック と □コピーにチェックを入れる。
ウ.新規ブックにコピーされるので、元のテンプレートに戻ります(ウインドウで選択)
エ.テンプレート.xlsを閉じます。

上記マクロを記録すると、

Option Explicit

Sub Macro4()
'
' Macro4 Macro
' マクロ記録日 : 2009/11/28  ユーザー名 : user2000
'

'
    Windows("テンプレート.xls").Activate
    Sheets("名簿").Select
    Sheets("名簿").Copy
    Windows("テンプレート.xls").Activate
    ActiveWorkbook.Close
End Sub

と記録されます。

あとは、このマクロをAccessへ移植します。
と言っても、貼り付けて、頭に参照用の変数 oApp を付けただけです。


oApp.Windows("テンプレート.xls").Activate
oApp.Sheets("名簿").Select
oApp.Sheets("名簿").Copy
oApp.Windows("テンプレート.xls").Activate
oApp.ActiveWorkbook.Close

↑の挿入位置は、テンプレート.xlsを開いた後に入れました。


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 '変数も後始末しますか。使った器はキレイにしろって?

End Sub

これで、テンプレートファイルを誤って壊される心配も無くなりました。。。

操作手順とテストの動画
[動画を貼り付ける。]
www.youtube.com
http://www.youtube.com/watch?v=0pAGQdCZTgc

Ken3 ホームページ 目次

分類:HPを大きく分けると4つの柱(分類)です。

  1. [VBA・マクロ プログラミング]の解説
    当店の人気はVBA系のCreateObject("XXXXXX.application")で他のアプリケーションを操作するサンプルが人気です
  2. [プログラマーの愚痴]では、あまり見せたくない三流プログラマーの内面かな。
    三流君を踏み台にする
  3. [古いクラシック ASP(Active Server Pages)]の解説。
  4. [元コンビニ店長時代の話]が弟に巻き込まれ、失敗した脱サラ、畑違い?の仕事で失敗。
主に上記4つの分類でHP作成やメルマガの発行を行ってます。
※更新頻度が落ちていて情報の鮮度が悪いです。



本当に三流なんです(笑):たまにスゴイですねなんて言われることもありますが、
真実は→ [三流君の真実は...] ←を初めに見てくださるとわかると思います。
(からくりは、成功例↑しか載せてなくて ヒドイ失敗例はお蔵入り迷宮入りが多かったりします)