Linking Fortran Program (not simple subroutine) with excel using input file

[Anon_Ymous]

Hi,

FYI - I tried searching the forum using the terms "excel fortran dll" but only 1 thread actually shows up, so I did try looking and reading first! Also, I'm able to create simple subroutines in Fortran, create dlls, and link them with excel with no problems. Unfortunately, my issue is a little more subtle.

I have legacy engineering code that's written as a Fortran program (outlined below):

Main Program Outline
-----------------------
Module ModuleNameHere
stuff
End Module

Program ProgramNameHere

variable declarations stuff here

Then, here, there's a bunch of calls to subroutines here

End Program

Subroutines are all down here (below the main).
----------------------------

There's also some more .f90 files that are compiled together with the main program referenced above. When compiled, these scripts make a nice command line interface that accepts either user input or a previously created input file.

I would like to access this package from Excel. I thought I'd be able to work it out myself, but it's not as simple as just creating a Fortran subroutine, as you can see.

So the first thing: I should be able to generate the input file in a spreadsheet in excel, that's no problem. Easy enough!

The next thing: What would you guys do? To me, it seems like there's two approaches:
1) Use VBA to pass the input file data directly to the program, much like the excel example provided with Visual Studio 2008 using a DLL

Issues with this method include:
*Does passing data to a program work like passing data to a subroutine?
*Removing current command line UI
*hard-coding data read in to the proper variables on the Fortran side
*either reading the generated output file into excel (formatted .txt file) or passing values back into excel

2)Use excel to create the input file, use VBA to call the program directly, then read generated output .txt file into excel

Issues with this method include:
*It's clunkier than method one
*Still have the problem of waiting for the program to finish running then reading the data in
*how to write VBA that runs executable file?

So, as you can see, I have some problems. Maybe my best bet is to remove all the user interface stuff and just break the program down into subroutines (basically option #1)? But then, what's the best way to write the data to excel? Note that the input file has an arbitrary number of rows and the output file also has an arbitrary number of rows so array passing will come in handy here.

I'm sort of stuck at a fork in the road. If anyone can provide insight as to the best approach and/or best practices, or good books/manuals, I'd really appreciate it. Thank you for your help!

[IanH]

(Without looking at the VS2008 example code you mention...)

There's a third option too - including having your Fortran code use COM/Automation to make Excel dance (or vice versa). That's not for the faint hearted though (particularly the vice versa side!). And there are probably more.

Which option (and in some respects its more a continuum then discrete options) is best depends on your specific context. Aspects to consider include things like the nature of the calculation split between Excel and fortran, your familiarity/ability to modify the existing Fortran code; your familiarity with the (Excel flavour of) VBA and Fortran languages, how easy/difficult it is to get Excel to write input files and read output files (regular format text files like CSV - easy, some sort of unformatted "binary" file with variable records - could be ugly), how closely coupled you want your program with Excel (if you have documented input and output file formats then other applications might be able to read and write them); how much information needs to be transferred, installation and deployment issues; etc, etc...

I use all three options. For complete model runs some VBA code in excel writes an input file (these days it's in an xml format, but historically it was just a series of simple text records), the VBA code invokes the executable at a known location, when the run has finished the user clicks a button and the output text files created by the model are read into excel. Personally, for first steps this is where I'd go.

For quicker calculations (quick enough that there's no need for a separate calculation thread) the various fortran procedures (which are in static libraries, the exe above is just a simple main program that acts as a driver) are also linked into a DLL. That DLL has appropriate wrapper functions that allow Excel VBA to call the procedures - allowing me access to them directly from in-cell excel functions. Procedures that only need a moderate amount of information passed back and forth just do so via arguments, procedures that require lots of information get passed a COM handle to a worksheet that they then use to query/set the value of cells to their hearts content. Lots of flexibility, but there's a definite maintenance cost.

I use the same set of approaches to interface with Matlab.

In VBA you use the Shell built-in function to execute executables... for example:

[vb]'*******************************************************************************
'
' Attempts to execute the executable in path with the given argument.
'
' Returns zero in stat on success, non-zero on error.
'
'*******************************************************************************

Private Sub TryExecute(path As String, argument As String, _
    ByRef stat As Integer)

  Dim id As Variant     ' Program task identifier
  
  '*****************************************************************************
  
  On Error GoTo failure
  id = Shell("""" & path & """ """ & argument & """")
  stat = 0
  Exit Sub
  
failure:
  stat = 1
  
End Sub
[/vb]

[Anon_Ymous]

Ian,

As a novice who's basically teaching herself, I didn't realize the connections between VBA and FORTRAN could be so powerful (COM/automation, ect.). I should have expected as much because professional programmers certainly demand flexibility. Unfortunately, as you said, COM/automation probably isn't a real option for people at my skill level. It's neat enough to make me want to explore that kind of stuff down the road, though.

So I could easily re-compile the source code such that it immediately loads a user input file when ran, rather than waiting for user input at the terminal to prompt the program (I know enough about programming and FORTRAN to do that much myself). Then I need to use excel to

A) produce a properly formatted .txt file (in the same directory as the FORTRAN .exe) with a standard default name that overwrites any existing input file of the same name

B) Use the Shell command to execute the Fortran code

C) When the code has finished running, the user will probably have to press a button to import the output.txt file into a spreadsheet

A couple questions (if you don't mind):

1) Can you point me to a few resources where I can learn more about how to use the Shell command to execute a file? I'm preferrably looking for examples. I'm sure I'll be able to find some stuff just by searching myself, but I figured I'd ask you too since you probably know about better resources compared to what I might find.

2) Do you know of a good way to handle importing the output file into excel? I mean, I can do it in general. The problem is that excel doesn't know when the run has completed, so the user has to press a button to import the file. It would be nice if it would automatically do this (I'm hoping this doesn't require the COM/automation).

I think that's probably about it. You've been a huge help!

[anthonyrichards]

might these links help?

http://www.java2s.com/Code/VBA-Excel-Access-Word/File-Path/ExamplesoftheVBAOpenStatement.htm

http://zo-d.com/blog/archives/programming/vba-writing-to-a-text-file-ms-project-excel.html

Why not compile your Executable as .DLL and, instead of starting it with a PROGRAM...End Program, enter the program via a subroutine or function call made from EXCEL, to MAINPROGRAM or some such name? This could be given a filename as an input argument, and the program could put up a MessageBox just before it returns saying it has ended. It could even return a new filename if required, so that your EXCEL program could then open and read it.

[Joe_Longwell]

This is some VBA code I have used to execute an external program from Excel. The code "waits" in the do loop until the external program is done. A good VBA source is "Power Programming with VBA" by Walkenbach.


ACCESS_TYPE = &H400
STILL_ACTIVE = &H103

Program = """"Path to External Executable code""""

On Error Resume Next
TaskID = Shell(Program, 1)


' Get the process handle
hProc = OpenProcess(ACCESS_TYPE, False, TaskID)

If Err <> 0 Then
MsgBox "An error occured while executing the following External program command:" & vbCr & Program, _
vbCritical, "Beam2D Error Message"

On Error GoTo 0
Exit Sub
Else
On Error GoTo 0
End If

Do ' Loop continuously until External Job is completed.
' Check on the process
GetExitCodeProcess hProc, lExitCode
' Allow event processing
DoEvents
Loop While lExitCode = STILL_ACTIVE

[Joe_Longwell]

I forgot to mention that these functions must be declared:

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long



Joe

[jimdempseyatthecove]

Here is a non-programming clunkier way (easier done than said):

1) Create a Visual Studiosolution - a console app that does nothing
2) Right-Click on the startup project name | Add | Existing Item | Visual C++ | Utility | Text File (.txt)
enter your data file name | Add
3) Right-Click on the startup project name | Properties | Build Events | Pre-Build Event
In Command line enter your command line including the input text file name from 2) | Apply


As an experiment assume 2) above used "Test.txt" in the solution folder.
For your program enter:

NOTEPAD $(SolutionDir)Test.txt

Note the $(SolutionDir) can be obtained from the Macros, but this will change to your default input folder.

Open Excel up in a new window. Select the block of cells you want to use by your application (in this case by NOTEPAD), right click, pick Copy.

Click into theTest.txt file in the VS solution, Ctrl-V (paste). (or later Ctrl-A, Ctrl-V)

(you do not have to save it)
Click on Build (initially the dummy app will compile, 2nd time and later it will not)
NOTEPAD will open with the modified text
Edit the text, Exit, Save
The VS build now completes, notices the text file changed and asks if you want to reload it. Click Yes

Ctrl-A in Test.txt file window(select all)
Drag and drop onto your Excel sheet (or Paste Special)

The Visual Basic scripts may be a proper way of doing it. This is a relatively easy hack to use too.

Select block | Right Click | Copy
Click in Test.txt | Ctrl-A | Ctrl-v
Build
Yes
Ctrl-A
Drag-n-drop

Jim Dempsey

[Anon_Ymous]

Hi again everyone!

Wow, great responses here! I'm most interested in IanH's response though because that one seemed to help the most (not to say that the others wouldn't work, this is just the one that I'm becomming comfortable with).

First, making a subroutine in Excel that creates the formatted input file was a snap!

So far, on the Fortran side, I've been able to recompile the program into an .exe so that it just immediately updates it's database, reads the input file, and creates an output file.

Now, I just need to execute the Shell command, which is simple enough (I have Walkenbach's book now and it's clearly explained there). I can do this for standalone .exe files just fine...the problem is that, as I said before, the FORTRAN program I'm working with isn't a simple program.

So now I have an ugly, ugly problem.

You see, the fortran executable previously mentioned actually needs to call some batch files during it's run. For whatever reason, the Excel Shell command does not handle this well. The error I'm getting, which shows up on the windows command line, is copied below...

CMD.exe was not started with the above path as the current directory.
UNC paths are not supported. Defaulting to windows directory
'helperfile.bat' is not recognized as an internal or external command, operable
program, or batch file.

Any ideas on how to handle this? I feel like I'm so close! It's starting to get frustrating :(

[IanH]

In the following, I'm assuming that the error message you quote is coming from your Fortran program's attempt to run batch files, and not from the initial attempt by the VBA Shell function to call your Fortran program. (If it is the former, then are you sure that the "command" that you are passing to Shell is correct?)

Perhaps the current directory of the fortran executable is not what it expects. You can change the current directory of the excel process prior to invoking the fortran executable in VBA using the ChDir statement. Before you do so you may wish to query the original working directory using the CurDir function.

[vb]Dim old_path as String 
Di id as Variant

old_path = CurDir()
' ChDrive "Q:"   ' You may need to change the current drive too
Debug.Print "Before the call, the current directory was " & old_path
ChDir "Paththatmy fortran programexpects"
id = Shell("MyFortranProgram.exe")
' ChDrive old_path
ChDir old_path[/vb]

Note that the current directory is a process (excel) wide property. That makes it prone to being changed by things in the process other than your VBA code - for example navigating to a different directory using the standard file open or file save dialog boxes can (in some cases) change the current directory for a process.

You also could deal with directory issues outside of excel. Rather than invoking the fortran exe directly you could have a wrapper batch file that set the directory appropriately before invoking the fortran executable.. Or, you could deal with it within the fortran program proper. The fortran standard does not deal with the concept of directories as far as I'm aware, but intel fortran provides some portability routines (see ChDir, ChangeDirQQ, GetDriveDirQQ, and friends) to help you out. You may also be able to modify the batch scripts that the fortran program calls to make them tolerant of a different working directory.

Then again, perhaps your problems have nothing to do with the current directory, in which case we'd need more specific details in order to help.

[Anon_Ymous]

IanH,

I just ninja-edited my last post. That's all irrelevant. Our security settings were disabling the program. We have it working now! Thanks for all of the help guys! Woooooooot!!

[bansalmaddy]

Hallo IanH

I read ur discussions with 'anonymous' regarding link between excel and fortran, i am not much of a programmer, i have a mechanical background... i do some mathematical tasks in fortran and have quite a big set of codes inFORTRAN thr r many .for files with lot of variables calling each other. also i pass data with a input txt file and ask results also the same way.

now i want to access all this using Excel and make a interface there, i tried making dll, and simple .for code ia m able to link by passing some variables as arguments and also ask for the result, - it works , how do i deal with bigger stuf now, what is this COM\automation thing, i read u post but cant understand much..!

Can you please help me.