VBA将CSV转换为XLSX

最新推荐文章于 2024-11-07 10:57:23 发布

啊哩啵啵

最新推荐文章于 2024-11-07 10:57:23 发布

阅读量3k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： Misc

本文链接：https://blog.youkuaiyun.com/u010314160/article/details/88252214


Sub 批量转换格式()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim FilePath, MyFile, iPath As String
    iPath = ThisWorkbook.Path
    MyFile = Dir(iPath & "\*.CSV")
    
    On Error Resume Next

    Do While MyFile <> ""
        Rem 兼容两种分割类型
        If MyFile = "Sentence.csv" Then
            Workbooks.OpenText Filename:=iPath & "\" & MyFile, Origin:=65001, DataType:=xlDelimited, Tab:=True, Comma:=False
        Else
            Workbooks.OpenText Filename:=iPath & "\" & MyFile, Origin:=65001, DataType:=xlDelimited, Tab:=False, Comma:=True
        End If
        
        MyFile = iPath & "\" & Replace(ActiveWorkbook.Name, ".csv", ".xlsx", 1, -1, 1)
        With ActiveWorkbook.Sheets(1).UsedRange
            .Offset(1).SpecialCells(2, 1).NumberFormatLocal = "#,##0_ "
            For I = 1 To 5
               .Parent.Columns(I).ColumnWidth = .Parent.Columns(I).ColumnWidth * I
            Next I