QA@IT

TextファイルをExcel出力する際の書式設定(OpenText使用)

4596 PV

初投稿です。宜しくお願いします。

  • 開発環境:Visual Studio 2005
  • 言語:Visual Basic 2005
  • Excel:Microsoft Excel 11.0(Excel2003)

Tab区切りで格納されているTextファイルを
[Microsoft.Office.Interop.Excel.Workbooks]の[OpenText]メソッドを使用して
Excelに変換しようとしています。
その際、[OpenText]の[FieldInfo]パラメータを指定して、列毎に書式を指定したいです。

以下、現在の状態です。
(COMオブジェクトの開放については省略)

Private Sub OpenTextFile()
    Dim xlsApp As Excel.Application = Nothing
    Dim xlsWorkBooks as Excel.WorkBooks = Nothing
    Dim xlsBook As Excel.Workbook = Nothing

    xlsApp = CreateObject("Excel.Application")
    xlsApp.DisplayAlerts = False
    xlsApp.Visible = False

    xlsWorkBooks = xlsApp.Workbooks
    xlsWorkBooks.OpenText(Filename:=[読み込むTextFilePath], _
                          DataType:=Excel.XlTextParsingType.xlDelimited, _
                          TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
                          Tab:=True, _
                          FieldInfo:=New Object() {New Object() {1, 1}, New Object() {2, 2}, New Object() {3, 9}, New Object() {4, 5}})

    xlsBook = xlsWorkBooks.Item(1)
    Call xlsBook.SaveAs([保存するExcelFilePath])
End Sub

結果、3列目は正しく省略されるのですが、
2列目の文字列・4列目の日付(YMD)の書式が正しく設定されません。

[OpenText]メソッドを使用して、正しく文字列の書式を設定するには
どのようにすれば宜しいのでしょうか?

[OpenText]での書式設定が不可能なのでしたら、
諦めてRangeの[NumberFormatLocal]プロパティを指定した後に
値を貼り付ける方法に変更する予定です。(時間掛かるが、他に方法がない為)

よろしくお願いいたします。

回答

VisualBasic2005というよりはExcel VBAの処理が
分かればなんとかなりそうですね。

Excel2010でマクロ記録した内容です

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;<取込むファイルの名前>", Destination:=Range("$A$1"))
        .Name = "<ファイル名から作成された名前>"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

希望の動作をさせる為のポイントは TextFileColumnDataTypes の設定のところに
あると思います。この例では最初が日付、後が文字列型を指定してみました。
Excelバージョンの違いがあるかもしれないので手元の環境でマクロ記録をしてみることを
奨めます。

編集 履歴 (0)
  • 回答ありがとうございます。

    [QueryTables]メソッドで処理を行いますと
    その後も取込むファイルとのデータ連携が行われてしまうため、
    取込んだTextFileを削除してしまうと上手く表示されなくなってしまいます。

    ただ、取込んだ外部データのリンク情報を削除すれば
    実現も出来そうなので試してみたいと思います。

    -
ウォッチ

この質問への回答やコメントをメールでお知らせします。