Text file from Excel - MESSY! HELP!

[andysuth]

Hi, Can someone help me tidy this up please?

I have a multi-worksheet Excel file, which I want people to fill in data on, and then save the individual worksheet (or section of, e.g. A1 to E15) as a Text File.

I currently have a routine, which works, but ends up renaming the active file (which I don't want) and then I have to rename it and the worksheet to its original name so it will work the next time.

Basically it currently puts too much HDD activity in to be efficient, so I need help to streamline my code whilst achieving the same effect:

a Text file containing A1 to E15 of the active worksheet without renaming or resaving the active workbook.

Any ideas how to help Please?

There's another bit in this code which saves a new line to an existing "historical" file, which keeps records of what has been done and when, this seems to work fine, but is listed here so the function is a whole.

Thanks,

-Andy.

[vba]

Sub textmaker(code As String, verint As Integer)
Dim vFF As Long, vFile As String, xls As String, txt As String

' copies the hidden sheet to new file name.
xls = pathstring & "aframe.xls"
txt = pathstring & verint & filestring & ".txt"

Sheets(code).Select
Range("B3").Select
ActiveCell.Value = verint & code
Sheets(code).SaveAs Filename:=txt, FileFormat:=xlTextPrinter, CreateBackup:=False

ActiveSheet.Name = code
If Dir(xls) <> "" Then
Kill (xls)
End If
ActiveWorkbook.SaveAs Filename:=xls, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
'MsgBox ("Appending file to keep historical record")
vFile = pathstring & "historical.txt"
Range("B4").Select
vFF = FreeFile
Open vFile For Append As #vFF
Print #vFF, verint & "," & Now() & "," & code & "," & ActiveCell.Value
Close #vFF
'
'
End Sub
[/vba]

[andysuth]

"Pathstring" & "filestring" are both global variables, and that part runs fine.

[mdmackillop]

[vba]Sub SaveText()
Range("A1:E15").Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Move
ActiveWorkbook.SaveAs Filename:="C:\AAA\MyNewFile3.txt", FileFormat:=xlText _
, CreateBackup:=False
ActiveWorkbook.Close False
End Sub

[/vba]

[andysuth]

Thanks for that, I've integrated it into my routine and it works well.

I've added a line for the autoadjustment of the columns in the spreadsheet and use the textprinter to save the file in a format.

Has got me thinking about using the transpose (currently data is in columns) and then appending all my excel file parameters to the "Historical.TXT" file.

I'm using the main text file to record parameters to change in SolidEdge2D and ProEngineer Wildfire 3.0, but want to keep a record of the historical data (hence the "Historical.txt", but this only recordsd the date, time, document number, type of drawing and customer at the moment).

[vba]
Sub textmaker(code As String, verint As Integer)
Dim vFF As Long, vFile As String, txtfl As String
' copies the hidden sheet to new file name.
txtfl = pathstring & verint & filestring & ".txt"
Sheets(code).Select
Range("B3").Select
ActiveCell.Value = verint & code
Range("A1:E15").Copy
Sheets.Add
ActiveSheet.Paste
Columns("A:E").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveSheet.Move
ActiveWorkbook.SaveAs Filename:=txtfl, FileFormat:=xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Close False
vFile = pathstring & "historical.txt"
Range("B4").Select
vFF = FreeFile
Open vFile For Append As #vFF
Print #vFF, verint & "," & Now() & "," & code & "," & ActiveCell.Value
Close #vFF
End Sub
[/vba]