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?

 

[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