时间戳

首页 > 解决方案 > VBA 创建 csv 文件

excel - VBA 创建 csv 文件

问题描述

将批处理文件从 excel 保存为 csv。

VBA 在每 833 行之后将一张 Excel 数据表分成单独的文件。

现在我需要这些文件是 csvs 而不是 Excels。如何直接保存到 .csv（以逗号分隔）文件？

我已经制作了适当的 VBA 来保存到 Excel (xml)，但不是 csvs。我需要 CSV。

Sub Macro1()
Dim rLastCell As Range
Dim rCells As Range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook

    With ThisWorkbook.Sheets(1)
    Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

        For lLoop = 1 To rLastCell.Row Step 833
        lCopy = lCopy + 1
            Set wbNew = Workbooks.Add
                .Range(.Cells(lLoop, 1), .Cells(lLoop + 833, .Columns.Count)).EntireRow.Copy _
                    Destination:=wbNew.Sheets(1).Range("A1")
            wbNew.Close SaveChanges:=True, Filename:="Chunk" & lCopy & "Rows" & lLoop & "-" & lLoop + 833
        Next lLoop
    End With


End Sub

实际结果：Excel 预期结果：CSV 文件

标签： excelvbacsv

解决方案

像这样的东西？

Sub CSVQuotes()

Dim SrcRange As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSeparator As String
Dim FileName As Variant
Dim current As String    

FileName = Application.GetSaveAsFilename()
ListSeparator = ";"

Set SrcRange = Sheets("DATI").UsedRange

Open FileName For Output As #1
For Each CurrRow In SrcRange.Rows
    CurrTextStr = ""

    For Each CurrCell In CurrRow.Cells
        current = CurrCell

        CurrTextStr = CurrTextStr & """" & current & """" & ListSeparator
    Next
    While Right(CurrTextStr, 1) = ListSeparator
        CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
    Wend
    Print #1, CurrTextStr
    Next
Close #1

推荐阅读