ken3memo (三流君)

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



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

2009-11-24

AccessからExcelを起動してファイルを開く、そんなマクロ(VBA)

Access, 動画解説

５．AccessのデータをExcelへ転記する

まだまだ、穴があり、改良の余地がある入力画面をほっといて(おいおい)

次は、データをExcelに書き出してみたいと思います。

操作の動画↓

D

http://www.youtube.com/watch?v=TRRc_fDH1Xk

5.1 Excel ひな型(テンプレート)ファイルの用意

まず、罫線付き、フォーマットが整ったExcelファイルを用意します。

(※白紙から作る方法もありますが、今回は事前に準備します。)

f:id:ken3memo:20091124013755j:image

↑テンプレート.xls として、作成します。

5.2 AccessからExcelを起動します(ウイザードで楽に作ります)

次にAccessからExcelを起動します。

ここでは、ウィザードを利用して作ってみます。

f:id:ken3memo:20091124013754j:image

↑まぁ、普通にボタンを選択するとウィザードが立ち上がるので、

　アプリケーション -- Excelの起動

　を選択します。

すると、下記のコードが自動的に作成されます。

Private Sub コマンド0_Click()
On Error GoTo Err_コマンド0_Click

    Dim oApp As Object

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True

Exit_コマンド0_Click:
    Exit Sub

Err_コマンド0_Click:
    MsgBox Err.Description
    Resume Exit_コマンド0_Click
    
End Sub

5.3 作成済みのひな型(テンプレート)ファイルを開きます

まず、Excel側でマクロを記録します。

ツール -- マクロ -- 新しいマクロの記録　を選択します。

f:id:ken3memo:20091124013753j:image

次に、ファイル -- 開く で 先ほど作成した テンプレート.xlsを開きます。

開いたら、マクロの記録を終了させます。

f:id:ken3memo:20091124013752j:image

作成されたマクロ(Excel VBA)を確認するには、

ツール -- マクロ -- Visual Basic Editor を選択します。

(alt+F11キーでもOKです。)

標準モジュールにマクロが作成されています。

Option Explicit

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

'
    ChDir "E:\WORK"
    Workbooks.Open Filename:="E:\WORK\テンプレート.xls"
End Sub

f:id:ken3memo:20091124013751j:image

↑作成された.xlsファイルを開くマクロ(VBAの命令)↓をコピーします

Workbooks.Open Filename:="E:\WORK\テンプレート.xls"

Access側に戻り、

記録したマクロ(コピーしたマクロ)をAccess側に貼り付けます。

頭にExcel参照用の変数 ここではoAppを付けます。

Option Compare Database
Option Explicit

Private Sub コマンド0_Click()
On Error GoTo Err_コマンド0_Click

    Dim oApp As Object

    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:="E:\WORK\テンプレート.xls"
    '↑頭にoAppとAccessからExcelを参照する変数を付ける

Exit_コマンド0_Click:
    Exit Sub

Err_コマンド0_Click:
    MsgBox Err.Description
    Resume Exit_コマンド0_Click
    
End Sub

Excelを閉じ、Accessのボタンを押してテストします。

無事にひな型(テンプレート)が開かれたことを確認します。

続きは、全体の開発の流れを見るには http://ken3hitori.g.hatena.ne.jp/bbs/2?mode=tree のツリーを見てください。

↓ここまでのサンプルファイルです。

Test1124.zip 直

↑Access2003とExcel2003のテストファイルが入ってます。

Permalink | コメント(0) | トラックバック(3) | 01:39

ADOを使用してデータを取り出しExcelへセットする

Access

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

5.4 データの転記 ひな型(テンプレート)にデータを流し込みます

やっと、データの転記処理です。

5.4.1 ファイルの位置をCurrentDb.Nameから抜き出す

と、その前に、

oApp.Workbooks.Open Filename:="E:\WORK\テンプレート.xls"

だと、E:\WORK\とフォルダー・ディレクトリの場所が固定なので、

手前味噌の No.007 ＭＤＢと同フォルダのExcelファイルを開く

http://www.ken3.org/vba/backno/vba007.html

を参考にして、

Private Sub コマンド0_Click()
On Error GoTo Err_コマンド0_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
    
    '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 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



Exit_コマンド0_Click:
    Exit Sub

Err_コマンド0_Click:
    MsgBox Err.Description
    Resume Exit_コマンド0_Click
    
End Sub

CurrentDb.Name から 現在のフルパスを取り、パスを抜き出して、"テンプレート.xls"と合わせてみました。

strXLSFILEが無事に作成されたら、

おまけで、Dir関数を使用して、ファイルの存在チェックも行い、

oApp.Workbooks.Open Filename:=strXLSFILE

で、開きました。

5.4.2 社員テーブルから全てのデータを抜き出しセットする

手前味噌のNo.093 Access2000 ADOでクエリーのレコードを参照 Excelへ出力

http://www.ken3.org/vba/backno/vba093.html

を参考にして、データをセットしてみます。

Dim rs As New ADODB.Recordset

で変数を1つ作成して、

rs.Open "select * from 社員テーブル;", CurrentProject.Connection, _

adOpenKeyset, adLockOptimistic

で、レコードセットを作ります(開きます)

あとは、.EOFのループで

'ループ処理

While rs.EOF = False 'いつものEOFが偽の間

MsgBox "氏名は" & rs.Fields("氏名") '氏名をテストで表示する

rs.MoveNext '次のレコードに移動しないと、とんでもないことに(笑)

Wend

rs.Fields("フィールド名")で、データを取り出せるので、

oApp.cells(行, 列) = rs.Fields("氏名")

みたいな感じで、セットします。

D

http://www.youtube.com/watch?v=Bs-J-51DEt0

[動画を貼る]

↓データセットまでのソース

Option Compare Database
Option Explicit

Private Sub コマンド0_Click()
On Error GoTo Err_コマンド0_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
    
    '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 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

    Dim rs As New ADODB.Recordset  'ADOのレコードセットです。
    Dim y As Integer  'セットする行番号です
    
    rs.Open "select * from 社員テーブル;", CurrentProject.Connection, _
                                adOpenKeyset, adLockOptimistic
    'ループ処理
    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列に持っている資格複数をセットする。※後で作成する。
               
        rs.MoveNext  '次のレコードに移動しないと、とんでもないことに(笑)
        y = y + 2    '1つの名簿データで2行使うので、＋２で次の行です
    Wend

    rs.Close   '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
    Set rs = Nothing  '変数も後始末しますか。使った器はキレイにしろって?


Exit_コマンド0_Click:
    Exit Sub

Err_コマンド0_Click:
    MsgBox Err.Description
    Resume Exit_コマンド0_Click
    
End Sub

続きは http://d.hatena.ne.jp/ken3memo/20091124/1259006845 を見てください。

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


↓ここまでのサンプルファイルです。

Test1124.zip 直

↑Access2003とExcel2003のテストファイルが入ってます。

Permalink | コメント(0) | トラックバック(2) | 04:45

Access ADOでクエリーからデータを読み込む時に社員番号を条件としたSQLを作成する

Access, 動画解説

http://d.hatena.ne.jp/ken3memo/20091124/1259005500 の続きです。

5.4.3 社員番号を使用して、資格取得テーブルからデータを抜き出しセットする

社員テーブルのデータを単純にセットする・・・(そんなに単純じゃないけど)

まで、なんとか動いたので、

次は、個人の持っている資格データをH列にセットしてみます。

資格の名前を取り出したいので、

資格取得テーブルと資格名称をつなげたクエリー

クエリー名:Q資格

を１つ作ります。

次に、Q資格から一致する社員番号を取り出すサブ関数を作成します。

'社員番号を受け取り、持っている資格名称を返すサブ関数
Private Function get資格(社員番号 As String) As String

    Dim strSQL As String
    
    strSQL = "select * from Q資格"
    strSQL = strSQL & " where 社員番号='" & 社員番号 & "'"

    Dim strRET As String
    Dim rs As New ADODB.Recordset
    rs.Open strSQL, CurrentProject.Connection, _
                                adOpenKeyset, adLockOptimistic
    'ループ処理
    strRET = ""  'リターン値の初期化
    While rs.EOF = False  'いつものEOFが偽の間
        strRET = strRET & rs.Fields("資格名称") & " "
        rs.MoveNext  '次のレコードに移動しないと、とんでもないことに(笑)
    Wend

    rs.Close   '開いたら閉じろ、ドアを開けたら閉めるってしつけられたでしょ(笑)
    Set rs = Nothing  '変数も後始末しますか。使った器はキレイにしろって?

    get資格 = strRET  '作成した変数を返す

End Function

あとは、上位の関数で

'H列に持っている資格複数をセットする。

oApp.cells(y, "H") = get資格(rs.Fields("社員番号"))

↑みたいに、rs.Fields("社員番号")を渡して、資格名称を受け取り、セットします。

作成手順の動画

D

http://www.youtube.com/watch?v=3jYbUgntzm4

[動画を貼る]

↓ここまでのサンプルファイルです。

Test1124.zip 直

↑Access2003とExcel2003のテストファイルが入ってます。

続きは http://d.hatena.ne.jp/ken3memo/20091127/1259307176 を見てください。

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

Permalink | コメント(0) | トラックバック(2) | 05:07



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




.

カレンダー
<< 2009/11 >>
日 月 火 水 木 金 土
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
編集したキーワード
ken3memoのブクマ
その他blog

流行のBlogで情報発信(手打ちのHTMLよりブログで更新が楽なので)
・はてなを使用して[id:ken3memo]メモ的に放り込んでいるブログです
・はてなを使用して[id:ken3link]商品リンクなどを放り込んでいるサブidのブログです
・[その他商品紹介Blog] ・・・ 内容の薄い、商品のメモ？です

メニューほか

分類・カテゴリー一覧

SEO
IE操作
Outlook
Excel
Access
DAO
SQL
VBA
PowerPoint
Windows
はてな
Google
人力検索
楽天
広告
VBS
雑談
メモ
ブログトップ 記事一覧 ログイン 無料ブログ開設