×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

interface between excel and MSDOS file

interface between excel and MSDOS file

interface between excel and MSDOS file

(OP)
Hi
  I want to import some data from excel spreadsheet to MSDOS file (or text file).
   I have some fortran programmes written and got executable file. I want interface with excel. for example.. if i want to change some number in my programme, i will change it in excel spreadsheet and that will be imported in my file. and after executing that programme, results will be exported to excel to display there.
  I am sorry if my words are not much scientific, but i hope that you will get some idea what i want to do.
  Any help will be highly appreciated.

Prashant

RE: interface between excel and MSDOS file

Prashant,

I have used Excel to create a nice, evenly spaced text file that was used as an input file for a DOS program that was likely Fortran based.  I would think a lot depends on your Fortran program input requirements though.

For my case, I could enter the required data in individual cells in Excel.  There were several rows of data that were required.  Then select File/Save As to create your input file.  When you get to the Save As window, select a Save As Type suitable for your program input.  Mine happened to like Formatted Text (Space delimited).  Others that might be suitable could be Text (Tab delimited) or CSV (Comma delimited).

As for getting the output back to Excel, again that depends on your Fortran output but likely it will be in a text file format.  You should be able to open directly from Excel.  Once the data is back in Excel you will need to select Data/Text to Columns to convert it back into a spreadsheet form.

RE: interface between excel and MSDOS file

What I've done in the past is to use Excel as the entry table for parameters, call up Word to take the parameters and copy them into the input file, run the DOS program, wait for completion, open the output file in Word, find and copy an output value into a calculation sheet in Excel and copy the results from the calculation into the input sheet.

In VBA, I had the routine looping over a number of rows so that we could get parametric analysis of a design point.

TTFN

RE: interface between excel and MSDOS file

(OP)
Thank you very much for the tips. I will try to use them ans see what happens.

regards
Prashant

RE: interface between excel and MSDOS file

(OP)
Dear EGT01 and IRstuff
    I think you have some experience what i am looking for. Let me explain you in detail what are steps i need.

1. Put input data on spreadsheet.
2. Click button on spreadsheet (some link throuh macro throuh visual basic????? )
3. This will put this input data for fortran file.
4. This file will be executed.
5. This fortran programme generates text file for output data.
6. This output is then imported to excel spreadsheet to display.

So i want the excel interface for user who will give his needs and get the output. Other things like fortran programme runs in the background.

I think you have specific idea what i am looking for. Can you tell where exactly i can start now and where i can find the relevant programming knowledge for the same???

 i have found the way to put data fron excel into text file through visual basic. but i have specific conceren about how i can tell from excel to run my fortran routine.

thanks in advance for the help

Regards
Prashant

RE: interface between excel and MSDOS file

shell

Runs an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero.

Syntax

Shell(pathname[,windowstyle])

The Shell function syntax has these named arguments:

Part    Description
pathname    Required; Variant (String). Name of the program to execute and any required arguments or command-line switches; may include directory or folder and drive.
windowstyle    Optional. Variant (Integer) corresponding to the style of the window in which the program is to be run. If windowstyle is omitted, the program is started minimized with focus.
The windowstyle named argument has these values:

Constant    Value    Description
  
vbHide    0    Window is hidden and focus is passed to the hidden window.
vbNormalFocus    1    Window has focus and is restored to its original size and position.
vbMinimizedFocus    2    Window is displayed as an icon with focus.
vbMaximizedFocus    3    Window is maximized with focus.
vbNormalNoFocus    4    Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus    6    Window is displayed as an icon. The currently active window remains active.
Remarks

If the Shell function successfully executes the named file, it returns the task ID of the started program. The task ID is a unique number that identifies the running program. If the Shell function can't start the named program, an error occurs. If you use the MacID function with Shell in Microsoft Windows, an error occurs.

Note   The Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.

Cheers

Greg Locock

RE: interface between excel and MSDOS file

Very old and not documented code

Main macro:

Sub Flir92()
'
' Flir92 Macro
'

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Dim ColIndex, RowIndex, Done, EntryValue, test
    Dim EntryString, AcqFile, AcqFileName, PauseTime, StartTime
    Dim DwellPath, ExcelDwellPath, FileName, SheetName, Flir92File
    Dim Comma, Quote, FileKey, KeyCol, KeyRow, ColMax, ColumnHead, TempFlag, Temp
    Comma = ","
    Quote = """"
    
    Dim EnvString, Indx, Msg, PathLen, SysRoot, BatPath
    SysRoot = Environ("SystemRoot")   ' Get environment variable.
    
    Dim WordApp As Word.Application
    Dim AcqBook As Object
    
    On Error Resume Next
    If Tasks.Exists("Microsoft Word") Then Set WordApp = GetObject(, "word.application")
    If WordApp = "" Then Set WordApp = CreateObject("word.application")
    On Error GoTo 0
    Err.Clear
    
    WordApp.WindowState = wdWindowStateMinimize
    WordApp.Visible = True
    
    Application.DisplayAlerts = False
    
    FileName = ActiveWorkbook.Name
    SheetName = ActiveSheet.Name
    Sheets("MRTSheet").Select
    KeyRow = ActiveCell.Row
    KeyCol = ActiveCell.Column
    Cells(KeyRow, KeyCol).Select
    Flir92File = Range("Flir92File").Value
    ExcelDwellPath = ActiveWorkbook.Path & "\" & Flir92File
    
    DwellPath = ExcelDwellPath
    BatPath = ActiveWorkbook.Path
    
    AcqFile = "Xl_acqmw2.xls"
    AcqFileName = ActiveWorkbook.Path & "\" & AcqFile
    Set AcqBook = GetObject(AcqFileName)
    
    Sheets(SheetName).Select
    Done = False
    ColIndex = 1
    RowIndex = ActiveCell.Row
    Cells(RowIndex, ColIndex).Select
    FileKey = ActiveCell.Value
    ColumnHead = Range("field_temp").Value
    
    
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    While Not Done
        If FileKey <> "" Then
            Call OpenFlir92(WordApp, DwellPath)
            Application.StatusBar = "Editing Input File..."
            ColMax = Range("PerformanceOutput").Column - 2
            TempFlag = False
            For Column = ColIndex + 1 To ColIndex + ColMax
                EntryValue = Cells(RowIndex, Column).Value
                If EntryValue <> "" Then
                    If IsNumeric(EntryValue) Then
                        If EntryValue < 1000 Then
                            EntryValue = Application.WorksheetFunction.Text(EntryValue, "0.000")
                        Else
                            EntryValue = Application.WorksheetFunction.Text(EntryValue, "0.000E+00")
                        End If
                    End If
                    EntryString = Cells(3, Column).Value
                    If EntryString = ColumnHead Then
                        TempFlag = True
                        Temp = Cells(RowIndex, Column).Value
                    End If
                    Call ModFlir92(WordApp, EntryString, EntryValue)
                End If
            Next Column
            
            Call CloseFlir92(WordApp)
            
            Application.StatusBar = "Running Flir92..."
            Application.DisplayAlerts = True
            Nyquist = Range("Nyquist").Value
            Call RunFlir92(BatPath, SysRoot, Flir92File, Nyquist)
            Call GetFlir92MRT(WordApp, DwellPath)
            Application.StatusBar = "Getting MRT..."

            Windows(FileName).Activate
            
            Sheets("MRTSheet").Select
            ActiveCell.Value = FileKey
            Cells(KeyRow + 1, KeyCol).Select
            ActiveSheet.Paste
            KeyCol = KeyCol + 2
            If KeyCol > 250 Then
                KeyCol = 1
                KeyRow = KeyRow + 25
            End If
            Cells(KeyRow, KeyCol).Select
            
            Windows(AcqFile).Activate
            Sheets("Main Calculations").Select
            Range("A12").Select
            ActiveSheet.Paste
            
            If TempFlag = True Then
                Windows("LT7Dat1.xls").Activate
                Select Case Int(Temp)  ' Evaluate Number.
                    Case 244, 257
                        Range("Tran257").Select
                    Case 272
                        Range("Tran272").Select
                    Case 287
                        Range("Tran287").Select
                    Case 294
                        Range("Tran294").Select
                    Case 308
                        Range("Tran308").Select
                    Case 322
                        Range("Tran322").Select
                End Select
                Selection.Copy
            Windows(AcqFile).Activate
            Range("MLS23").Select
            ActiveSheet.Paste
            End If
            
            Range("RangePerformance").Select
            Selection.Copy
            Windows(FileName).Activate
            Sheets(SheetName).Select
            Cells(RowIndex, Range("PerformanceOutput").Column).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True
        
            Windows(AcqFile).Activate
            Sheets("Main Calculations").Select
            Range("OtherPerformance").Select
            Selection.Copy
            Windows(FileName).Activate
            Sheets(SheetName).Select
            Cells(RowIndex, Range("PerformanceOutput2").Column).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True
        
        End If
        
        RowIndex = RowIndex + 1
        ColIndex = 1
        Cells(RowIndex, ColIndex).Select
        FileKey = ActiveCell.Value
        If FileKey = "" Then Done = True
    Wend
    Application.DisplayAlerts = True
    Application.StatusBar = ""
End Sub

Macro that edits text files in Word from Excel:


Sub ModFlir92(WordApp, EntryString, EntryValue)
'
    WordApp.Selection.Find.ClearFormatting
    With WordApp.Selection.Find
        .Text = EntryString
    End With
    WordApp.Selection.Find.Execute
    WordApp.Selection.MoveRight unit:=wdCharacter, Count:=1
    
    WordApp.Selection.Find.ClearFormatting
    With WordApp.Selection.Find
        .Text = "^#"
    End With
    WordApp.Selection.Find.Execute
    
    
    WordApp.Selection.Find.ClearFormatting
    WordApp.Selection.ExtendMode = True
    With WordApp.Selection.Find
        .Text = " "
    End With
    WordApp.Selection.Find.Execute
    WordApp.Selection.MoveLeft unit:=wdCharacter, Count:=1, Extend:=wdExtend
    WordApp.Selection.TypeText Text:=EntryValue
    WordApp.Selection.HomeKey unit:=wdStory
End Sub


Macro that runs .bat file:
Sub RunFlir92(BatPath, SysRoot, DwellPath, Nyquist)

    Dim ShellPath, PauseTime, StartTime, EndTime, RunTime, TimerTemp
    ChDir (BatPath)
    If Dir(DwellPath & ".2") <> "" Then Kill (DwellPath & ".2")     'Deletes output file to make
                'sure that there isn't a hangup from WORD
    If Dir(DwellPath & ".trm") <> "" Then Kill (DwellPath & ".trm")     'Deletes flag file to make
                'sure that there isn't a hangup
      
    ShellPath = SysRoot & "\system32\zona.bat " & DwellPath & " " & Nyquist
    RetVal = Shell(ShellPath, vbNormalFocus)
    
    Do While Dir(DwellPath & ".trm") = ""
    Loop
       
    PauseTime = 2
    StartTime = Timer
    Do While Timer < StartTime + PauseTime
    Loop
    
    On Error GoTo KillTrm       'forces delete when permission denied error occurs
    
KillTrm:
    If Dir(DwellPath & ".trm") <> "" Then Kill (DwellPath & ".trm")     'Deletes flag file to make
    
    EndTime = Timer
    RunTime = EndTime - StartTime
End Sub

Macro that retrieves data from output file in Word from Excel:

Sub GetFlir92MRT(WordApp, DwellPath)
'
'
    WordApp.Application.DisplayAlerts = False
    WordApp.Documents.Open FileName:=DwellPath & ".2", PasswordTemplate:= _
        "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
        Format:=wdOpenFormatAuto, ReadOnly:=True
    
    WordApp.Selection.EndKey unit:=wdStory
    WordApp.Selection.HomeKey unit:=wdLine
    WordApp.Selection.MoveUp unit:=wdLine, Count:=70, Extend:=wdExtend
'    WordApp.Selection.MoveUp Unit:=wdLine, Count:=21, Extend:=wdExtend
    Call MakeTable(WordApp)
    WordApp.Selection.MoveDown unit:=wdLine
    WordApp.Selection.MoveDown unit:=wdLine, Count:=20, Extend:=wdExtend
    WordApp.Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=10, _
        NumRows:=20
    WordApp.Selection.HomeKey unit:=wdLine
    WordApp.Selection.MoveRight unit:=wdCell
    WordApp.Selection.MoveRight unit:=wdWord, Count:=2, Extend:=wdExtend
    WordApp.Selection.MoveDown unit:=wdLine, Count:=19, Extend:=wdExtend
    WordApp.Selection.Copy
    WordApp.ActiveDocument.Close (wdDoNotSaveChanges)
End Sub

.bat file that runs program and creates dummy file when program completes:

d:\memo\c995\cobra\flir92 -d %1 -o %1 -p MRT -nyq %2
rem pause
copy %1 %1.trm

TTFN

RE: interface between excel and MSDOS file

(OP)
friends

  thanks a lot. i think i have idea how to proceed.
thanks again

regards
Prashant

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close