如何将Workbooks.Open()命令应用于VBA中的.csv文件

Question

我将在一个目录中列出所有.csv文件，逐个打开它们并应用"Text To Columns"，然后将每个文件保存为.xlsx。为了打开文件，我使用：

Workbooks.Open (directory & fileName)

目录所在位置：

directory = wb.Sheets("Directory").Cells(1, 2).Value 

filename是一个变量。

我注意到：当Workbooks.Open (directory & fileName)命令打开一个文件时，甚至在应用"Text to Columns"之前，.csv文件就会显示它的内容按列拆分（而不是将所有内容放在一列中）。有没有办法在Workbooks.Open (directory & fileName)中定义任何文件都必须以.csv格式打开？

感谢

Answer 1

请尝试将参数作为链接。关键点是分隔符是，或；

workbooks.Open(Filename, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], **[Delimiter]**, [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad])

来自www.safaribookonline.com

Answer 2

您可以尝试从包括csv在内的文本文件中"按原样"读取数据，并将其写入Excel工作簿：

Function MM_OpenTextFile(vPath As String, delim As String) As Variant 
    Dim FF As Integer 
    Dim lineArray As Variant 
    Dim temp As String 
    Dim arrayList As Object 
    Set arrayList = CreateObject("System.Collections.ArrayList") 
    FF = FreeFile 
    Open vPath For Input As #FF 
    While Not EOF(FF) 
        Line Input #FF, temp 
        lineArray = Split(temp, delim) 
        arrayList.Add lineArray 
        Erase lineArray 
    Wend 
    Close #FF 
    MM_OpenTextFile = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arrayList.ToArray())) 
    arrayList.Clear 
    Set arrayList = Nothing 
End Function

这将把分隔文件的内容作为2D数组分配给一个变体，然后可以将其放置在工作表上的分隔状态下，如下所示：

Sub Example() 
    Dim ar As Variant '// Must be a Variant to work
    '// Change to a file and delimiter of your choosing...
    ar = MM_OpenTextFile("C:UsersBloggsJSomeFile.txt", ";")          
    '// Change 'A1' to the cell you want the data pasting to 
    With Range("A1").Resize(UBound(ar, 1), UBound(ar, 2)) 
        .NumberFormat = "@" '// Change format to "text"
        .value = ar '// insert array values
    End With 
End Sub 

Answer 3

问题是，在Windows设置中，它会根据列表分隔符自动拆分单词。您可以在窗口中更改列表分隔符设置，并尝试将csv作为普通excel打开。

you can change the List separator setting in the Regional and Language Options as specified on the
    1.  Click the Windows Start menu.
    2.  Click Control Panel.
    3.  Open the Regional and Language Options dialog box.
    4.  Click the Regional Options Tab.
    5.  Click Customize / Additional settings (Win10).
    6.  Type a new separator in the List separator box.
    7.  Click OK twice.