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 |
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 |