INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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.

Jobs

fortran to excel without deleting existing file

fortran to excel without deleting existing file

(OP)
Hi, I have an excel which is fed with equations on sheet2. from my fortran program, I want to write output on sheet1 of this excel - is this possible. I know how to write the output to excel, unfortunately, it deletes the existing excel and makes a fresh copy in the folder - where by I loose the excel with equations/macros on the sheet2. I want the equations/macros on sheet2 to work on the extracted values on sheet1...appreciate your suggestion on how to write into the same excel file.thanks

RE: fortran to excel without deleting existing file

How are you writing the output to excel? Are you using COM or creating a CSV or creating an xml file and zipping it or using some interaction with VBA?

RE: fortran to excel without deleting existing file

I'd write to the same .csv file each time.

In Excel have a sheet that uses the Import From Text File feature in the Data TAB, to ONE TIME create an import that can be refreshed on command. Your workbook could be configured to refresh that import in the Workbook_Open event, so that the latest data would appear in that sheet each time the workbook is opened.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: fortran to excel without deleting existing file

I happen to be looking at linking Excel to Fortran based compiled programs on my blog at the moment.

Have a look at: Running exe files from Excel

The process is:
  1. Create the input data in Excel (if required), and save it to a text file
  2. Run the program from VBA using the WScript.Shell object
  3. Read the resulting output files and save to a named range
The Fortran program in my example produces space delimited files, which are read and split into cells using two VBA functions (ReadText and SplitText), which are included in the download file. Let me know if you would like a modified version to read csv files.

Following posts look at the process of compiling the Fortran (using GFortran) as an exe file, then compiling as a dll so that data can be passed directly from and to Excel.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: fortran to excel without deleting existing file

Quote (Doug)

The Fortran program in my example produces space delimited files, which are read and split into cells...
This is what this built-in Import feature does in Excel, parses your input text file: either a FIXED RECL format or DELIMITED.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: fortran to excel without deleting existing file

Quote (Skip)

This is what this built-in Import feature does in Excel, parses your input text file: either a FIXED RECL format or DELIMITED.

I know, but I find the built in import cumbersome to set up, inflexible, and lacking in many features I need, so I wrote my own.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: fortran to excel without deleting existing file

(OP)
hi, i am not sure if I was clear with the problem...the usual process is that the output from fortan is produced in .xls format and this is manually copied on to another excel file with 2 sheets in it. the sheet 1 receives the output from fortran and sheet 2 has macro written on it which will process the information on its sheet1. what I wish is fortran should directly write the output to sheet1 of the file, and I can run the macro on sheet 2 to process it. this will avoid the manual copying of data from one file to another. assume the excel file with sheet1,2 is calculation.xls.. but when I get fortran write the output to 'calculation.xls', it destroys the original 'calculation.xls'(which had sheet1 and sheet2) and replaces it with a fresh 'calculation.xls'.

RE: fortran to excel without deleting existing file

So how are you creating sheet1?

RE: fortran to excel without deleting existing file

(OP)
earlier, I was manually pasting the fortran output into sheet1 and then run the macro on sheet 2... but now when I try to write the fortran output directly into the sheet1, the whole excel is getting replaced and so I loose the sheet (sheet 2) where I had my macros... I do understand that I can write the fortran output to a text/csv file and keep another excel ready with a macro which can read the text/csv file to its sheet1.

RE: fortran to excel without deleting existing file

If your FORTRAN program generates an .xls workbook, then you can still use a similar feature via Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* ... and drill down to your workbook. http://www.tek-tips.com/faqs.cfm?fid=5829

I've been using this acquisition technique for over 20 years. Set up in a few minutes. It's a ONE TIME TASK. Get data from tables in your own workbook, other workbooks, other databases like Access, Oracle, DB2 and text files: it's very versatile.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: fortran to excel without deleting existing file

there's this: http://www.adeptscience.co.uk/uncategorized/qtxls-...

Alternately, you could simply leave the output file in CSV format, and modify your macro to open the file directly and either automatically copy the data into the macro's workbook, or manipulate the data in situ.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

RE: fortran to excel without deleting existing file

If you are still keen on writing an excel file, try the following

1) Open you spreadsheet
2) Save it as a XML Spreadsheet 2003(*.xml). Let us call this top.xml. Note that there is an xml format further up: don't use that one. Use the one that says XML Spreadsheet.
3) Copy top.xml to bottom.xml
4) Open top.xml in notepad. Look for ss:Name=”Sheet1”. Look further down for the first <row>. Delete from that point to the end of the file. Save and close
5) Open bottom.xml in notepad. Look for ss:Name=”Sheet1”. Look for </Table>. Delete from the line before to the beginning of the file. Save and close
6) Write a program to do the following
a. Open a new spreadsheet, say madeup.xml
b. Copy the entire contents of top.xml to madeup.xml
c. Write out your data in the following format

i. For each row, print <Row ss:AutoFitHeight=”0”>
ii. For each column print <Cell><Data ss:Type=”Number”>x</Data></Cell> where x is the number
iii. To end the row, print </Row>
d. Copy the entire contents of bottom.xml to madeup.xml
e. Close madeup.xml
7) Open madeup.xml in excel and it should execute all your formulas.

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!


Resources


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