LoginSignup
3
3

More than 3 years have passed since last update.

2次元配列をCSVで書き出し(UTF8形式)VBA

Posted at

タイトルの通りのVBAサンプルです(^ ^)

'------------------------------------------------------------------------------
2次元配列をUTF8形式のCSVで書き出しのテスト用コード
'------------------------------------------------------------------------------
Sub testExportCSVutf8()
    'CSVに書きだす2次元配列生成(テストデータ生成)
    Dim TextArry(10, 8) As String
    For i = 0 To UBound(TextArry, 2)
        For j = 0 To UBound(TextArry, 1)
            TextArry(j, i) = "<" & j & ">" & "(" & i & ")"
        Next
    Next

    'UTF8で書き出し
    Call ExportCSVutf8(TextArry, ActiveWorkbook.Path & "\data_utf8.csv")
End Sub

'------------------------------------------------------------------------------
' 2次元配列をUTF8形式のCSVで書き出し
' ※参照設定で Microsoft ActiveX Data Objects x.x Library にチェックを入れること
'------------------------------------------------------------------------------
Sub ExportCSVutf8(ByRef iTextArry2D() As String, ByVal iExportFileName As String)
    'ADODB.Streamオブジェクトを生成
    Dim AdoDB As Object
    Set AdoDB = CreateObject("ADODB.Stream")
    '1行分のテキストを入れるバッファ
    Dim LineText As String

    With AdoDB
        .Charset = "UTF-8"      '文字コード指定
        .LineSeparator = adCRLF '改行コード指定
        .Open                   '書き込むCSVファイルオープン

        For r = LBound(iTextArry2D, 1) To UBound(iTextArry2D, 1)
            '1行バッファをクリア
            LineText = ""

            '1行分のテキスト生成(カンマで連結)
            For c = LBound(iTextArry2D, 2) To UBound(iTextArry2D, 2)
                LineText = LineText & iTextArry2D(r, c) & ","
            Next

            '1行分書き出し
            .WriteText LineText, adWriteLine
        Next

        '上書き保存
        .SaveToFile iExportFileName, adSaveCreateOverWrite
        '閉じる
        .Close
    End With
End Sub
3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
3