CODEWITHSUNDEEP
excelvbacsvoffice-2010
Sydney
Sydney

Reputation: 12212

Close copied worksheet when exporting into CSV

I try to save each worksheet of an excel spreadsheet into CSV files. The following script do the job. The line .Parent.Close savechanges:=False is supposed to close the created worksheet but when uncommented the script stops after the first sheet. If I comment the line, all the sheets are processed.

Dim newWks As Worksheet
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:\home\tmp\base\" & wks.Name & ".txt", FileFormat:=xlTextWindows
.Parent.Close savechanges:=False
End With
Next wks

MsgBox "done with: " & ActiveWorkbook.Name

Upvotes: 0

Views: 370

Answers (1)

Jon49
Jon49

Reputation: 4606

You're closing the activeworkbook. If you want to close the newly created object then do something like this (tested it and it works for me):

Dim newWkb As Workbook
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
  wks.Copy 'to a new workbook
  Set newWkb = ActiveWorkbook
  With newWkb
    .SaveAs Filename:="C:\home\tmp\base\" & wks.Name & ".txt", FileFormat:=xlTextWindows
    newWkb.Close savechanges:=False
  End With
Next wks

MsgBox "done with: " & ActiveWorkbook.Name

Upvotes: 1

Related Questions