Exporting sheet as csv

Question

i am trying to export a sheet from my Excel file as a csv.

I am getting the error

Method SaveAs of Object Workbook Failed`

on my SaveAs line.

I notice as this code creates a new workbook, it has several blank default sheet tabs, would this be causing the issue?

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

Edit to exclude WbkExport variable, replace with ThisWorkbook and 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

Side note: you don't need to actually create a workbook. shtToExport.Copy with no parameters will create a new workbook, which will then be the ActiveWorkbook. — BigBen
– BigBen, Commented Nov 16, 2020 at 16:50
So you're saying I can eliminate this line and get the same result? — excelguy
– excelguy, Commented Nov 16, 2020 at 17:05
Yes you can eliminate Set wbkExport = Application.Workbooks.Add and just do shtToExport.Copy, and then work with ActiveWorkbook. — BigBen
– BigBen, Commented Nov 16, 2020 at 17:07
Tried this, now I seem to be getting application-defined or object defined error. I updated the code above if you want to take a look. — excelguy
– excelguy, Commented Nov 16, 2020 at 18:47
Should just be shtToExport.Copy. No Before. Then ActiveWorkbook.SaveAs. — BigBen
– BigBen, Commented Nov 16, 2020 at 18:49

pcandido · Accepted Answer · 2020-11-16 18:52:49Z

The previous comments are correct. A simple procedure would be:

 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

Collectives™ on Stack Overflow

Exporting sheet as csv

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related