How can I import a file with Power Query with a custom file path, one that is in a cell in my Excel file?
Good question, here is how.
Select file macro code
I showed you a button in the video that enabled you to select the file you wanted to import and put the path into a cell.
Here is the code behind that button.
Sub GetFilePath()
Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FileSelected = .SelectedItems(1)
End With
ActiveSheet.Range("A2") = FileSelected
End Sub
Power Query code
We also had a piece of code to add in Power Query. You can see it here:
FilePath = Excel.CurrentWorkbook(){[Name="Path"]}[Content]{0}[Column1],Download files
You can see the files I used here.