I am using this post's accepted answer for one my exports.
Based on the answer, it saves my exported .csv
file in my C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART
.
However, I need this export into my file location, where I am exporting data from. It should be C:\Users\username\Desktop\Sample_Files\Excel_to_csv_export
I tried the following based on the answer:
Sub ExportTable()
Dim wb As Workbook, wbNew As Workbook
Dim ws As Worksheet, wsNew As Worksheet
Dim wbNewName As String
Set wb = ThisWorkbook
Set ws = ActiveSheet
Set wbNew = Workbooks.Add
'I set the path below, which did not work
Set Path = 'C:\Users\username\Desktop\Sample_Files\Excel_to_csv_export'
With wbNew
Set wsNew = wbNew.Sheets("Sheet1")
wbNewName = ws.ListObjects(1).Name
ws.ListObjects(1).Range.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
.SaveAs Filename:=wb.Path & "\" & wbNewName & ".csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False
End With
End Sub
How can I set the path to my desired path? Any help would be great!
Path
. Try addingDim Path as String
and remove theSet
fromSet Path = ...
.SaveAs Filename: "C:\Users\username\Desktop\Sample_Files\Excel_to_csv_export.csv"
and it exports, but I have to manually save. Any ideas why is it not saving automatically?