Calling Java using Shell command 1

[sapatos]

Hi,

I have very limited vba skills but have written a java programme to parse worksheets and write out data to a csv file. My client have asked that a button/macro be added to the spreadsheet that is the source of the data to call this programme.

I've gotten as far as finding the Shell command and running basic microsoft apps, however when I try to build the command line for the java call its not happy. Below is the code:


Sub showMessage()

Dim retVal As Variant
Dim jar As String
Dim srcFile As String

jar = """C:\Documents and Settings\msimcox\My Documents\NetBeansProjects\excelReadDividend\dist\excelReadDividend.jar"""
srcFile = """C:\\Managed Fund Distributions 2008.xls"""




retVal = Shell("C:\Program Files\Java\jre1.6.0_05\bin\java.exe -jar " & jar & srcFile, vbNormalFocus)


End Sub

The actual command that runs the java is:


C:\Program Files\Java\jre1.6.0_05\bin\java -jar "C:\Documents and Settings\msimcox\My Documents\NetBeansProjects\excelReadDividend\dist\excelReadDividend.jar" "C:\\Managed Fund Distributions 2008.xls"

I believe its to do with escaping the " in the strings but can't seem to work around it. Any help would be fantastic.

Also is there anyway to get the name of the current workbook programmatically i.e currentwkbk.getName(). This would allow for name changes etc.

Many thanks.

 

[Bong]

retVal = Shell("C:\Program Files\Java\jre1.6.0_05\bin\java.exe -jar " & jar & srcFile, vbNormalFocus)

Your problem may be as simple as putting a space (" ") between jar and srcFile:

retVal = Shell("C:\Program Files\Java\jre1.6.0_05\bin\java.exe -jar " & jar & [red] & " " &[/red] srcFile, vbNormalFocus)

_________________
Bob Rashkin