title: Change source of a query using vba
body: <p>Im trying to figure out How to edit the Source of a query automatically via VBA, which creates the query. The Point is that the excel file containing the Data is Stored in the Same folder As the excel file in which the query will be created, and if i want to Send the whole folder to another Person, the query tries to connect to My User instead of the local excel file containing the Data.</p>
  
  <pre><code> Dim Datei2 As String
  Set Datei2 = ActiveWorkbook.Path
  Datei2 = ActiveWorkbook.Path &amp; "\NameOfTheFile.csv"
  'This part creates the path I want to use
  
  ActiveWorkbook.Queries.Add Name:="NameOfTheFile" _
      , Formula:= _
      "let" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "    Quelle = 
  
  Csv.Document(File.Contents(""C:\Users\USERNAME\Desktop\censored\NameOfTheFile.c 
  sv""),[Delimiter="";"", Columns=18, Encoding=1252, 
   QuoteStyle=QuoteStyle.None])," &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "    #""Geänderter Typ"" = Table.TransformColumnTypes(Quelle,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}," &amp; _
      " {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type t" &amp; _
      "ext}})" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "in" &amp; Chr(13) &amp; "" &amp; Chr(10) &amp; "    #""Geänderter Typ"""
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""NameOfTheFile"";Extended Properti" _
      , "es="""""), Destination:=Range("$A$1")).QueryTable
      .CommandType = xlCmdSql
      .CommandText = Array( _
      "SELECT * FROM [NameOfTheFile]")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "NameOfTheFile"
      .Refresh BackgroundQuery:=False
  </code></pre>
  
  <p>Hope it's not too messy, i created a new Macro and included all the code, so you can run it if you enter the Paths and Filenames.</p>
  
  <p>I created the first three lines of code which give me the location of the excel containing the data, which should then be pasted five lines below where the current absolute path is (under CSV.Document(File.Contents(...)</p>
  
  <p>Or is it possible to edit an existing query where i can change the path using a string like above?</p>
  
  <p>Thank you for your time!</p>