[No.55 Outlookを操作してメールを送りたい]
[No.56 Outlookから複数のアドレスにBCC送信]
[No.57 Outlook メールアイテムの形式でハマる]
[No.58 Access クエリーを Excel既存シートへ エクスポート]
[No.59 Excelのマクロ付きフォーマットを壊されないように]
www.ken3.org(サイト内)から Google を利用して、

三流君 VBAで楽しくプログラミング(Excel/Access VBAの解説/サンプルです)
[VBA系のバックナンバー] [VBA系 TOP] [三流君 TOP]



No.55 2003/03/05
Outlookを操作してメールを送りたい
[ページTOPへ戻る]

<Outlookを操作してメールを送りたい>

こんにちは、三流プログラマーKen3です。 なんか、間があいてしまいました。 1ヶ月間、何にもしていないのに、読者が増えているのはビックリ。 そして、今日の発行でまた減って行く、、、 なんていつもの暗い前置きは置いといて、 今回は、 Outlookを操作してメールを送りたい を軽く書きたいと思います。 へぇ〜、そうなんだぁ程度に、気楽に読んでください。 /* * 1.今回のキッカケ */ In message "outlookを開いてメールを送ることはできますか?", onikoさん wrote... > IEを開いてHPを見るというのがありましたが、 >OUTLOOKを開いてメールを送るにはどうしたら >よろしいでしょうか? > > 宛先はエクセルのどこかのセルに入れておいても >構いません。 > > マガジンで紹介してください。 >急ぎませんのでよろしくお願いいたします。 ---- 急がないって言われると、変わり者だから急ぎたくなるんだよなぁ、、、 といってたけど、メールを貰ってから1ヶ月がたってたりして(笑) 1999/11/23 Excel VBA からOutLookデータを読む ( http://www.ken3.org/backno/backno_guchi05.html#22 を参照) でだいぶ前に、Excel97とOutLook97で少し遊んでました。 それを思い出しつつ、、、 /* * 2.アプリケーション名はOutlook.Application */ 下記、オブジェクト作成のテストです。 Sub test_Outlook() Dim oApp As Object Dim myNameSpace As Object Dim myFolder As Object Set oApp = CreateObject("Outlook.Application") Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub ポイントは、 Set oApp = CreateObject("Outlook.Application") で、Outlookのアプリケーションを操作するためのオブジェクトを作成してます。 Set oApp = CreateObject("Excel.Application") とよくExcelでやってるので、イメージはOKかなぁ。 私が少しハマッタのは、よく oApp.Visible = True とやっているので、これでOutlookが表示されるかと思ったらエラーが出てしまい、 あれ?って感じでした。 ヘルプを見てみると、 Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 で規定のフォルダー表示とあったので、流用しました。 フォルダー関係の操作は今後の課題かなぁ。 まぁ、なんとか起動は出来たと思います。 /* * 3.送信メールの作成をテストする */ やりたいことは、メールの作成なので、 Sub test送信メール作成() Dim oApp As Object 'アプリケーションオブジェクト Dim objMAIL As Object 'メールのオブジェクト Dim strMOJI As String '本文 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" objMAIL.To = "test@ken3.org" '宛先 objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 objMAIL.Display '途中で編集したい時(メール表示してみた) 'おまけでOutlook表示 Dim myNameSpace As Object Dim myFolder As Object Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub まず、アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") 次に、メールアイテムを作成します。 Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 本文を一時的に変数に保管して(直接でもいいですよ) strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" こんな感じで、vbCrLfの改行を使って、作成してます。 データをメールアイテムにセットする objMAIL.To = "test@ken3.org" '宛先 objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 見たまま?で、 .To 宛先 .Subject 件名 .Body 本文 をセットしてます。 objMAIL.Display '途中で編集したい時(メール表示してみた) で、メールの編集画面を表示してます。 ここで書きなおしたり出来きます。 *送信箱に行かなかったので、たまたま、発見しただけなんだけど(笑) おっと、やりたいことは送信用のメールを作成だったよね。 Sub testSEND送信() Dim oApp As Object 'アプリケーションオブジェクト Dim objMAIL As Object 'メールのオブジェクト Dim strMOJI As String '本文 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" objMAIL.To = "test@ken3.org" '宛先 objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 objMAIL.Send '直接送信箱行き 'おまけでOutlook表示 Dim myNameSpace As Object Dim myFolder As Object Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub ポイントは、 objMAIL.Send '直接送信箱行き の.Sendプロパティです。 これで送信箱に行きます。 えっ、送受信をしないと、実際に送信されないって? いきなり送りたいのかなぁ。。。 次回のネタに取って、今回はこれくらいで。 逃げるなって? バレた(笑) /* * 4.おわりの挨拶 */ 今回は、OutLookアプリの操作で、 ・アプリケーションの参照用のオブジェクト変数の作成 ・メールの新規作成 ・メールを送信箱に作成 でした。 拾い読みして、 1つでも何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- 1999/11/23 Excel VBA からOutLookデータを読む ( http://www.ken3.org/backno/backno_guchi05.html#22 を参照) で、 Excel97とOutLook97で少し遊んでました。 時間があったら、あわせてみて下さい。

No.56 2003/03/10
Outlookから複数のアドレスにBCC送信
[ページTOPへ戻る]

<Outlookから複数のアドレスにBCC送信>

こんにちは、 今月は読者減らさないようにがんばろう と 思っている。 三流プログラマーKen3です。 そして、今日の発行でまた減って行く、、 なんていつもの暗い前置きは置いといて、 今回は、 Outlookから複数のアドレスにBCC送信 を軽く書きたいと思います。 へぇ〜、そうなんだぁ程度に、気楽に読んでください。 /* * 1.今回のキッカケ */ In message "質問", 初心者 さん wrote... >EXCELからOUTLOOKを起動してEXCELシートに書かれている複数のアドレスにBCC >で送信していんですが、どうしたらいいんですか? ---- BCC送信かぁ、、、 タイトルが質問で名前が初心者かぁ。 メールの送り元はdion.ne.jpで@Hotmail.comかぁ なんて関係無いことは置いといて、 /* * 2.質問元のサンプル */ 前回と同じだけど(行数、ボリューム稼ぎしてもイミ無いから参照にしろって?) Sub testSEND送信() Dim oApp As Object 'アプリケーションオブジェクト Dim objMAIL As Object 'メールのオブジェクト Dim strMOJI As String '本文 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" objMAIL.To = "test@ken3.org" '宛先 objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 objMAIL.Send '直接送信箱行き 'おまけでOutlook表示 Dim myNameSpace As Object Dim myFolder As Object Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub まず、アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") 次に、メールアイテムを作成します。 Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 本文を一時的に変数に保管して(直接でもいいですよ) strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" こんな感じで、vbCrLfの改行を使って、作成してます。 データをメールアイテムにセットする objMAIL.To = "test@ken3.org" '宛先 objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 見たまま?で、 .To 宛先 .Subject 件名 .Body 本文 をセットしてます。 objMAIL.Send '直接送信箱行き の.Sendプロパティです。 これで送信箱に行きます。 と送信箱にメールを保存しました。 /* * 3.BCCの複数宛先指定 */ やりたいことは、BCCで複数の宛先を指定することなので、 .TOを.BCCに変えただけです。 Sub testBCC送信() Dim oApp As Object 'アプリケーションオブジェクト Dim objMAIL As Object 'メールのオブジェクト Dim strMOJI As String '本文 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" objMAIL.BCC = "test@ken3.org" 'BCCに変えただけ objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 objMAIL.Send '直接送信箱行き 'おまけでOutlook表示 Dim myNameSpace As Object Dim myFolder As Object Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub objMAIL.BCC = "test@ken3.org" 'BCCに変えただけ なんでBCCとわかったんだよ、、、直感です。 じゃなくって、.Toがそのまま宛先だったので、 勇気をもって実験したら通りました。(できました) あっ、複数の宛先にBCCだっけ、 カンマで区切ったらできるのかなぁ? よし、やってみますか '文字列で宛先を作る strBCC = "test@ken3.org, excel@ken3.org, access@ken3.org" objMAIL.BCC = strBCC '宛先の文字列を渡す あれ?エラーだよ、、 strBCC = "test@ken3.org , excel@ken3.org , access@ken3.org" と、カンマを離してみるがダメ、、、う〜ん、、、 strBCC = "test@ken3.org ; excel@ken3.org ; access@ken3.org" と、セミコロン;で区切ったらうまく行きました。 あとは、固定値だけどセルからアドレスを取ってきて、 セミコロン区切りにして、.BCCにセットすれば出来上がりかなぁ。 BCCで送信する用途に少し危険なニオイを感じるけど(未承諾広告?に使う?) こんな感じです。 Sub testBCC送信() Dim oApp As Object 'アプリケーションオブジェクト Dim objMAIL As Object 'メールのオブジェクト Dim strMOJI As String '本文 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 strMOJI = "こんにちは" & vbCrLf _ & "プログラマーの愚痴、教えまっせ?" & vbCrLf _ & "http://www.ken3.org/ よろしく(笑)" Dim strBCC As String 'BCC宛先の変数を定義 '文字列で宛先を作る strBCC = "test@ken3.org ; excel@ken3.org ; access@ken3.org" objMAIL.BCC = strBCC '宛先の文字列を渡す objMAIL.Subject = "未承諾広告※(笑)" '件名 objMAIL.Body = strMOJI '本文の代入 objMAIL.Send '直接送信箱行き 'おまけでOutlook表示 Dim myNameSpace As Object Dim myFolder As Object Set myNameSpace = oApp.GetNameSpace("MAPI") Set myFolder = myNameSpace.GetDefaultFolder(6) '規定のフォルダーを指定 myFolder.Display '表示 End Sub /* * 4.おわりの挨拶 */ 今回は、OutLookアプリの操作でBCC送信でした。 ・.TOを.BCCにして、BCCの宛先にアドレスをセット ・複数アドレスは;セミコロンで区切る でした。 拾い読みして、 1つでも何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- 2003/03/05 Outlookを操作してメールを送りたい ( http://www.ken3.org/backno/backno_vba12.html#55 を参照) で、 Outlookの起動と送信メールの作成を行いました。 1999/11/23 Excel VBA からOutLookデータを読む ( http://www.ken3.org/backno/backno_guchi05.html#22 を参照) で、 Excel97とOutLook97で少し遊んでました。 時間があったら、あわせてみて下さい。

No.57 2003/03/17
Outlook メールアイテムの形式でハマる
[ページTOPへ戻る]

<Outlook メールアイテムの形式でハマる>

こんにちは、 三流プログラマーKen3です。 今回は、 Outlookのメール形式について、 軽く書きたいと思います。 *解決してないんだけど(笑) /* * 1.今回のキッカケ */ http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi?mode=allread&no=61&page=0 >投稿時間:2003/03/13(Thu) 17:59 >投稿者名:Yonekura >タイトル:メールを送信するときのメール形式について > >三流君 VBAで楽しくプログラミング で紹介していただいたNo.55 >,No56について質問です。メールを新規作成で表示させると >メール形式がリッチテキストになってしまうのですが、これを自動 >でテキスト形式にすることってできるのでしょうか? >Windows2000でOutlook2000を使ってます。 あれ、よく見ると私の環境もテキスト形式じゃなく、 リッチテキストになってますね。 どうしてだろう? /* * 2.EditorType プロパティ */ EditorType プロパティ アイテムに対する OlEditorType クラスの定数を取得します。 OlEditorType クラスの定数は、 olEditorHTML(2)、olEditorRTF(3)、olEditorText(1)、 および olEditorWord(4) のいずれかです。 メモ MsgBox myItem.Body のように、 アイテムの Body プロパティに単にアクセスするだけでは、 EditorType プロパティは変更されません。 しかし、myItem.Body = "This is a new body" のように、 Body プロパティを設定し直すと EditorType プロパティはユーザーの既定のエディタに戻ります。 値の取得のみ可能です。 おっ、これかなぁ?エディタ関係は、 あれ、 >値の取得のみ可能です えっ、指定できないんだぁ。。。 /* * 3.送信トレイからアイテムを作成してみた */ >Outlook を使って返信する場合、既定では、受信したメッセージと同じ形式の >返信メッセージが使用されます。たとえば、テキスト形式のメッセージに対す >る返信は、テキスト形式で送信されます。返信メッセージの形式は変更するこ >とができます。メッセージの本文にカーソルを置いて [書式] メニューをクリ >ックし、目的のメッセージ形式をクリックします。 現在、 'アプリケーションオブジェクトの作成 Set oApp = CreateObject("Outlook.Application") Set objMAIL = oApp.CreateItem(0) 'olMailItem=0 と .CreateItem(0)でolMailItemを作成してました。 それじゃ、これを送信トレイからアイテム作成に変えて、 下記のようにしてみました。
Sub xxx()

    Dim myOlApp  As Object  'Outlook参照用
    Dim olMAPI   As Object  'ネームスペース
    Dim myFolder As Object  'フォルダーの頭
    Dim myMAIL   As Object  'メールアイテム
    Dim nFCNT    As Integer 'フォルダーのカウント用
    Dim strMOJI  As String  '本文

    'アプリケーション変数の作成
    Set myOlApp = CreateObject("Outlook.Application")
    Set olMAPI = myOlApp.GetNameSpace("MAPI")

    '送信トレイを探す
    For nFCNT = 1 To olMAPI.Folders(1).Folders.Count
        Debug.Print olMAPI.Folders(1).Folders(nFCNT).Name
        'フォルダー名が一致したら抜ける
        If olMAPI.Folders(1).Folders(nFCNT).Name = "送信トレイ" Then
            Set myFolder = olMAPI.Folders(1).Folders(nFCNT)
        End If
    Next nFCNT    'Debug.Print olMAPI.GetDefaultFolder(2).Name
    
    'MsgBox myFolder.Name
    
    strMOJI = "こんにちは" & vbCrLf _
            & "プログラマーの愚痴、教えまっせ?" & vbCrLf _
            & "http://www.ken3.org/ よろしく(笑)"
    
    Set myMAIL = myFolder.Items.Add  '送信トレイのアイテムを追加

    myMAIL.to = "test@ken3.org"           '宛先
    myMAIL.Subject = "未承諾広告※(笑)"  '件名

    myMAIL.display  '編集画面の表示
    'myMAIL.Save  '下書きに保存
    
    myMAIL.body = strMOJI                 '本文の代入
    'myMAIL.Send    '直接送信箱行き

End Sub
送信トレイのフォルダーを一覧から探して、 .Items.Add で、アイテムの追加後プロパティをセット。 あれ、これでもリッチテキストのままだ。。。 myMAIL.Save '下書きに保存 といったん保存してもダメだし、、、 間違って 'myMAIL.body = strMOJI '本文の代入 と.Bodyをコメントにしたら、 本文白紙でテキスト形式でメールが作成された。 う〜ん、.Body = "XXX"とセットした瞬間にリッチテキスト形式に変わってしまう。 .HtmlBodyはHTML形式になるだけだし。 /* * 4.おわりの挨拶 */ 今回は、回答編じゃなくて、いろいろとやった試行錯誤でした。 試行錯誤で、 ・フォルダー(トレイ)の探し方 ・.Saveで下書きへ ・.HtmlBodyでHTML形式のメールを作成できる なんてわかったけど、本題ができてない。。。 拾い読みして、 1つでも何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- 2003/03/10 Outlookから複数のアドレスにBCC送信 ( http://www.ken3.org/backno/backno_vba12.html#56 を参照) で、 ;で区切って複数の宛先(BCC送信)を作成しました。 2003/03/05 Outlookを操作してメールを送りたい ( http://www.ken3.org/backno/backno_vba12.html#55 を参照) で、 Outlookの起動と送信メールの作成を行いました。 1999/11/23 Excel VBA からOutLookデータを読む ( http://www.ken3.org/backno/backno_guchi05.html#22 を参照) で、 Excel97とOutLook97で少し遊んでました。 時間があったら、あわせてみて下さい。 -------------------- 読者からのお便り紹介 -------------------- http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi?mode=allread&no=63&page=0 >投稿時間:2003/03/14(Fri) 08:47 >投稿者名:YN >タイトル:計算実行 > >ftpでファイルの移動、rshで長い計算を交互に実行しています。その1つ1つの >コマンドが終わったら次のコマンドへ進む方法はないでしょうか?今まではフラ >グを立てる方法を取っていましたが、言語的に何かありますか。普通の四則演算 >とかは順番どおり次々とやっても気になりませんが・・・ --- 何かアイデアや案を持っている人は、 http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi?mode=allread&no=63&page=0 に書き込んでください。 終了まで待ちたい、、、よくある話のような。

No.58 2003/03/18
Access クエリーを Excel既存シートへ エクスポート
[ページTOPへ戻る]

<Access クエリーを Excel既存シートへ エクスポート>

こんにちは、 三流プログラマーKen3です。 今回は、 Access クエリーを Excel既存シートへ エクスポート を軽く書きたいと思います。 /* * 1.今回のキッカケ */ BBS に以下の投稿がありました。 ( http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi?mode=allread&no=66&page=0 ) >投稿時間:2003/03/18(Tue) 09:11 > >おなまえ:TMNOA >タイトル:アクセスからエクセルへ > >始めまして、TMNOAです。 >いつも読ませて頂いております。 > >質問なのですが、アクセスのクエリーからエクセルへのエクスポートの際、 >概存ブック及び概存シートへのエクスポートは可能なのでしょうか? >現在の所、概存ブックの新規シートへのエクスポートはできるのですが・・・。 > >お忙しいとは思いますが、よろしくお願い致します。 >(他のサイトは説明が難しく困っているので・・・。) > >では、失礼します。 -------------------------------------------------------- と質問メールが来ました。 私の説明もわかりにくいと評判なんだけど、、、少し調べてみますか。 /* * 2.簡単にできないか、確認してみる。 */ 昔の自分のメルマガサンプルを見てみると、 <Access97からExcel形式へExport時に書式設定を行いたい> http://www.ken3.org/backno/hosoku/e025/index.html で、 DoCmd.TransferSpreadsheet acExport, 5, "管理MST", "C:\TEST.XLS", True, "" <書式付きエクスポート DoCmd.OutputToで、できます> http://www.ken3.org/backno/hosoku/ETC_026.html で、 DoCmd.OutputTo acOutputTable, "T_管理MST", acFormatXLS, "C:\TEST.xls", True を使ってました。 簡単なのは、DoCmd.XXXXが使えるといいんだけどなぁ、 クエリーの出力を試してみました。(Access97) spreadsheettype を8 Excel97に変えてと 0 acSpreadsheetTypeExcel3 (既定値) 6 acSpreadsheetTypeExcel4 5 acSpreadsheetTypeExcel5 5 acSpreadsheetTypeExcel7 8 acSpreadsheetTypeExcel97
Sub コマンド0_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "Q_VBADATA", "D:\058.XLS", True, ""
    MsgBox "出力終了"
End Sub
まず、1回目ファイルがない新規の状態で実行。 シートが作成されます。(あたりまえか(笑)) さてと、行挿入・列挿入してタイトルとか入れときますか。 ついでにデータ消してみてと。見出しに色付けて。 シートも挿入してしまえ。。。とブックの形を変えて保存します。 ブックを閉じたことを確認後、再度ボタンを押して実行します。 ブックを開いてみるとあら不思議、 データが入っているよ。 DoCmd.TransferSpreadsheet を使うと出きるみたいですね。 /* * 3.データエリアの名前付けを利用しているのでは?と勝手に予想するが、、、 */ 勝手に一人で盛り上がってないで、 何でか?理由を説明しろ?(想像しろって?) データの転送先のセルに付けた名前をみると、 Q_VBADATAってのができてます。 えっ、どこどこ? メニューの挿入・名前・定義を選択します。 そうすると、範囲が書いてあります。 既存のブックで範囲が設定されていれば、そこに保存するみたいです。 とすると、今在るブックにデータを挿入したかったら、 あたらしくクエリー名と同じ名前の定義を追加すればいいの? Q_ASPDATAって名前で範囲を追加してみます。 メニューの挿入・名前・定義を選択します。 名前と範囲を入れて追加ボタンを押します。 で保存してと。
Sub コマンド1_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "Q_ASPDATA", "D:\058.XLS", True, ""
    MsgBox "出力終了"
End Sub
クエリー名を変えて、実行。 よし今日もお疲れさん。 エラーだよ三流君、、、えっ、なんで?ほんとだぁ。。。 それじゃダメなのかなぁ、、 シートを消して、再実行。 すると、ブック内にQ_ASPDATAのシートが作成された。 同様にタイトルなどの飾りを付けて、 確認用にデータを少し消してから保存。 再度実行する。 あらら、ダメかぁ。 名前の定義でそこにセットされるなら、 既存シートのセットも簡単かなぁと思ったのですが、あと一歩です。 1つのブックに一つのクエリーはエラー無く名前の範囲に出力されるので、 一度ダミーでシート作成、その後タイトルやワクを作成して使ってみてください。 *複数クエリーを既存シートは工夫が必要みたいです。  (もしかしたら私の操作ミスかも) 一度シート作成後、他のシートをシートのコピーで持ってくるとか。 /* * 4.おわりの挨拶 */ 今回も途中までは出来たのになぁ、、、、 何か単純なミスがありそうだけど。 拾い読みして、 1つでも何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- 昔のサンプル、 <Access97からExcel形式へExport時に書式設定を行いたい> http://www.ken3.org/backno/hosoku/e025/index.html 時間があったら、あわせてみて下さい。

No.59 2003/03/19
Excelのマクロ付きフォーマットを壊されないように
[ページTOPへ戻る]

<Excelのマクロ付きフォーマットを壊されないように>

こんにちは、 三流プログラマーKen3です。 今回は、 Excelのマクロ付きフォーマットを壊されないように を軽く書きたいと思います。 今回の関連画像は、手抜きで無しです(オイオイ)

/* * 1.今回のキッカケ */

前回の続きなんだけど、 2003/03/18 Access クエリーを Excel既存シートへ エクスポート ( http://www.ken3.org/backno/backno_vba12.html#58 を参照) で、 悪戦苦闘してました。 BBS に以下の投稿がありました。 ( http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi?mode=allread&no=66&page=0 ) >投稿時間:2003/03/19(Wed) 01:38 >投稿者名:TMNOA >タイトル:Re: DoCmd.TransferSpreadsheet acExport でできそうなのですが... > >KEN3 様 > >返信ありがとうございました。 >人生初めてのBBSの使用で少し緊張していたのですが・・・。 > >すみませんが、現状をもう少し説明させてください。 > >1、ACCESSのクエリーより(日付・数量)をEXCELにエクスポートしま >す。(既存ブック・新規シート) > >2、既存ブック・新規シートにあるデータを同じ既存ブックの既存シート(カレン >ダー形式の雛形)にIFVOOKUP関数を使用して、転送 > >3、雛形自体をプリントアウトして、データを保存せずにEXCELを閉じる。 > >この状況で、データを保存しないと2番のIFVOOKUP関数の数式が壊れてしま >い2回目以降使用できません。 > >そこで昨日お話させていただいたのですが、アクセス・クエリーよりエクセルの既存 >ブック・既存シートの雛形(カレンダー雛形)にエクスポートしたいのですが・・・。 > >すみません、お手間を取らせて。 >何か案がありましたら、よろしくお願い致します。 -------------------------------------------------------- と書き込みがありました。 さて、どうしましょう? なんて言って無いで、 過去の記憶を(経験から)似たような事例を頭に思い浮かべると あっ、あれがいいかなぁと1つ浮かんだので、メルマガに載せます。

/* * 2.苦い経験、Excelのマクロ付きフォーマットを壊された */

その昔、AccessのデータからExcelの雛形にデータをセットしてました。 私の場合は、関数が壊れたとかじゃなくて、 Excelなので、担当者がいろいろとフォーマットをイジって上書き保存してしまい、 処理が動かなくなったなんてことがありました。 >3、雛形自体をプリントアウトして、データを保存せずにEXCELを閉じる。 > >この状況で、データを保存しないと2番のIFVOOKUP関数の数式が壊れてしま >い2回目以降使用できません。 いつものクセで文章を逆に読むと、(嫌いじゃない=好き、とは言えないけど何の話?) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ データを保存すると、数式は壊れない? でも保存すると2回目以降不具合がある? なんて勝手に想像しました。 私がやってた処理は(対策は)、 Excelの雛形にデータをセット後に、 別名で保存してあとはお好きなように的な処理にしました。 なんて書いても?なので、実際の処理を載せます。 ファイルの構成 (V:が共有ネットワークドライブです) C:\MDB\別荘管理.mdb V:\DATA\MAKE_水道台帳.xls (これが雛形) V:\DATA\PRINT.CSV (クエリーをCSVにした中間データ) V:\水道台帳.xls (別名にした結果のファイル) ________ 処理概要 ^^^^^^^^ Accessの管理データから抽出された情報をCSVに保存します。 DoCmd.TransferText acExportDelim, "", "Q_CSV_水道台帳", "V:\DATA\PRINT.CSV" ExcelをCreateObject("Excel.Application")で起動 雛形を開く oApp.workbooks.Open "V:\DATA\MAKE_水道台帳.xls" 'フォーマットを開く データセット処理 oApp.Run ("MAIN") 'Excel側のマクロを起動 ここからExcel側で、 Call READ_DATA 'CSVからデータ読みこみ Call MAKE_DATA 'データをセット でポイントが下記の、 'フォーマット破壊防止 and 作業者が使いやすいように共通V:のルートへ ActiveWorkbook.SaveAs FileName:="V:\水道台帳.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False と、共通ドライブのルートに別名にして保存しました。 *雛形は保存されてないので、2回目の処理も動くし、  作業者がシートをいじっても、大丈夫かなぁ。 _______________________________ Access側の処理 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub B_水道料台帳_Click()

    Dim oApp As Object
    Dim W_MIDASHI As String

    W_MIDASHI = "MIDASHI"

    DoCmd.TransferText acExportDelim, "", "Q_CSV_水道台帳", "V:\DATA\PRINT.CSV", True, ""

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    oApp.UserControl = True
    
    oApp.workbooks.Open "V:\DATA\MAKE_水道台帳.xls"  'フォーマットを開く
    oApp.sheets("MIDASHI").Cells(1, 1) = Me![SEL_別荘].Column(0)
    oApp.sheets("MIDASHI").Cells(2, 1) = Me![SEL_別荘].Column(0)

    oApp.Run ("MAIN")  'Excel側のマクロを起動

End Sub
__________________________________________ Excel側の処理 V:\DATA\MAKE_水道台帳.xls ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub MAIN()

    Application.DisplayAlerts = False
    Application.Caption = "水道料入金台帳"

    Sheets("HYO").Select
    Range("A1").Select

    Call READ_DATA
    Call MAKE_DATA

    'フォーマット破壊防止 and 作業者が使いやすいように共通V:のルートへ
    ActiveWorkbook.SaveAs FileName:="V:\水道台帳.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    
    MsgBox "データ作成終了、B5用紙をセット後、印刷してください。"

End Sub
Sub READ_DATA()
'
' Macro1 Macro
' 記録日 : 1998/2/17  
'

    Workbooks.Open FileName:="V:\DATA\PRINT.CSV"
    Cells.Select
    Selection.Copy
    
    Windows("MAKE_水道台帳.xls").Activate
    Sheets("DATA").Select
    Cells.Select
    ActiveSheet.Paste
    
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy

    Windows("PRINT.CSV").Activate
    Range("A1").Select
    ActiveWorkbook.Close
    
End Sub
Sub MAKE_DATA()

    Dim W_FURIGANA

    W_FURIGANA = Left(Sheets("DATA").Cells(2, 8), 1)

    Sheets("HYO").Select
    Range("A1").Select

    'データ数を数える
    Y_LINE = 2
    While 0 < Len(Sheets("DATA").Cells(Y_LINE, 2))
        Y_LINE = Y_LINE + 1
    Wend

    'コピー先にデータを挿入する
    Sheets("HYO").Select
    Rows("4:4").Copy
    Rows("5:" & LTrim(Str(5 + Y_LINE))).Select
    Selection.Insert Shift:=xlDown
    
    'データをセットする
    Y_LINE = 2
    YY = 4
    While 0 < Len(Sheets("DATA").Cells(Y_LINE, 2))
        'セットするセルに移動する
        Cells(YY, 1).Select
        
        '滞納有無
        If Sheets("DATA").Cells(Y_LINE, 12) > 1 Then
            Cells(YY, 1) = "滞"
        Else
            Cells(YY, 1) = " "
        End If
        
        '氏名 印刷エリアが小さいので省略形にする。まったく追加処理だよ
        W_NAME = Sheets("DATA").Cells(Y_LINE, 3)
        W_MAE = Left(W_NAME, 4)
        W_ATO = Right(W_NAME, 4)
        If W_MAE = "株式会社" Then
            W_NAME = "(株)" & Mid(W_NAME, 5, 30)
        End If
        If W_MAE = "有限会社" Then
            W_NAME = "(有)" & Mid(W_NAME, 5, 30)
        End If
        If W_ATO = "株式会社" Then
            W_LEN = InStr(W_NAME, "株式会社")
            W_NAME = Mid(W_NAME, 1, W_LEN - 1) & "(株)"
        End If
        If W_ATO = "有限会社" Then
            W_LEN = InStr(W_NAME, "有限会社")
            W_NAME = Mid(W_NAME, 1, W_LEN - 1) & "(有)"
        End If
        
        Cells(YY, 4) = W_NAME
        
        '改ページのチェック 1998/06/29 追加、グループ化しろって?やんなるね
        If W_FURIGANA <> Left(Sheets("DATA").Cells(Y_LINE, 8), 1) Then
            W_FURIGANA = Left(Sheets("DATA").Cells(Y_LINE, 8), 1)
            KUGIRI = LTrim(Str(YY))
            Rows(KUGIRI & ":" & KUGIRI).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        End If
        
        '号地
        Cells(YY, 5) = Mid(Sheets("DATA").Cells(Y_LINE, 2), 2, 1)
        Cells(YY, 6) = Mid(Sheets("DATA").Cells(Y_LINE, 2), 4, 6)
        '地積
        Cells(YY, 7) = Sheets("DATA").Cells(Y_LINE, 5)
        
        '建物管理費
        W_KAZU = Sheets("DATA").Cells(Y_LINE, 6)
        Cells(YY, 8) = Format(Int(W_KAZU / 1000), "##")
        If Val(Cells(YY, 8) & "") > 0 Then
            Cells(YY, 9) = Format((W_KAZU Mod 1000), "000")
        Else
            Cells(YY, 9) = Format((W_KAZU Mod 1000), "###")
        End If
        
        '他の年度にも同様の数値をセットする
        Cells(YY, 11) = Cells(YY, 8)
        Cells(YY, 12) = Cells(YY, 9)
        Cells(YY, 14) = Cells(YY, 8)
        Cells(YY, 15) = Cells(YY, 9)
    
        'カウントアップ
        Y_LINE = Y_LINE + 1
        YY = YY + 1
    Wend


    '保存する
    Range("A1").Select
    'ActiveWorkbook.Save '上のルーチンで別名保存するので
    'もうフォーマット壊すなよなぁ
    'VBAいじれる社員が来たらどうしましょう?
    'ここみてたら、一緒にがんばろうよ。変更多いよここ。

End Sub

/* * 3.実際にあった私のお馬鹿な失敗談(笑) */

まぁ、そんな感じで、フォーマットの破壊は発生しなくなりました。 で、余談の笑い話があって、 EXCEL側のVBA修正で、テストデータがほしかったので、 Accessからボタンを押してExcelファイル作成。 あとはExcelの世界で実行、テストを繰り返し、完成、保存。 もうオチがわかってしまった? Accessからこの処理でファイルを作成すると、 V:\DATA\MAKE_水道台帳.xls (雛形) を開いて、 V:\水道台帳.xls (別名の結果ファイル)を作成。 私はなんにも気にしないで、別名の水道台帳.xls内のVBAを修正してた。 で、完成、最終段階のAccessから連動起動の動作チェックをする。 あれ?もとのままだ。 あっ!!!!! ~~~~~~~~~~~~~~ とかなりのショックを受けた記憶が。。。。 *V:\DATA\MAKE_水道台帳.xls (雛形)を修正しないとね、、、(笑) 皆さんは同様の失敗をしないように(笑) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ *面白い失敗談、聞いてほしい話などあったら、  掲示板 http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi  へ書き込んでください。  こんな変な先輩居たよ、、と先輩の話でもOKです。

/* * 4.おわりの挨拶 */

今回は、 ・Excel雛形ファイルを壊されないように別名で保存 ・Ken3のお馬鹿な失敗談、アナタは雛形ファイルに修正しないと でした。 拾い読みして、 1つでも何かの参考となれば幸いです。 Excel/Access大好き、三流プログラマーKen3でした。 ---------------------------------- 関連項目(ただのバックナンバー紹介) ---------------------------------- [No.2 AccessからExcel出力] http://www.ken3.org/backno/backno_vba01.html#2 [No.7 MDBと同フォルダのExcelファイルを開く] http://www.ken3.org/backno/backno_vba02.html#7 [No.14 Excel 隣のシートを参照して逃げる] http://www.ken3.org/backno/backno_vba03.html#14 [No.48 AccessからExcelブックを開き、書式設定を行う] http://www.ken3.org/backno/backno_vba10.html#48 [No.54 AccessからExcel開いて、処理終了後に閉じたい] http://www.ken3.org/backno/backno_vba11.html#54 [No.58 Access クエリーを Excel既存シートへ エクスポート]</A><br> http://www.ken3.org/backno/backno_vba12.html#58


検索して目的の情報を探す。

目的の情報を探すには、最近はググれとよく聞きます。なので、検索ボックスを付けました。
いろいろなキーワードを入れて、検索してみてください。

カスタム検索
三流君(site:www.ken3.org) 内を Googleを利用してキーワード する

ページフッター

ここまで、読んでいただきどうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか?少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。

質問や要望など メッセージを送る(三流君に連絡する)

質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。
あなたのお名前(ニックネーム):さん
返信は?: 不用(HP更新を待つ) , E-mail→ アドレス:に返事をもらいたい



(感想や質問・要望 メッセージはHPで記事に載せることがあります。)

急ぎで連絡がほしい、そんな時は:[三流君連絡先]に連絡してください。

リンクや広告など

項目別に↓に人気の記事をまとめてみました。お探しのジャンルを選択してください。
人気記事(来場者が多いTOP3):
[VBAでIE,WebBrowserを操作]・・・VBAでIE,WebBrowserを操作する サンプルです
[Access から Excel 連携 CreateObject("Excel.Application")]・・・AccessからExcelを操作したりデータの書き出しなどです
[VBAでOutlookの操作 CreateObject("Outlook.Application" )]・・・VBAからOutlookを使い、メール関係を処理するサンプルです
↑上記3つみたいなCreateObjectで他のアプリケーションを操作するサンプルが人気です。

開発時の操作: [F1を押してHELPを見る]/ [Debug.Print と イミディエイトウインドウ]/ [実行時エラーでデバッグ]/ [ウォッチ式とSTOP]/ [参照設定を行う]

仕様書(設計書?) XXXX書類: [基本設計書や要求仕様書]/ [テスト仕様書 テストデータ]/ [バグ票]/ [関数仕様書]/ [流れは 入力・処理・出力]

Excel関係:
[Excel UserFormを操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
[ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
[Excel関係 関数、その他]・・・その他Excel関係です

Access関係:
[Access UserForm/サブフォーム 操作]・・・アクセスでフォームを使ったサンプルです
[Access レポート操作]・・・レポートを操作してみました
[Access クエリーやその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です

その他:VBAの共通関数やテキストファイルの操作など
[VBAでテキストファイル(TextFile)の操作]・・・普通のテキストファイルを使ったサンプルです
[VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます

Blog:[三流君の作業日記]/ [サンプルコードのゴミ箱]/ 広告-[通販人気商品の足跡]



[三流君(TOP ken3.org へ戻る)] / [VBA系TOPへ] / [VBA系バックナンバー目次へ移動]