在Excel 2007中自动导入文本

问题描述

我正在尝试使用VBA编写Excel宏，以自动将CSV文本导入电子表格，但我从未做过.我需要确保每次出现的文本导入向导都以相同的方式运行.我需要采取的步骤是:

I'm trying to write an Excel macro using VBA to automate importing CSV text into a spreadsheet but I've never done it before. I need to make sure that the Text Import Wizard that comes up is run through the same way each time. The steps I need to take are:

1. 使用打开文件对话框打开文件
2. 将类型设置为定界
3. 将定界符设置为逗号
4. 设置所有要导入为文本的列
5. 自动适合所有列

我似乎无法浏览显示如何执行这些操作(例如打开文件)的文档.甚至能够从那里开始也会很有帮助.

I can't seem to wade through the documentation that shows how to do these things like open files. Even being able to start there would be helpful.

推荐答案

我最终在使用该功能之前对其进行了一些调整.

I ended up making some tweaks to the function before putting it into use.

Public Sub OpenCsv()
    ' I don't expect any more columns than 256 in my environment, so I can 
    ' just fill this array and call it done.
    Dim columnFormats(0 To 255) As Integer
    For i = 0 To 255
        columnFormats(i) = xlTextFormat
    Next i

    Dim filename As Variant
    filename = Application.GetOpenFilename("All Files (*.*),*.*", 1, "Open", "", False)
    ' If user clicks Cancel, stop.
    If (filename = False) Then
        Exit Sub
    End If

    Dim ws As Excel.Worksheet
    Application.Workbooks.Add
    Set ws = Excel.ActiveSheet
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True


    With ws.QueryTables.Add("TEXT;" & filename, ws.Cells(1, 1))
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileCommaDelimiter = True
        ''// This array will need as many entries as there will be columns:
        .TextFileColumnDataTypes = columnFormats
        .Refresh
    End With
End Sub

感谢上述人员帮助我前进.

Thanks to the above guys for getting me going.

这篇关于在Excel 2007中自动导入文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！