Excel: 2008年5月アーカイブ

マクロを使ってCSVでファイル保存をしたときなどに、下記のような確認・警告メッセージがでて、いちいち「はい」を押さないと先に進まないことがあります。

これを非表示にするには"Application.DisplayAlerts = False "と書きます。

Sub macro1()


    Application.DisplayAlerts = False

    

    .....　(中略)  ....

    

    Application.DisplayAlerts = True

End Sub

このように、コードの最初に　" Application.DisplayAlerts = False　" とすることで警告メッセージを非表示にできます。



また、コードが終了する直前に　　" Application.DisplayAlerts = True　" とすることで、もとの警告表示モードに戻すことができます。
(これをしないまま終了すると、警告がずっと非表示のままになってしまいます。)

エクセルシートをタブ区切りtxt形式でエクスポートするマクロ。
書き方は前回の記事(CSVで保存)とほぼ同じです。

Sub saveAsText()


    Application.DisplayAlerts = False

    

    Sheets("Sheet1").Copy

    ActiveWorkbook.SaveAs Filename:= "C:\test\test.txt" _

        , FileFormat:=xlText

    ActiveWindow.Close

    

    Application.DisplayAlerts = True

End Sub

カンマ区切りかタブ区切りか

ところでカンマ区切りcsvとタブ区切りtxt、どちらを選べばよいのでしょうか？

もしこの保存したデータを、PerlやPHPなどのプログラミング言語で扱う可能性があれば、タブ区切りの方が扱いやすいという意見があります。
また、後でテキストエディタなどで開いて加工する場合も、タブ区切りの方が便利です。

逆にエクセル上でインポートしたりエクスポートしたりするだけであれば、カンマ区切りcsvの方が便利かもしれません。

エクセルシートをCSV形式でエクスポートするマクロ。

Sub saveAsCSV()


    Application.DisplayAlerts = False

    

    Sheets("Sheet1").Copy

    ActiveWorkbook.SaveAs Filename:="C:\test\test.csv", _

        FileFormat:=xlCSV

    ActiveWindow.Close

    

    Application.DisplayAlerts = True

End Sub

最初と最後の Application.DisplayAlerts は無しでも動きます。
ですが、これがないと下記のような警告ダイアログが2回も出て邪魔なので、 Application.DisplayAlertsは書いた方が便利です。

前回に続き、WSH(VBS)からAccessのクエリ（削除クエリや更新クエリ)を実行する方法を紹介します。





←実行したいアクションクエリ

VBSのコード

Option Explicit

Dim cn

Dim rs

Set cn= CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:￥～￥ファイル名.mdb;"

rs.Open "Q_del", cn

rs.Open "Q_add", cn

cn.Close

Set rs = Nothing

Set cn = Nothing

MsgBox "完了しますた。"

Excel VBAからAccessのクエリ（削除クエリや更新クエリ)を実行したい場合があります。
その場合も、前回の記事と同様にADOを使うと簡単に実現できます。

←実行したいアクションクエリ
Excel VBA側でのコード

Sub macro1()
    
    Dim myProvider As String
    Dim mySrc As String
    
    mySrc = "Data Source=C:￥～￥アクセスのファイル名.mdb;"
    myProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.Open myProvider & mySrc
    
    rs.Open "Q_del", cn
 
    rs.Open "Q_add", cn

    Set rs = Nothing
    cn.Close
    Set cn = Nothing
     
End Sub

VBAでゼロパディング（1→001に変換するやつ）のやり方を紹介します。
Excel, Access, Powerpoint等、Office製品の全てで共通に使えます。

１　を　００１に変換してみる

Sub test()

    MsgBox Right(1 + 10 ^ 3, 3)

End Sub

実行結果

一般に、任意の桁でゼロ埋めしたいとき

ゼロパディング関数を作ります。

Function padZero(n As Integer, keta As Integer)
    padZero = Right(n + 10 ^ keta, keta)
End Function

関数を呼び出すときは、

Sub test()
  MsgBox padZero(1, 4)  ' 1 →　0001 に変換
End Sub

などと書きます。

考え方は、前回紹介したExcelのゼロパディングと全く同じです。
[Excel] "1"を"001"に変換する方法

Excelで、1という数字を"001"という文字列に変換したいことがよくあります。
専門的には、ゼロ埋めとかゼロパディングとか言います。

実は簡単です。

1. A1セルに、"1"と入力
2. 隣のB1セルに、次の数式を入力

=Right(A1+1000,3)

これで、Ｂ１セルに"001"という文字列が生成されます。

解説

仕組みはこうです。

• まず、1に1000を足して"1001"という数字を作る
• "1001"の右３文字をちょんぎって取り出す。(Right関数)
• "001"という文字列ができる。

４桁にしたい場合、例えば"0001"としたいときは、こうです。

= Right ( A1 + 10000 , 4 )

こう書くこともできます。

= Right ( A1 + 10^4 ,4 )

９桁にしたい場合はこう。

= Right ( A1 + 10^9 ,9 )

追記

text関数を使ったこんなやり方もあるみたいです。

上記のように書くと、「それってAccessのエクスポート機能を使えばいいじゃん」と思われるかもしれません。
たしかにAccessを開いて、テーブルやクエリをエクセル形式でエクスポートするやり方もあります。

しかし、Excelファイルを開いた上で、そこからAccessのデータを取りに行くというのが必要なときもあります。
Excel VBAからAccessのデータを呼び出す方法、と言った方がわかりやすいかもしれません。
ADO(ActiveX Data Object)という仕組みを使うと、これを実現できます。

ADOを使う準備

ExcelのVBE(Visual Basic Editor)の画面で、ADOの参照設定をします。

Excel VBAコードを書く

下記のようにマクロを書きます。

Sub get_data()
    
    Dim myPath As String
    Dim myProvider As String
    Dim mySrc As String
    
    myPath = "C:\～\サンプル.mdb"
    myProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    mySrc = "Data Source=" & myPath & ";"
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    cn.Open myProvider & mySrc
    rs.Open "テーブル名またはクエリ名", cn
    
    Range("a1").CopyFromRecordset rs
     
End Sub

マクロを実行すると、Excel上にAccessのデータが展開されます。

ADOとは何か？

ADOというものが一体何なのか、最初はよくわからないと思います。
そいういう場合は、無理に理解する必要はなく、上記のようにやるとVBAからAccessのデータをいじれるとだけ覚えておけばよいと思います。

感覚的には、FSO( File System Object )と似ているかもしれません。
FSOを使うと、VBAからＰＣ上のフォルダやファイルを操作することができます。
同じように、ADOを使うと、VBAからデータベース上のデータを操作することができます。