Exporting to a text file

Posted by Kevin on February 11, 2002 10:49 AM

I have a VBA procedure which performs a simulation, recalculating the spreadsheet as many times as the user requests (via an inout box). There are RAND()'s scattered about the workbook, so each iteration will produce different results. I want to be able to output some of the results (which are stored in a range) of each recalculation to one text file (since there could be too many rows to conveniently fit in an Excel workbook). Is there any way to output successive recalculations to THE SAME text file, with each iteration's results being appended to the text file?

Thanks for your help!

Posted by DK on February 11, 2002 11:19 AM

You can write a text file using VBA. This example creates a text file and writes the range A1:J1 (comma delimited) to the file. It then recalculates the active worksheet and writes another line. If you can't get this to work, post more details (e.g. the range you're wanting to write) and I'll see what I can do.

Sub WriteFile()
Dim intFileNum As Integer, lngNumberOfRecalcs As Long, lngCell As Long

intFileNum = FreeFile

Open "C:\results.csv" For Output As intFileNum
For lngNumberOfRecalcs = 1 To 10
'Write range A1:J10 10 times over
For lngCell = 1 To 10
Write #intFileNum, Sheet1.Cells(1, lngCell);
Next lngCell
Write #intFileNum,
Sheets("sheet1").Calculate
Next lngNumberOfRecalcs

Close #intFileNum
End Sub

HTH,
D

Posted by Kevin on February 15, 2002 6:18 AM

Thanks for the tip (and sorry for the delayed response -- this is the first chance I have had to check back here). Just to make sure, though: the "Open ... For Output" method will work even if the text file already has (say) 100,000 rows? (Excel wont try to open it as a workbook and crash because there are too many rows?)