Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheet as a text file
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 "Tom Hutchins" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem encountered when saving worksheet as a text file | Excel Worksheet Functions | |||
Saving a worksheet as . prn text file - record length | Excel Discussion (Misc queries) | |||
Saving worksheet in new file with date AND cell value as file name | Excel Discussion (Misc queries) | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
saving an excel file as an ASCII text file without delimiters | Excel Programming |