Excelの最近のブログ記事

Excel VBAで、Writeステートメントを使ってCSV出力すると、日付が #2008-01-01# のような形式になってしまいます。
このCSVをAccessにインポートするのは大変です。
以下のような自作関数を使って変換しましょう。

Excel側でエクスポートする際に変換してもいいし、Access側でインポートした後にクエリで変換してもいいです。

Function sanitizeDate(str As Variant) As Variant 
    If Left(str, 2) = "#2" And Right(str, 1) = "#" And Len(str) = 12 Then
        sanitizeDate = Mid(str, 2, 10)
    Else
        sanitizeDate = str
    End If
End Function

使い方

Sub test()
   MsgBox sanitizeDate("#2008-01-01#")
End Sub

実行結果：　2008-01-01

ExcelやPowerPointやWordでVBAを実行するときに、マクロの実行時間を測定する方法。

ストップウォッチが手元にないときはこのコードを使いましょう。

Sub macro1()

    Dim t

    t = Timer

    ・・・あれこれ処理・・・

    MsgBox Timer - t

End Sub

解説

最初に　t = Timerで、マクロ開始時刻が記録されます。
最後に、Timer - T　と引き算することで、マクロの経過時間(秒)が得られます。

コードのベンチマークをやるときに便利ですよ。

マクロを使って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は書いた方が便利です。