method run object iwshshell3 failed vba

Question

I am trying to automate file upload on chrome with this VBA:

Dim Customer_rates As String

Dim WshShell As Object

Customer_rates = "D:\FX Exch. Rates\2022-Feb-24 1707\MP_customer_exchange_rates_sample.xlsx"

Set WshShell = CreateObject("WScript.Shell")
  
    WshShell.Run "cmd.exe/c echo" & Customer_rates & "| clip", vbNormal, True
    WshShell.SendKeys "^{v}"
    Application.Wait DateAdd("S", 2, Now)
    WshShell.SendKeys "{ENTER}"

I get this error:

method run object iwshshell3 failed

Answer 1

Think about how this would appear in the console. The file path has spaces. So it will require quotes around it when you run it. Something like:

WshShell.Run "cmd.exe/c echo" & chr(34) & Customer_rates & chr(34) & "| clip", vbNormal, True

Answer 2

I did a workaround of cmd with this Sub and it seems to work:

Sub StoreData()
  Dim varText As String
  Dim objCP As Object

  varText = "D:\FX Exch. Rates\2022-Feb-24 1707\MP_customer_exchange_rates_sample.xlsx"
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", varText

End Sub

Answer 3

Try to always work with absolute paths (program and arguments). Be aware of quotes. I preferably use chr(13)