0

Original post: Export sheet as UTF-8 CSV file (using Excel-VBA)

Could someone help me edit this so I could save the files inside a folder on my desktop? As of now, the code does everything I want, but it saves the CSV file in my documents.

Sub SaveWorkSheetAsCSV()

Dim wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim name As String



    Set wsSource = ThisWorkbook.Worksheets(1)
    name = "test"
    Application.DisplayAlerts = False 'will overwrite existing files without asking
    Set wsTemp = ThisWorkbook.Worksheets(1)
    Set wbNew = ActiveWorkbook
    Set wsTemp = wbNew.Worksheets(1)
    wbNew.SaveAs name & ".csv", xlCSVUTF8 'new way
    wbNew.Close
    Application.DisplayAlerts = True

End Sub
IPV8
  • 1
  • 1
    Does this answer your question? [Replace file path in VBA with user name](https://stackoverflow.com/questions/45882111/replace-file-path-in-vba-with-user-name) – Warcupine Feb 08 '21 at 13:21
  • 1
    I think https://stackoverflow.com/a/17551579/7599798 gives a much better answer. – FunThomas Feb 08 '21 at 13:35

1 Answers1

0

I didn't test it, but try this:

Sub SaveWorkSheetAsCSV()

Dim wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim name As String

Dim Fld As FileDialog
Dim BrowseToFolderPath As String


    Set Fld = Application.FileDialog(msoFileDialogFolderPicker)
    
    If Fld.Show = -1 Then
        BrowseToFolderPath = Fld.SelectedItems(1)
    Else
        MsgBox "No path selected"
        Exit Sub
    End If


    Set wsSource = ThisWorkbook.Worksheets(1)
    name = BrowseToFolderPath & "test"
    Application.DisplayAlerts = False 'will overwrite existing files without asking
    Set wsTemp = ThisWorkbook.Worksheets(1)
    Set wbNew = ActiveWorkbook
    Set wsTemp = wbNew.Worksheets(1)
    wbNew.SaveAs name & ".csv", xlCSVUTF8 'new way
    wbNew.Close
    Application.DisplayAlerts = True

End Sub

Please remember that the xlCSVUTF8 parameter is from Excel 2016

xl0911
  • 108
  • 8