VBA: 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は書いた方が便利です。

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

PowerPoint VBAの使い方

パワーポイントで、文字がテキストボックスからはみ出しているときに、強制的にフォントサイズを小さくしてボックス内に収めたいことがあります。 上の図の場合、文字がはみ出して２段になってしまっています。(自動折り返しの場合）
これを１段に収めるには、フォントサイズを小さくさいないといけません。
対象ファイルがいっぱいある場合は、手作業でやると時間がかかって大変ですよね。

そんなときこそVBAで自動化しましょう。

Sub Macro1()
    
    With ActivePresentation.Slides(1).Shapes("Text Box 1").TextFrame.TextRange
    
            Do While .BoundHeight > 20  ' テキストボックスの高さが20以上の場合は２段だと判断
             .Font.Size = .Font.Size - 1
            Loop

      End With

End Sub

shapes("Text Box1")の部分は、ファイルによって違いますので、「マクロの記録」機能を使って調べましょう。

さて、このコードを実行すると↓こうなります。

ちゃんと１段に収まってくれました。

テキストボックスの横方向にはみ出す場合

テキストボックスでを文字を折り返す設定にしていない場合は、横にはみ出します。



この場合は、こう書きます。↓

Sub Macro1()
    
    With ActivePresentation.Slides(1).Shapes("Text Box 1").TextFrame.TextRange
    
            Do While .BoundHeight > 500  ' 横幅500ポイントに収まるまで、フォントを縮小
             .Font.Size = .Font.Size - 1
            Loop

      End With

End Sub

500という数字を適当に変えて実行してみてください。

ちゃんと収まってくれました。

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"に変換する方法

上記のように書くと、「それって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からデータベース上のデータを操作することができます。