vba 是否可以使用宏将 csv 批量转换为 xls？

Is it possible to batch convert csv to xls using a macro?

提问by Alistair Weir

I have a large amount of csvfiles that I need in .xlsformat. Is it possible to run a batch conversion with a macro or best done with another language?

我有大量csv需要.xls格式的文件。是否可以使用宏运行批量转换或最好使用另一种语言完成？

I have used this code http://www.ozgrid.com/forum/showthread.php?t=71409&p=369573#post369573to reference my directory but I'm not sure of the command to open each file and save them. Here's what I have:

我已经使用此代码http://www.ozgrid.com/forum/showthread.php?t=71409&p=369573#post369573来引用我的目录，但我不确定打开每个文件并保存它们的命令。这是我所拥有的：

Sub batchconvertcsvxls()
    Dim wb As Workbook
    Dim CSVCount As Integer
    Dim myVar As String

    myVar = FileList("C:\Documents and Settings\alistairw\My Documents\csvxlstest")
    For i = LBound(myVar) To UBound(myVar)

        With wb

            Application.Workbooks.OpenText 'How do I reference the myvar string ?
            wb.SaveAs '....

        End With

    Next
End Sub

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = Split("No files found", "|") 'ensures an array is returned
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
    Loop
    FileList = Split(sTemp, "|")
End Function

Edit: The files are .txt files formatted as csv

编辑：文件是 .txt 文件，格式为 csv

采纳答案by Scott Holtzman

In a lot less lines of code, this should get you what you want. However, I will say this may not be the fastest way to get it done, because you are opening, saving, and closing the workbook every time. I will look for a faster way, but I forget the method off the top of my head.

在更少的代码行中，这应该可以满足您的需求。但是，我会说这可能不是完成它的最快方法，因为您每次都在打开、保存和关闭工作簿。我会寻找更快的方法，但我忘记了方法。

Sub batchconvertcsvxls()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "C:\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(strDir & strFile)
    With wb
        .SaveAs Replace(wb.FullName, ".csv", ".xls"), 50 'UPDATE:
        .Close True
    End With
    Set wb = Nothing
Loop

End Sub

** UPDATE ** you need the proper fileformat enumeration for a .xls file. I think its 50, but you can check here Excel File Type Enumeration, if it's not.

** 更新 ** 您需要 .xls 文件的正确文件格式枚举。我认为它是 50，但如果不是，您可以在此处查看Excel File Type Enumeration。

回答by Rishab Jain

By combining the code given by Scott Holtzman and 'ExcelFreak', the conversion works quite well. The final code looks something like this:

通过结合 Scott Holtzman 和“ExcelFreak”给出的代码，转换效果很好。最终代码如下所示：

Sub CSV_to_XLS()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "U:\path\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
    wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), 50 'UPDATE:
    wb.Close True

    Set wb = Nothing
    strFile = Dir
Loop

End Sub

Opening the converted .xls file throws a warning everytime:

打开转换后的 .xls 文件每次都会抛出警告：

"The file you are trying to open, 'filename', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

“您尝试打开的文件‘文件名’与文件扩展名指定的格式不同。在打开文件之前，请确认该文件没有损坏并且来自受信任的来源。是否要打开该文件现在？”

Clicking Yes then opens the .xls file.

单击是，然后打开 .xls 文件。

Is there a way to get rid of this warning message? Excel throws a warning everytime the .xls file is opened.

有没有办法摆脱这个警告信息？每次打开 .xls 文件时，Excel 都会发出警告。

回答by ExcelFreak

The Code of Scott Holtzman nearly did it for me. I had to make two changes to get it to work:

Scott Holtzman 的代码几乎为我做到了。我必须进行两项更改才能使其正常工作：

1. He forgot to add the line that makes our loop continue with the next file. The last line before the Loop should read

   strFile = Dir

2. The Workbooks.Open method did not read my CSV files as expected (the whole line ended up to be text in the first cell). When I added the parameter Local:=Trueit worked:

   Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)

1. 他忘记添加使我们的循环继续下一个文件的行。Loop 之前的最后一行应该是

   strFile = Dir

2. Workbooks.Open 方法没有按预期读取我的 CSV 文件（整行最终成为第一个单元格中的文本）。当我添加参数时，Local:=True它起作用了：

   Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)

回答by RaaonGB

This works properly at least on Excel 2013. Using FileFormat:=xlExcel8 parameter instead of the filetype tag 50 creates files that open without security nags.

这至少在 Excel 2013 上正常工作。使用 FileFormat:=xlExcel8 参数而不是文件类型标记 50 创建打开时没有安全问题的文件。

Sub CSV_to_XLS()

子 CSV_to_XLS()

Dim wb As Workbook Dim strFile As String, strDir As String

将 wb 变暗为工作簿 将 strFile 变暗为字符串，将 strDir 变暗为字符串

strDir = "C:\temp\" strFile = Dir(strDir & "*.csv")

strDir = "C:\temp\" strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

做 while strFile <> ""

Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), FileFormat:=xlExcel8
wb.Close True

Set wb = Nothing
strFile = Dir

Loop

环形

End Sub

结束子

回答by Bartolomeo Rodas

This was a good question and I have found in the internet several answers. Just making very small changes (I couldn't edit any of the codes already published) I could make things work a bit better:

这是一个很好的问题，我在互联网上找到了几个答案。只需进行非常小的更改（我无法编辑任何已发布的代码），我就可以使事情变得更好：

Sub CSV_to_XLSX()

Dim wb As Workbook
Dim strFile As String, strDir As String

strDir = "C:\Users\acer\OneDrive\Doctorado\Study 1\data\Retest Bkp\Day 1\Sart\"
strFile = Dir(strDir & "*.csv")

Do While strFile <> ""

    Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
    With wb
        .SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51
        .Close True
    End With
    Set wb = Nothing
    strFile = Dir
Loop

End Sub