Excel Sheet "Save As" Text File without altering active document.

[andysuth]

I'm trying to save the contents of an Excel Worksheet as a text file so as to archive what has been input.

I was trying to use:

[vba]
ActiveWorkbook.SaveAs Filename:=pathstring & versionint & filestring & ".txt", FileFormat:=xlTextPrinter
[/vba]

but this changes the current document to an TXT from an XLS, and I want to keep it as a XLS.

Anyone know of a command switch to save a file as some file name without switching the active document to the new file name?

Cheers,

-AS

[daniel_d_n_r]

sorry i posted here but the code was wrong.

hope somone gets an answer too you,,

im off to bed

[andysuth]

It's still changing the file name, but there again I tried slicing and dicing the code so it was in the mian function of the MAcro, does it have to be in the "Option Explicit" sub for it to work?

-AS

[andysuth]

I've tried it as a "Explicit" sub routine too, and firstly it draws a line between explicit and the sub title line, and then it still renames the worksheet as versionint & filestring, and the whole workbook as versionint & filestring & ".txt",

that's was what was messing up originally, is it a setting in Excel I need to change?

Cheers,

-AS

[andysuth]

Here's what I've actually written:

[vba]
Option Explicit
Sub textbackup(verint)
Application.DisplayAlerts = False
ChDir pathstring
ActiveWorkbook.SaveAs Filename:=(verint & filestring & ".txt"), FileFormat:=xlText, _
CreateBackup:=False
Application.DisplayAlerts = True
End Sub
[/VBA]

[daniel_d_n_r]

[vba]Option Explicit
Sub ws_Text()
Dim ws As Worksheet
Dim s As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
s = ws.Name
Sheets(s).Select
Sheets(s).Copy
ActiveSheet.SaveAs Filename:= _
"C:\logs\" & ws.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next

End Sub[/vba]

This works ,you may be able to tweak it,without displaying alerts it will simpy overwrite any text files of the same name(so if you have 1 sheet in a workbook called "sheet1",when you run this code in another workbook and it has a "sheet1",it will simply overwrite the first "sheet1.txt" if it is saved to the same folder, possibly you could keep alerts on,save to a different folder for eack workbook,or keep you names different,or a variation of all of these options added to the code).
The option explicit is not really necessary it just forces all variables to be declared before you run the code, its just good practice if you have a lot of code in one place.

cheers

[andysuth]

Cheers,

I'll try that in a minute, thanks for your help.

What's DNR, "Do Not Resuscitate"?????

-AS

[daniel_d_n_r]

you might be able to do somthing with the file allocation
possibly automate a directory according to the file name then simply keep all your sheets saved from that file in the directory.
If your sheet names stayed the same it would simply update the files every time you ran the sub.
DNR stands for Development of Natural Resources,,, so im not so great at screen names.


cheers

[andysuth]

I tried that and it crashed pretty badly, no matter what I did, perhaps because I'm running it from the workbook, not my personnel vba folder, (forgot to mention, sorry!)

I've also been tinkering with this SR:

[vba]
Sub textbasckup(verint)
Dim VFF As Long, vParam As String, vBody As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Range("a2:b13").Select


vParam = pathstring & verint & filestring & ".txt"
vBody = ActiveCell.Value


VFF = FreeFile
Open vParam For Output As #VFF
Print #VFF, vBody
Close #VFF

End Sub
[/vba]

with varying levels of success.

It did actually save almost what I wanted it to save at one point, but the next few times it bugged out and just saved the word "True" - I might have changed something to do with the path statement in between.

-AS