1

I would like to execute a shell script in Excel with VBA. However, I need to use quotes " inside of my command. To figure out how I can escape quotes in the function MacScript I simply try it with a simple command but I fail all the time.

So, here is a working example:

Function HTTPGet2() As String

HTTPGet2 = MacScript("do shell script ""echo 'hello' """)

End Function

Output: hello

But now I want to display he"llo". Both of the following examples I've tried already and both of them failed..

Try 1:

Function HTTPGet2() As String

HTTPGet2 = MacScript("do shell script ""echo 'he""llo""' """)

End Function

Try 2:

Function HTTPGet2() As String

HTTPGet2 = MacScript("do shell script ""echo 'he"& Chr(34) &"llo"& Chr(34) &"' """)

End Function

Can you guys please explain me why it doesn't work and how to make it work? I'm using Excel 2019 and MacOs on 10.14.4 (18E226)

At the end this is the command I want to execute:

xmllint --html -xpath 'string(//*[@id="AssetAllocationLongShortTable1"]/table/tbody/tr[1]/td[1])' http://tools.morningstar.de/de/xray/default.aspx\?LanguageId\=de-DE\&PortfolioType\=2\&SecurityTokenList\=F000000EM3\]2\]0\]ETEXG$XETR\|F00000J8T2\]2\]0\]FODEU$$ALL\&values\=50.00\|50.00\&CurrencyId\=EUR\&from\=editholding

But this isn't working as well..:

Function HTTPGet2() As String

    HTTPGet2 = MacScript("do shell script "" xmllint --html -xpath 'string(//*[@id\""AssetAllocationLongShortTable1\""]/table/tbody/tr[1]/td[1])' http://tools.morningstar.de/de/xray/default.aspx\?LanguageId\=de-DE\&PortfolioType\=2\&SecurityTokenList\=F000000EM3\]2\]0\]ETEXG$XETR\|F00000J8T2\]2\]0\]FODEU$$ALL\&values\=50.00\|50.00\&CurrencyId\=EUR\&from\=editholding """)

End Function
Jan
  • 1,180
  • 3
  • 23
  • 60
  • Applescript has its own escaping requirements... `MacScript("do shell script ""echo 'he\""llo\""' """)` https://stackoverflow.com/questions/10667800/using-quotes-in-a-applescript-string – Tim Williams Apr 29 '19 at 20:42
  • Thanks @TimWilliams! - I have added the final command I want to execute.. I have tried it with `\""` but somehow it isn't working.. do you know why? – Jan Apr 29 '19 at 20:53
  • Since backslash `\ ` acts to escape the next character you also need to escape any `\ ` in your command by doubling them up `\\ ` – Tim Williams Apr 29 '19 at 20:57
  • `MacScript("do shell script "" xmllint --html -xpath 'string(//*[@id\""AssetAllocationLongShortTable1\""]/table/tbody/tr[1]/td[1])' http://tools.morningstar.de/de/xray/default.aspx\\?LanguageId\\=de-DE\\&PortfolioType\\=2\\&SecurityTokenList\\=F000000EM3\\]2\\]0\\]ETEXG$XETR\\|F00000J8T2\\]2\\]0\\]FODEU$$ALL\\&values\\=50.00\\|50.00\\&CurrencyId\\=EUR\\&from\\=editholding """) ` - this is not working for me.. tried it with `\\`. Can you may give me an example? – Jan Apr 29 '19 at 21:01
  • Sorry I'm not on Mac so can't help with testing... – Tim Williams Apr 29 '19 at 21:07
  • @TimWilliams Alright.. maybe passing the url as parameter works.. but I cannot insert the url here as parameter.. do you know how to do this? `HTTPGet2 = MacScript("do shell script "" xmllint --html -xpath 'string(//*[@id=\""hphdl\""])' ""url"" """)` – Jan Apr 29 '19 at 21:18
  • Sorry- I have no knowledge of macscript other than what I just Googled on escaping quotes ;-) – Tim Williams Apr 29 '19 at 21:28

0 Answers0