fortran to excel without deleting existing file

[rk_19]

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

 

[xwb]

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?

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

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:
[ol 1]
[li]Create the input data in Excel (if required), and save it to a text file[/li]
[li]Run the program from VBA using the WScript.Shell object[/li]
[li]Read the resulting output files and save to a named range[/li]
[/ol]

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/

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

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/

 

[rk_19]

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'.

 

[xwb]

So how are you creating sheet1?

 

[rk_19]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

there's this:

http://www.adeptscience.co.uk/uncategorized/qtxls-readwrite-excel-fortran.html

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 forum1529

 

[xwb]

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.