在Excel 2007中自动导入文本

Question

我想写一个使用VBA的Excel宏来自动将CSV文本导入到电子表格中，但我从来没有做过。我需要确保每次出现的文本导入向导都以相同的方式运行。我需要采取的步骤是：

1. 打开一个文件，使用打开文件对话框
2. 类型设置为分隔
3. 设置分隔符以逗号
4. 设置要作为文本导入
所有列
5. 自动适合所有列

我似乎无法通过显示如何做这些事情的文档，如打开的文件。即使能够在那里开始会有帮助。

Answer 1

我在结束使用之前对函数做了一些调整。

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 

感谢上面的人让我去。

Answer 2

Public Sub Example() 
    Const csPath As String = "C:\Test\Example.csv" 
    Dim ws As Excel.Worksheet 
    Set ws = Excel.ActiveSheet 
    With ws.QueryTables.Add("TEXT;" & csPath, 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 = Array(xlTextFormat, xlTextFormat) 
     .Refresh 
    End With 
End Sub 

Answer 3

以下代码将允许用户浏览csv文件。
然后它将：

• 打开所选择的文件，处理该数据作为文本
• 调整列
• 将数据移动到从该代码在运行工作簿。

根据源数据中的列数，需要更新.opentext代码。

Sub ImportCSV() 

Dim vPath As Variant 
Dim wb As Excel.Workbook 
Dim ws As Excel.Worksheet 

Set wb = Excel.ActiveWorkbook 
Set ws = Excel.ActiveSheet 

vPath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv" _ 
, 1, "Select a file", , False) 
''//Show the file open dialog to allow user to select a CSV file 

If vPath = False Then Exit Sub 
''//Exit macro if no file selected 

Workbooks.OpenText Filename:=vPath, Origin:=xlMSDOS, StartRow:=1 _ 
    , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True _ 
    , FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), _ 
    Array(3, xlTextFormat)) 
''//The fieldinfo array needs to be extended to match your number of columns 

Columns.EntireColumn.AutoFit 
''//Resize the columns 

Sheets(1).Move Before:=wb.Sheets(1) 
''//Move the data into the Workbook 

End Sub