gpt4 book ai didi

excel - 有没有办法将范围批量写入文本/CSV 文件?

转载 作者:行者123 更新时间:2023-12-02 15:35:36 26 4
gpt4 key购买 nike

我习惯用VBA中的write命令将一系列单元格的内容(值)写入文本文件,例如:

写入 #myfile, Range("A1").value, Range("A2).value, Range("A3).value

是否存在一种更优雅、更方便的内置方法来将整个范围直接转储到分隔文件,甚至可能一次转储多行?或者有人提出了定制的解决方案吗?我认为这将非常有用。

最佳答案

我写信给你,它仍然可以改进,但我认为它已经足够好了:

Sub SaveRangeAsCSV(r As Range, filename As String, overwrite As Boolean)
Dim wB As Workbook
Dim c As Range
Dim usedRows As Long
If overwrite Then
If Dir(filename) <> "" Then Kill filename
If Err.Number <> 0 Then
MsgBox "Could not delete previously existing file." & vbNewLine & Err.Number & ": " & Err.Description
Exit Sub
End If
End If
If Dir(filename) <> "" Then
Set wB = Workbooks.Open(filename)
Else
Set wB = Workbooks.Add
End If

With wB.Sheets(1)
usedRows = .UsedRange.Rows.Count
'Check if more than 1 row is in the used range.
If usedRows = 1 Then
'Since there's only 1 row, see if there's more than 1 cell.
If .UsedRange.Cells.Count = 1 Then
'Since there's only 1 cell, check the contents
If .Cells(1, 1) = "" Then
'you're dealing with a blank workbook
usedRows = 0
End If
End If
End If
'Check if range is contigious
If InStr(r.Address, ",") Then
For Each c In r.Cells
.Range(c.Address).Offset(usedRows, 0).Value = c.Value
Next
Else
.Range(r.Address).Offset(usedRows, 0).Value = r.Value
End If
End With
wB.SaveAs filename, xlCSV, , , , False
wB.Saved = True
wB.Close
End Sub
Sub Example()
'I used Selection here just to make it easier to test.
'Substitute your actual range, and actual desired filepath
'If you pass false for overwrite, it assumes you want to append
'It will give you a pop-up asking if you want to overwrite, which I could avoid
'by copying the worksheet and then closing and deleting the file etc... but I
'already spent enough time on this one.
SaveRangeAsCSV Selection, "C:\proofOfConcept.csv", False
End Sub

使用时,只需提供实际范围、实际文件名以及是否要覆盖该文件。 :) 这已更新为允许非连续范围。对于合并单元格,它最终会将值放入合并范围的第一个单元格中。

关于excel - 有没有办法将范围批量写入文本/CSV 文件?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13077317/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com