1

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

Improve this question
4
  • Why would you bend over backwards to copy the filename to the clipboard and .SendKeys() CTRL+V, when you could .SendKeys() the actual filename?
    – Tomalak
    Commented Feb 25, 2022 at 11:02
  • you mean like this : WshShell.SendKeys Customer_rates in place of CTR+V? didnt work
    – Stoyan Bozhkov
    Commented Feb 25, 2022 at 13:46
  • Yes, exactly. A couple of characters have special meaning for SendKeys - you must escape +, [, ], ^, %, ~, {, }, (, ) as {+}, {[}, {]}, {^}, {%}, {~}, {{}, {}}, {(}, {)}, respectively - but other than that you can send the filename directly with SendKeys and skip the clipboard entirely. You could make a small helper function e.g. Function MakeSafeForSendKeys(string) that does these replacements and returns a safe value. Then you can call WshShell.SendKeys MakeSafeForSendKeys(Customer_rates) and it should work.
    – Tomalak
    Commented Feb 25, 2022 at 14:00
  • This function you could then re-use to directly type other values into other fields.
    – Tomalak
    Commented Feb 25, 2022 at 14:02

3 Answers 3

Reset to default
0

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
Improve this answer
3
  • thanks but same error occurs again
    – Stoyan Bozhkov
    Commented Feb 25, 2022 at 13:53
  • Can you echo the output in the console and show us what command is being executed?
    – Sivakanesh
    Commented Feb 25, 2022 at 14:13
  • Yes thanks, here it is : 'D:\FX' is not recognized as an internal or external command, operable program or batch file.
    – Stoyan Bozhkov
    Commented Mar 1, 2022 at 9:06
0

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

Improve this answer
0

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
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.