Newbies111
New Member
- Joined
- Aug 20, 2021
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
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
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
VBA Code:
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