ドラッグ＆ドロップでファイルのパスを取得するVBAマクロ(クリップボード経由) 関連記事：https://note.com/kinuasa/n/n3db79c1b6e03

GetDroppedFileAndFoldersFromCB.vb

Public Sub Sample2()
  Dim aryPaths As Variant
  Dim i As Long
  aryPaths = GetDroppedFileAndFoldersFromCB
  If UBound(aryPaths) <> -1 Then
    For i = LBound(aryPaths) To UBound(aryPaths)
      Debug.Print aryPaths(i)
    Next
  End If
End Sub

'ドラッグ＆ドロップされたファイルやフォルダーのパスをクリップボード経由で取得
Private Function GetDroppedFileAndFoldersFromCB() As Variant
  '実行するPowerShellスクリプト
  Dim com As String
  com = "PowerShell -Command """
  com = com & "Set-Clipboard $null;"
  com = com & "Add-Type -AssemblyName \""PresentationFramework\"";"
  com = com & "$xaml = '<Window xmlns=\""http://schemas.microsoft.com/winfx/2006/xaml/presentation\"" Title=\""FilePicker\"" Height=\""250\"" Width=\""350\"" AllowDrop=\""True\"" Topmost=\""True\""><Grid><ListBox Name=\""FileListBox\"" AllowDrop=\""True\""/><TextBlock Name=\""HintText\"" Text=\""ここにファイルをドラッグ＆ドロップしてください\"" Foreground=\""Gray\"" HorizontalAlignment=\""Center\"" VerticalAlignment=\""Center\"" IsHitTestVisible=\""False\"" /></Grid></Window>';"
  com = com & "$reader = [System.Xml.XmlReader]::Create([System.IO.StringReader]$xaml);"
  com = com & "$window = [Windows.Markup.XamlReader]::Load($reader);"
  com = com & "$fileListBox = $window.FindName(\""FileListBox\"");"
  com = com & "$files = @();"
  com = com & "$fileListBox.Add_DragOver({if($_.Data.GetDataPresent([Windows.DataFormats]::FileDrop)){$_.Effects = [Windows.DragDropEffects]::Copy}else{$_.Effects = [Windows.DragDropEffects]::None};$_.Handled = $true});"
  com = com & "$fileListBox.Add_Drop({if($_.Data.GetDataPresent([Windows.DataFormats]::FileDrop)){$script:files = $_.Data.GetData([Windows.DataFormats]::FileDrop);$window.Close()}});"
  com = com & "$window.ShowDialog() | Out-Null;"
  com = com & "Set-Clipboard($files -Join \""|\"");"""
  
  'PowerShellを非表示で実行
  Dim ps As Object, pid As Long
  Set ps = GetObject("winmgmts:Win32_ProcessStartup")
  ps.ShowWindow = 0
  GetObject("winmgmts:Win32_Process").Create com, , ps, pid
  
  'PowerShell実行完了待ち
  Dim oSWbemServicesEx As Object
  Dim colProcesses As Object
  Dim cnt As Long: cnt = 0
  Set oSWbemServicesEx = CreateObject("WbemScripting.SWbemLocator").ConnectServer
  Do
    Set colProcesses = oSWbemServicesEx.ExecQuery("Select * from Win32_Process Where ProcessId = " & pid)
    cnt = colProcesses.Count
    'Debug.Print cnt '確認用
    DoEvents
  Loop While cnt > 0
  
  'クリップボード経由で実行結果を取得
  '参考：https://www.ka-net.org/blog/?p=7537
  Dim str As String
  With CreateObject("Forms.TextBox.1")
    .MultiLine = True
    If .CanPaste = True Then .Paste
    str = .Text
  End With
  GetDroppedFileAndFoldersFromCB = Split(str, "|")
End Function