Saving worksheet as a text file

[drinese18]

I am trying to create a macro that will save a worksheet as a text file when
I call the macro. So far I got it to save the worksheet as a text file, but I
would like it to save as different files, instead of overwriting one file
over and over. For instance when I edit the file everyday and I save it, I
want it to be saved as a certain name, maybe todays date, the next day I
would like it to save as the date for that day. Does anyone know how to do
this? I recorded a macro below, tell me what you think:

Sub Save_as()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("ExportSheet").Select

Dim myPath As String
Dim myFile As String

myPath = ActiveWorkbook.Path & "\"
myFile = "import_file"
myFile = myFile & ".txt"

ActiveWorkbook.SaveAs Filename:=myPath & myFile, FileFormat:=xlText,
CreateBackup:=False

Sheets("Sheet1").Select
Sheets("import_file").Name = "ExportSheet"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Tom Hutchins]

Maybe replace
Dim myPath As String
Dim myFile As String
myPath = ActiveWorkbook.Path & "\"
myFile = "import_file"
myFile = myFile & ".txt"
ActiveWorkbook.SaveAs Filename:=myPath & myFile, FileFormat:=xlText,
CreateBackup:=False

with
Dim myPath As String
Dim myFile As String
Dim NewDate As String
myPath = ActiveWorkbook.Path & "\"
myFile = "import_file"
myFile = myFile & ".txt"
NewDate = "_" & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs Filename:=myPath & myFile & NewDate, _
FileFormat:=xlText, CreateBackup:=False

This will add the current date to the SaveAs filename.
Hope this helps,

Hutch

 

[drinese18]

Ok I tried it and at first it worked but then it started talking about the
read-only file that I have cannot be accessed, in which I don't have a
read-only file, I'm guessing maybe it's trying to access a file that's not
there, but I don't get it, it's not suppose to do that, I wrote some extra
code below, I basically want it to save in a certain path and also as a text
file within that path, my code can be seen below:

Sub OpenWorkNewWorkBook()

Sheets("Import").Select
Range("A1:C2").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("C2").Select
Selection.NumberFormat = "0.00000000000000"
Range("A1").Select
End Sub


Sub Save_as()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Sheet1").Select

Dim myPath As String
Dim myFile As String
Dim NewDate As String
myPath = ActiveWorkbook.Path & "\"
myFile = "export_file"
myFile = myFile & ".txt"
NewDate = "_" & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs Filename:=myPath & myFile & NewDate, _
FileFormat:=xlText, CreateBackup:=False

Sheets("Sheet1").Select
Sheets("import_file").Name = "ExportSheet"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Tom Hutchins]

I didn't notice before that we are appending ".txt" before appending the
date. Try replacing
myFile = "export_file"
myFile = myFile & ".txt"
NewDate = "_" & Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs Filename:=myPath & myFile & NewDate, _
FileFormat:=xlText, CreateBackup:=False

with
myFile = "export_file"
NewDate = "_" & Format(Now(), "mmddyy")
myFile = myFile & NewDate & ".txt"
ActiveWorkbook.SaveAs Filename:=myPath & myFile, _
FileFormat:=xlText, CreateBackup:=False

So, instead of an odd filename like "export_file.txt_040308", it should be
like "export_file_040308.txt". It will save the file using the odd name, but
maybe that caused the problems you had later.

Hutch

 

[drinese18]

It saves as a text file now, but it's still coming with the same Error:

Cannot Access Read-Only document 'export_file_040308.txt'

what exactly does that mean and is it possible to direct where I want it to
be saved?

 

[Tom Hutchins]

At what point do you get the error? When I run the whole (modified) Save_as
subroutine as posted, I get an error on the Sheets("Sheet1").Select command
that follows the ActiveWorkbook.SaveAs command. That's because when you save
a workbook as a text file, only the active sheet (Sheet1 in this case) is
saved to the text file, and the worksheet in Excel is renamed to the name of
the text file. So, the Sheets("Sheet1").Select command fails because Sheet1
has been renamed. The code runs fine for me to that point - Sheet1 is
exported to a text file with the current date in the filename.

Hutch

 

[drinese18]

I saw that error and corrected it I mean basically all you have to do is make
sure the sheet i'm getting the data from is going to always be the same name,
but apart from that I just get an error saying that it "Cannot access
read-only document 'LPE NetTR wFEES Import_04-04-08.txt", but I mean I know I
don't have a file by that name, but regardless it never acted like this
before, I tried skipping back to what I had before and it just does that same
thing,

Help please

 

[Tom Hutchins]

If you send me a copy of your workbook, I will try to diagnose & correct the
problem. Remove or alter any confidential information first. My email address
is mistertom<remove this>@ameritech<remove this too>.net

Hutch

 

[drinese18]

Actually, I was able to fix the problem, had to do with when it was switching
the sheets over, I don't necessarily need that second to last line of code,
so I just took it out, now it works fine, the only problem is that, when it's
saving to the path I specify, it saves correctly to the path I want it to
save, but if I try it on someone else's computer it saves in a different
place, kind of wierd