Create automation VBA code with Power query to load data another workbook

[hakita]

Hi all,
I have a task repeating to mapping information so i need load data by using Power Query to select a table data from RAW data workbook. So i need a VBA code tools can help me to create a new sheets with table data from RAW workbook to current workbook for mapping data easily
Here is my record macro can help me to do that task but today i found if i dont open this file RAW data it will return #N/A caused by this file using Table data. So i think if i can have a tools auto create Power Query and load this table into my current Workbook it can working perfectly.
Have anybody have ideas to help me, very thankful to you <3
Link file: Microsoft OneDrive

Sub XlookupFileInv()
'
' XlookupFileInv Macro
'

'
ActiveWindow.SmallScroll Down:=10
Columns("A:A").Select
Range("A11").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-10
Range("A1").Select
ActiveCell.FormulaR1C1 = "Sub-region"
Range("B1").Select
ActiveCell.FormulaR1C1 = "RS"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Sku-En"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PC Shop"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[4],'Sign in to your Microsoft account Xiaomi/Retail Mapping/SCC2 Map Final/[Retail mapping.xlsx]Retail Mapping (Nation)'!C2,'Sign in to your Microsoft account Xiaomi/Retail Mapping/SCC2 Map Final/[Retail mapping.xlsx]Retail Mapping (Nation)'!C9)"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[3],'Sign in to your Microsoft account Xiaomi/Retail Mapping/SCC2 Map Final/[Retail mapping.xlsx]Retail Mapping (Nation)'!C2,'Sign in to your Microsoft account Xiaomi/Retail Mapping/SCC2 Map Final/[Retail mapping.xlsx]Retail Mapping (Nation)'!C14)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[5],'Sign in to your Microsoft account Xiaomi/RRP/[FILE Tracing RRP.xlsx]MBW RRP'!C2,'Sign in to your Microsoft account Xiaomi/RRP/[FILE Tracing RRP.xlsx]MBW RRP'!C3)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(XLOOKUP(RC[1],'Sign in to your Microsoft account Xiaomi/PC list Update/HC PC Plan_/[PC total final query.xlsx]Query1'!C28,'Sign in to your Microsoft account Xiaomi/PC list Update/HC PC Plan_/[PC total final query.xlsx]Query1'!C5)>0,""Have PC"",""Non-PC""),""Non-PC"")"
Range("A2:D2").Select
Selection.AutoFill Destination:=Range("A2:D4")
Range("A2:D4").Select
End Sub

 

[hakita]

Hi all, anybody help me