gpt4 book ai didi

vba - 将筛选的工作表导出为 CSV 文件

转载 作者:行者123 更新时间:2023-12-04 21:32:15 24 4
gpt4 key购买 nike

我目前正在使用此代码导出整个工作表,但我只想导出满足条件的行。在这种情况下,第 13 列中有一个“x”:

Option Explicit
Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

Dim Change below to "- 4" to become compatible with .xls files
MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

我尝试使用自动过滤器调整代码,但没有成功。
我换了线
ActiveWorkbook.ActiveSheet.UsedRange.Copy

有了这个:
ActiveSheet.Range("$A$1:$M$81").AutoFilter Field:=13, Criteria1:="x"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

但是我收到 Range Class 的 Autofiltermethod failed 错误。

最佳答案

有时更容易摆脱你不想要的东西,而不是复制你想要的东西。

Sub ExportAsCSV()
Dim myFileName As String

With ActiveWorkbook
myFileName = Left(.FullName, InStrRev(.Name, Chr(46)) - 1) 'note no extension needed
.ActiveSheet.Copy 'makes a copy in a new active workbook
End With

'new ActiveWorkbook with copy of worksheet
With ActiveWorkbook
With .Worksheets(1)
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
.Value = .Value
.AutoFilter field:=13, criteria1:="<>x"
With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
If CBool(Application.Subtotal(103, .Cells)) Then
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
End With
End With
If .AutoFilterMode Then .AutoFilterMode = False
End With
.SaveAs Filename:=myFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
.Close SaveChanges:=False
End With

Application.DisplayAlerts = False
Application.DisplayAlerts = True
End Sub

关于vba - 将筛选的工作表导出为 CSV 文件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49174615/

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