CODEWITHSUNDEEP
vbaexcelcsv
user2653322
user2653322

Reputation: 117

Export multiple worksheets to CSV and specify save folder

I've got this Macro working beautifully, but every now and again it will save the exported sheets into the last folder I was working in, and not the specific folder I want them in. How do I specify the folder they should go into?

Sub asdf()
Dim ws As Worksheet, newWb As Workbook

Application.ScreenUpdating = False
For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3"))
   ws.Copy
   Set newWb = ActiveWorkbook
   With newWb
      .SaveAs ws.Name & ".csv", xlCSVWindows
      .Close (False)
   End With
Next ws
Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 326

Answers (3)

user4039065
user4039065

Reputation:

Add a string type var that contains the full path to the folder. In the following example, I'll use the temp directory.

Sub asdf()

    Dim ws As Worksheet, newWb As Workbook, fp as string

    fp = environ("TEMP") & Chr(92)
    'could be something like
    'fp = environ("USER") & "\desktop\"

    Application.ScreenUpdating = False
    For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3"))
       ws.Copy
       Set newWb = ActiveWorkbook
       With newWb
          .SaveAs fp & ws.Name, xlCSVWindows   'add the path, let saveas add the extension
          .Close savechanges:=False
       End With
    Next ws
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Sixthsense
Sixthsense

Reputation: 1975

Replace this line

.SaveAs ws.Name & ".csv", xlCSVWindows

With

.SaveAs "D:\MyFolder\" & ws.Name & ".csv", xlCSVWindows

Change the "D:\MyFolder\" to your desired path.

Upvotes: 1

DDuffy
DDuffy

Reputation: 413

You need to specify the location you want it saved to in the code.

Try this.

.SaveAs FileName:="C:\OutputFilepath\" & ws.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Upvotes: 1

Related Questions