0

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!

user9431057
  • 101
  • 1
  • You haven't declared `Path`. Try adding `Dim Path as String` and remove the `Set` from `Set Path = ...` – DavidPostill Aug 12 '18 at 19:22
  • @DavidPostill♦ After searching for a while I found this [post](https://stackoverflow.com/questions/18507042/saving-excel-workbook-to-constant-path-with-filename-from-2-fields) . I added `.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? – user9431057 Aug 12 '18 at 19:33
  • 1
    You should spend some time learning to understand vba instead of trying to write code using random snippets from the internet. – DavidPostill Aug 12 '18 at 19:35
  • @DavidPostill agreed, I am new to VBA and this is one of the task I was thinking to do myself to automate. I started from today to learn. – user9431057 Aug 12 '18 at 19:36
  • [Workbook.Save Method (Excel)](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-save-method-excel) – DavidPostill Aug 12 '18 at 19:38
  • The easiest method I can suggest you,, `Dim FromPath As String` `Dim ToPath As String` `FromPath = "C:\Users\Abc\Data" ` `ToPath = "C:\Users\ABC\Test"` **The text I've used to show the File Path is a kind of data and every data needs some data element called variable to hold value. So that you need to declare variable first then assign path (value) to it, will work.** – Rajesh Sinha Aug 13 '18 at 07:18

0 Answers0