时间戳

首页 > 解决方案 > 将工作表导出为 csv

excel - 将工作表导出为 csv

问题描述

我正在尝试将我的 Excel 文件中的工作表导出为 csv。

我收到错误

对象工作簿的方法 SaveAs 失败`

在我的SaveAs线上。

我注意到此代码创建了一个新工作簿，它有几个空白的默认工作表选项卡，这会导致问题吗？

Public Sub ExportWorksheetAndSaveAsCSV()
    
    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet
    
    Set shtToExport = ThisWorkbook.Worksheets("Load check data")     'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    Application.DisplayAlerts = False 'Possibly overwrite without asking
    Path = ThisWorkbook.Sheets("Input").Range("B15") & "estload_" & ThisWorkbook.Sheets("Input").Range("F1") & ".csv"
    Debug.Print Path
    wbkExport.SaveAs Filename:=Path, FileFormat:=xlCSV, CreateBackup:=True
    Application.DisplayAlerts = True
    wbkExport.Close SaveChanges:=False
    
End Sub

编辑以排除 WbkExport 变量，替换为 ThisWorkbook 和 ActiveWorkbook。

Public Sub ExportWorksheetAndSaveAsCSV()
    
    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet
    Dim Path As String
    
    Set shtToExport = ThisWorkbook.Worksheets("Load check data")     'Sheet to export as CSV
    'Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    Application.DisplayAlerts = False 'Possibly overwrite without asking
    Path = ThisWorkbook.Sheets("Input").Range("B15") & "estload_" & ThisWorkbook.Sheets("Input").Range("F1") & ".csv"
    Debug.Print Path
    ActiveSheet.SaveAs Filename:=Path, FileFormat:=xlCSV, CreateBackup:=True
    Application.DisplayAlerts = True
    'wbkExport.Close SaveChanges:=False

标签： excelvba

解决方案

前面的评论是正确的。一个简单的程序是：

    Public Sub ExportWorksheetAndSaveAsCSV()
    
    'Simple copy of sheet content as csv file
    Dim NameSheet As String
    Dim PathNameCsv As Variant
           
    'Change names & Output Path
    NameSheet = "MySheet"
    PathNameCsv = "D:\Documents\MyCsv"
    
    Set shtToExport = ThisWorkbook.Worksheets(NameSheet)     'Sheet to export as CSV
    
    Application.DisplayAlerts = False 'Possibly overwrite without asking
    shtToExport.SaveAs Filename:=PathNameCsv, FileFormat:=xlCSV, CreateBackup:=True
    Application.DisplayAlerts = True
    
    'When saving the sheet as csv, _
    'by default its name is changed to the name of the csv.
    'Here you restore your name
    With shtToExport
    .Name = NameSheet '
    End With
  
End Sub

推荐阅读