0

I am trying to download some bank statements which only come in .pdf format and convert them to an excel file. I use NitroPDF.exe to convert the .pdf file to an excel file and then have created a VBA script to clean it up and append it into my main Excel file. I have to do the conversion manually up to the point where Excel VBA takes over. I would like to automate it, not sure how. I have tried to use cmd line:

"C:\Program Files\Nitro\Pro\12\NitroPDF.exe" "C:\Users\Adam\OneDrive\Desktop\TEMP\WebBroker - Balances.pdf

And that works fine in that it opens NitroPDF with the file I need in it. however when I try to run it in a .vbs file (named: RunningPdf.vbs) like this:

    Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "C:\Program Files\Nitro\Pro\12\NitroPDF.exe" "C:\Users\Adam\OneDrive\Desktop\TEMP\WebBroker - Balances.pdf", 1, true

I get an error:

Script:  C:\Users\Adam\OneDrive\Desktop\TEMP\RunningPdf.vbs  
Line: 2
Char: 59  
Error: Expected end of statement  
Code: 800A0401  
Source: Microsoft VBScript compilation error

What am I doing wrong in my translation from cmd to vbs? Should I even be using vbs for this? how could I meld this code to my Excel VBA code? I was planning to use sedkeys in the vbs code once NitroPDF opened, to automate the conversion from pdf to Excel, is this the best way? Sorry for the lengthy post, any help would be greatly appreciated Thank you

braX
  • 11,506
  • 5
  • 20
  • 33
Adam B
  • 93
  • 1
  • 3
  • 13
  • I cannot confirm but I suspect something with your strings... try Set WshShell = CreateObject("WScript.Shell") WshShell.Run "C:\Program Files\Nitro\Pro\12\NitroPDF.exe C:\Users\Adam\OneDrive\Desktop\TEMP\WebBroker - Balances.pdf", 1, true ...or... Set WshShell = CreateObject("WScript.Shell") WshShell.Run """C:\Program Files\Nitro\Pro\12\NitroPDF.exe""" """C:\Users\Adam\OneDrive\Desktop\TEMP\WebBroker - Balances.pdf""", 1, true ...try couple of things and see. If vbs accepts ' as string then use it alternating with ". – Apostolos55 Dec 05 '21 at 18:11
  • Tim's answer below worked with triple quotes, thanks – Adam B Dec 05 '21 at 18:34

1 Answers1

1

Always worth quoting file paths, but then you need to escape those quotes in the value being passed to Shell

WshShell.Run """C:\Program Files\Nitro\Pro\12\NitroPDF.exe"" ""C:\Users\Adam\OneDrive\Desktop\TEMP\WebBroker - Balances.pdf""", 1, true
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • That worked great, thank you so much, now to push my luck, could you help me with the other questions? – Adam B Dec 05 '21 at 18:19
  • You can look here: https://kb.gonitro.com/knowledgebase#/search/command%20line/000004309 I don't see an option for Excel conversion though. – Tim Williams Dec 05 '21 at 18:29
  • Thanks, that help a lot, perhaps I don't need to use SendKeys anymore, wonderful, Thank you Tim. – Adam B Dec 05 '21 at 18:33
  • About the hundredth time this answer has been given on [so] over the years. – user692942 Dec 05 '21 at 22:00
  • @AdamB - FYI you might consider accepting the answers to some of your previous questions, so folks who find them later can see there's a useful response there. – Tim Williams Dec 06 '21 at 08:10