Get data macro with source as variable

[Newbies111]

hello, im new to excel VBA

i've excel (lets call it Excel 1) with get data from another excel (lets call it Excel 2)
the problem is, i want the source of Get Data is variable, so i dont have to update the Source everytime i copy to other computer
Excel 1 & Excel 2 always in the same folder...

so i guess i can use something like this? --> Application.ActiveWorkbook.path & "\" & "Excel2.xlsm"
but i dunno how to arrange it in the VBA...

below is the original get data VBA from record macro..
please kindly help

ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""D:\OneDrive - MyNest\1. HOC - Heavy Oil Complex\FACILIY HOC\Project\E-laporan\E-Laporan HOC v11\DCU\Excel2.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Chan" & _
        "ged Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Laporan Kegiatan Lapangan #140 DCU"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type any}, {""Laporan Kegiatan Chamber #140 DCU"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type" & _
        " text}, {""Column11"", type text}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}, {""Column21"", type any}, {""Column22"", type any}, {""Column23"", type any}, {""Column24"", type a" & _
        "ny}, {""Column25"", type any}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False

 

[veyselemre]

Hi, I'm not sure, try it.

ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & _
                           "Source = Excel.Workbook(File.Contents(""" & _
                           ActiveWorkbook.Path & "\Excel2.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Chan" & _
                           "ged Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Laporan Kegiatan Lapangan #140 DCU"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type any}, {""Laporan Kegiatan Chamber #140 DCU"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type" & _
                           " text}, {""Column11"", type text}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}, {""Column21"", type any}, {""Column22"", type any}, {""Column23"", type any}, {""Column24"", type a" & _
                           "ny}, {""Column25"", type any}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

 

[Newbies111]

Hi, I'm not sure, try it.

ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & _
                           "Source = Excel.Workbook(File.Contents(""" & _
                           ActiveWorkbook.Path & "\Excel2.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Chan" & _
                           "ged Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Laporan Kegiatan Lapangan #140 DCU"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type any}, {""Laporan Kegiatan Chamber #140 DCU"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type" & _
                           " text}, {""Column11"", type text}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}, {""Column21"", type any}, {""Column22"", type any}, {""Column23"", type any}, {""Column24"", type a" & _
                           "ny}, {""Column25"", type any}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

thanks for the prompt response

ive tried ur code but it make connection only.. not showing any additional data on sheet. so i add the rest of my original code.
but it shows runtime error 1004: [DataFormat.Error] The supplied file path must be a valid absolute path .. when debug, prompter shows it error on line .Refresh BackgroundQuery:=False

Sub tarikexforum2()
'
' tarik Macro
'

'
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & _
                           "Source = Excel.Workbook(File.Contents(""" & _
                           ActiveWorkbook.Path & "\Excel2.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Chan" & _
                           "ged Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Laporan Kegiatan Lapangan #140 DCU"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type any}, {""Laporan Kegiatan Chamber #140 DCU"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type" & _
                           " text}, {""Column11"", type text}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type any}, {""Column20"", type any}, {""Column21"", type any}, {""Column22"", type any}, {""Column23"", type any}, {""Column24"", type a" & _
                           "ny}, {""Column25"", type any}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=False 'shows runtime error 1004: [DataFormat.Error] The supplied file path must be a valid absolute path
    End With
    Application.CommandBars("Queries and Connections").Visible = False
End Sub