andysuth
11-28-2007, 06:21 AM
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.
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
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.
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