Exporting sheet as text without modifying workbook

[Jarmo Hurri]

Hi Excel gurus!

I've been trying to program a small Excel workbook that calls DOS
binaries written with C++ to do the actual computational work. With
the help of previous articles in this newsgroup, calling executables
and waiting for their execution to end now works perfectly.

However, I still have a minor problem in passing output to the
programs. What I need to do is to write a number of sheets, with
macros / VBA code, in different text files before running the
programs. This can be achieved by using the SaveAs macro as follows:

Worksheets(1).SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS

However, the problem with this approach is that it somehow modifies
the current workbook by turning sheet 1 into file1.txt. This is not
what I want - I only want to export / write the data without modifying
the workbook.

I have found various awful-looking hacks in some newsgroups for doing
the exporting, but the articles I saw were fairly old. I would think
that by now there would be a more convenient way to achieve what I'm
trying to do. Is there one?

Thanks in advance for all your help.

 

[JE McGimpsey]

You could write out your text files directly using VBA. See

http://www.mcgimpsey.com/excel/textfiles.html

for examples.

 

[Jarmo Hurri]

Jarmo> However, I still have a minor problem in passing output to the
Jarmo> programs.

Oops, had to correct that one, the problem is of course in passing
*input* to the programs.

 

[Jan Karel Pieterse]

Hi Jarmo,

Worksheets(1).SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS

Make a copy of the worksheet in question first:

Worksheets(1).Copy
Activeworkbook.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
Activeworkbook.close false

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jarmo Hurri]

JE> You could write out your text files directly using VBA. See
JE> http://www.mcgimpsey.com/excel/textfiles.html
JE> for examples.

Yes, I know I could start writing / copying macros for doing this. But
that would seem a bit weird since SaveAs produces *exactly* the type
of output that I need - the only problem is that it modifies the
workbook. So Excel seems to have the functionality that I need, I just
feel that there's probably (or there should be) a way to tap that
functionality without the unwanted side effects.

 

[JE McGimpsey]

In that case, try copying the sheet to a separate workbook and saving
that as text:

ActiveSheet.Copy
With ActiveWorkbook
.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
.Close False
End With

 

[Jarmo Hurri]

Jan> Make a copy of the worksheet in question first:

Jan> Worksheets(1).Copy
Jan> Activeworkbook.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
Jan> Activeworkbook.close false

That sounds exactly like the kind of solution I was hoping to find (no
field fiddling needed). Thanks Jan!