Find and get data from another workbook

[slapshock]

Hi. I am newbie. I would like to ask help for a macro. So i have a workbook with lots of worksheets. The thing is, I would like to search and get data from this workbook and add it to the new workbook. The search criteria is the name of the worksheet. I tried recording a macro by using the GET DATA because it automatically add the worksheet to a new sheet with same worksheet name. I would like to run the macro by triggering it with a the text string that i will search from the reference workbook. Thanks in advance

 

[slapshock]

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Queries.Add Name:="E9L__ BESTD_ JS6", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""D:\AP3D Integration\ASME\ELBOW 90 LR.xls""), null, true)," & Chr(13) & "" & Chr(10) & " E9L__.BESTD_.JS6_Sheet = Source{[Item=""E[COLOR=rgb(44, 130, 201)]9L__.BESTD_.JS6[/COLOR]"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(E9L__.BESTD_.JS6_Sheet,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
"text}, {""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 text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", type text}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type text}, {""Column30"", type text}, {""Column31"", type text}, {""Co" & _
"lumn32"", type text}, {""Column33"", type text}, {""Column34"", type text}, {""Column35"", type text}, {""Column36"", type text}, {""Column37"", type text}, {""Column38"", type text}, {""Column39"", type text}, {""Column40"", type text}, {""Column41"", type text}, {""Column42"", type text}, {""Column43"", type text}, {""Column44"", type text}, {""Column45"", type te" & _
"xt}, {""Column46"", type text}, {""Column47"", type text}, {""Column48"", type text}, {""Column49"", type text}, {""Column50"", type text}, {""Column51"", type text}, {""Column52"", type text}, {""Column53"", type text}, {""Column54"", type text}, {""Column55"", type text}})," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Changed Type"", [PromoteAllScalars=tru" & _
"e])," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Previews"", type text}, {""Sizes"", type text}, {""ShapeName"", type text}, {""D"", type text}, {""D2"", type text}, {""R"", type text}, {""A"", type text}, {""L1"", type text}, {""L2"", type text}, {""OF"", type text}, {""ContentGeometryParamDefinition"", type text}, {""SizeRecordId" & _
"_S-ALL"", type text}, {""PortName_S-ALL"", type text}, {""NominalDiameter_S-ALL"", type text}, {""NominalUnit_S-ALL"", type text}, {""MatchingPipeOd_S-ALL"", type text}, {""EndType_S-ALL"", type text}, {""FlangeStd_S-ALL"", type text}, {""GasketStd_S-ALL"", type text}, {""Facing_S-ALL"", type text}, {""FlangeThickness_S-ALL"", type text}, {""PressureClass_S-ALL"", t" & _
"ype text}, {""Schedule_S-ALL"", type text}, {""WallThickness_S-ALL"", type text}, {""EngagementLength_S-ALL"", type text}, {""LengthUnit_S-ALL"", type text}, {""ShortDescription"", type text}, {""CompatibleStandard"", type text}, {""DesignStd"", type text}, {""PartFamilyLongDesc"", type text}, {""PartSizeLongDesc"", type text}, {""Material"", type text}, {""Material" & _
"Code"", type text}, {""Weight"", type text}, {""WeightUnit"", type text}, {""SKEY"", type text}, {""TYPE"", type text}, {""PartFamilyId"", type text}, {""CatalogPartFamilyId"", type text}, {""PartStatus"", type text}, {""Manufacturer"", type text}, {""ItemCode"", type text}, {""DesignPressureFactor"", type text}, {""ConnectionPortCount"", type text}, {""ComponentDes" & _
"ignation"", type text}, {""PartCategory"", type text}, {""ContentIsoSymbolDefinition"", type text}, {""PartVersion"", type text}, {""Status"", type text}, {""PathAngle"", type text}, {""CurveRadius"", type text}, {""SegmentCount"", type text}, {""PnPClassName"", type text}, {""ExcelFormatVersion_2.0"", type text}, {""PnPID"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type1""" & _
""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""E9L__ BESTD_ JS6"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [E9L__ BESTD_ JS6]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "E9L___BESTD__JS6"
.Refresh BackgroundQuery:=False
End With
Range("K15").Select
End Sub


The blue text is the keyword and not fixed