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

hakita

New Member
Joined
Jun 30, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Screenshot 2025-02-16 at 8.00.51 PM.jpg
    Screenshot 2025-02-16 at 8.00.51 PM.jpg
    46 KB · Views: 5

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top