Page 1 of 1

Excel2Gams VBA problem

Posted: Thu Nov 30, 2017 11:51 am
by David

I am trying to run a couple of instances of gams in a sequential manner.
I use excel to change parameters and other input data and I execute gams from a VBA macro which executes Gams through acommand shell.

I have tried some approaches to wait for the GAMS code to finish running to change the parameters and execute the gams code automatically, but so far none has worked.

Approaches I have tried :
In VBA run a wait code so the VBA code waits around 1 minute (while my GAMS code runs in around 45sec).
In VBA run a code so it checks for a results file that GAMS creates to appear and then continue with code.
In vba check if the gams.exe process is running and as soon as it stops running (+5sec) continue with the code.

All these approaches make GAMS stop before running the model. The only approach that seems to be working is to set a msgbox and when gams stops running I manually click on the message box button to continue with the VBA code.

The cmd stops at this point, where it normally continues running creating all the results files.

Thanks for your help.

Re: Excel2Gams VBA problem

Posted: Thu Nov 30, 2017 2:25 pm
by bussieck

We have a number of VBA (Excel) examples in datalib (e.g. ... folio.html)
that use the following way to synchronously call GAMS:

Code: Select all

   command = "gams " & fln
   result = CreateProcess(vbNullString, command, 0, 0, 0, NORMAL_PRIORITY_CLASS, 0, wdir, startup_info, process_info)
   If result = 0 Then
      result = GetLastError()
      txt = "Error code: " + Str(result)
      MsgBox txt
      result = WaitForSingleObject(process_info.hProcess, INFINITE)
   End If
Why does this not work for you?


Re: Excel2Gams VBA problem

Posted: Thu Nov 30, 2017 5:11 pm
by David
The difference is that my model is already written in a .gms file and the model itself creates all the necessary gdx files.

So when I try to adapt this code to run my model (deleting exportgdxfile,writegamsmodel and importgdxfile) and change the directories to the correct ones for my model it doesn't run. It just briefly shows the command window for 1 millisecond. This is my VBA cose to call GAMS :

Code: Select all

Public Function GAMSRunv1(sGAMSSystemExec As String, sGAMSModel As String, sGAMSclparams As String, processDir As String) As Long
    Dim result As Long
    Dim command As String
    Dim hWnd As Long, lSecs As Long
    Dim lCreateFlag As Long
    Dim lRetAPI As Long, lRetProcess As Long, lRetWait As Long, lRetExit As Long, lRetErr As Long
    Dim sTitle As String, lRetWT As Long, lLength As Long, sFinished As String
    Dim sMsgText As String, sGAMSErrorText As String
    Dim GAMSListing As String
    Dim txt As String
    Dim console As Long
       Const INFINITE = -1&


    sGAMSSystemExec = Trim(sGAMSSystemExec)
    'Altenatively, you can set the gams executable manually
    'sGAMSSystemExec = "C:\GAMS\win64\23.8\gams.exe"
    sGAMSModel = Trim(sGAMSModel)
    sGAMSclparams = Trim(sGAMSclparams)
    If (Len(sGAMSModel) < 4 Or Dir(sGAMSSystemExec) = "" Or Dir(sGAMSModel) = "" Or StrComp(Right(sGAMSModel, 4), ".gms") <> 0) Then
        MsgBox "GAMSRunv1 Error - GAMS System Directory or Model is invalid"
        Exit Function
    End If
    ' define gams listing file name
    GAMSListing = processDir & Mid(sGAMSModel, InStrRev(sGAMSModel, "\"), Len(sGAMSModel) - 3 - InStrRev(sGAMSModel, "\")) & ".lst"
    'form gams call for dos
    command = Chr(34) & sGAMSSystemExec & Chr(34) & " " & Chr(34) & sGAMSModel & Chr(34) & " " & sGAMSclparams
    sFinished = "FINISHED"
    lLength = Len(sFinished) + 1
    ' wait in milliseconds for process completion
    lSecs = 1000
    ' set default return value
    lRetProcess = -1
    StartUp.lpTitle = "VB GAMS"
    StartUp.wShowWindow = SW_SHOW ' SW_SHOWMINNOACTIVE
'    StartUp.dwFlags = StartUp.dwFlags + STARTF_USEPOSITION + STARTF_USESIZE
    StartUp.dwXSize = 800
    StartUp.dwYSize = 600
    StartUp.cb = Len(StartUp)

    ' Call CreateProcess to actually run GAMS
    result = CreateProcess(vbNullString, command, 0&, 0&, 1, lCreateFlag, 0&, processDir, StartUp, udtProcess)
    If result = 0 Then
      result = GetLastError()
      txt = "Error code: " + Str(result)
      MsgBox txt
      result = WaitForSingleObject(udtProcess.hProcess, INFINITE)
   End If
    CloseHandle (udtProcess.hThread)
   CloseHandle (udtProcess.hProcess)