-1

i would like to call an Octave script from VBA with a batch file and passing some arguments to it. The Octave script creates a .csv file which is needed in the VBA code later on. The Problem is, that the VBA code doesn't wait for Octave creating the .csv file and i get an error message that the file is not there. So i need a way to wait until Octave has created the file.

Here is my VBA code:

Public Sub LinkOctave(ByVal Projekt As String, ByVal Winddaten As String, ByVal Ak As String, ByVal Typ As String, _
                      ByVal Anz As Double, ByVal NH As Double, ByVal Netto As Double, _
                      ByVal StartGW As Double, ByVal EndGW As Double, _
                      ByVal StartP As Double, ByVal EndP As Double)

    Dim PID As Variant
    Dim BatchFilePath As String

    Dim ProjektBat As String
    Dim WinddatenBat As String
    Dim AkBat As String
    Dim TypBat As String
    Dim AnzBat As Double
    Dim NHBat As String
    Dim NettoBat As String
    Dim StartGWBat As String
    Dim EndGWBat As String
    Dim StartPBat As String
    Dim EndPBat As String


    ProjektBat = "'" & Projekt & "'"
    WinddatenBat = "'" & Winddaten & "'"
    AkBat = "'" & Ak & "'"
    TypBat = "'" & Typ & "'"

    AnzBat = Anz

    NHBat = str(NH)
    NettoBat = str(Netto)
    StartGWBat = str(StartGW)
    EndGWBat = str(EndGW)
    StartPBat = str(StartP)
    EndPBat = str(EndP)

    BatchFilePath = "C:\Users\bla\Documents\Octave\PFM\runfctPFM.bat"
    PID = Shell(BatchFilePath & " " & ProjektBat & " " & WinddatenBat & " " & AkBat & " " & _
                TypBat & " " & AnzBat & " " & NHBat & " " & NettoBat & " " & StartGWBat & " " & _
                EndGWBat & " " & StartPBat & " " & EndPBat)
End Sub

I already found the way to do it with wsh.Run like this:

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim lngErrorCode As Long

    lngErrorCode = wsh.Run(BatchFilePath & " " & ProjektBat & " " & WinddatenBat & " " & AkBat & " " & _
                TypBat & " " & AnzBat & " " & NHBat & " " & NettoBat & " " & StartGWBat & " " & _
                EndGWBat & " " & StartPBat & " " & EndPBat, windowStyle, waitOnReturn)

    If lngErrorCode <> 0 Then
        MsgBox "Something went wrong with the batch file!"
        Exit Sub
    End If

But it still doesn't wait for Octave to finish. What did i wrong? Or is there another solution?

This is the code of the bat file, where i call the octave script:

@echo off

set OCT_HOME=C:\Octave\Octave-5.1.0.0\mingw64\
set "PATH=%OCT_HOME%\bin;%PATH%"

set SCRIPTS_DIR=%~dp0
set ProjektBat=%1
set WinddatenBat=%2
set AkBat=%3
set TypBat=%4
set AnzBat=%5
set NHBat=%6
set NettoBat=%7
set StartGWBat=%8
set EndGWBat=%9
shift /1
set StartPBat=%9
shift /1
set EndPBat=%9

start octave-cli.exe --eval "cd(getenv('SCRIPTS_DIR')); fctPFM(%ProjektBat%, %WinddatenBat%, %AkBat%, %TypBat%, %AnzBat%, %NHBat%, %NettoBat%, %StartGWBat%, %EndGWBat%, %StartPBat%, %EndPBat%); quit;"

Thanks for your support Cheers

Pigmy
  • 1
  • 1
  • 2
    How are you running octave in the bat file? If the bat file is returning ie: octave isn't waiting to finish, then the bat file will be completing. – lostbard Nov 22 '19 at 13:19
  • Why do you want/need a batch file for starting Octave? why not running it directly from VBA? – aschipfl Nov 22 '19 at 18:06
  • @aschipfl: Because I don't know how to start the octave script from VBA and pass the arguments to it ; ) – Pigmy Nov 25 '19 at 07:17
  • @lostbard: I added the batch file but i don't understand what you mean. – Pigmy Nov 25 '19 at 07:20
  • Take a look at the [Shell function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function) (here is an [example](https://stackoverflow.com/q/20917355))... – aschipfl Nov 25 '19 at 08:51
  • @aschipfl : Is G in the example the variable name or the value of the variable? For example if i have an integer x = 5, i have to pass it like this "/x" or like "/5"? – Pigmy Nov 26 '19 at 13:30
  • No, it's just an argument (a switch) of the command... – aschipfl Nov 26 '19 at 23:50
  • @aschipfl: Sorry, but I don't get it. What is it for and how do I use it? Could you maybe give me an example how to run the octave script directly from VBA with passing multiple variables to it and wait for octave to finish? That would help, a lot – Pigmy Nov 27 '19 at 07:08

1 Answers1

0

The bat file has a 'start' command in it which according to the Microsoft docs opens a separate command prompt for the command - so its not going to wait for the separate command window to finish.

https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/start

Note also in windows, you should probably not be calling the octave executables directly, rather use the .bat files that come with it, as they set up expected environment variables in order for octave to run correctly.

lostbard
  • 5,065
  • 1
  • 15
  • 17
  • So you suggest me to do ...? Use the /wait command? I'v tried it, it didn't work. I've started Octave with the .bat and in addition to the not waiting problem the command window doesn't close itself. I'm completely lost here and have no experience with .bat files. – Pigmy Nov 26 '19 at 10:32
  • Why not just removing `start` at all? – aschipfl Nov 27 '19 at 08:11
  • @aschipfl I removed the `start`, VBA still doesn't wait for octave to finish – Pigmy Nov 27 '19 at 09:08