MrExcel.com - Your One Stop for Excel Tips & Solutions

save worksheets as text

Posted by carrie on December 19, 2000 6:39 AM

Hello everyone,

I'm looking for a macro to save all the pages of my workbook as individual text files. This macro will get run daily and each day will overwrite the previous days' file. The files will be in existence before the first run.

This will be the last part of a big macro that takes data from a database, deletes the irrelevant data, and then seperates it out into worksheets based on category (building name, in this case).

Any help anyone could provide would be appreciated. I haven't found anything in the archives that talks about saving files as text.

Thanks,
Carrie

Posted by thomas venn on December 20, 2000 1:46 PM


Hi,
try this... ASSUME.. cell a1 and cell b1 are both blank. you must start the macro from the left most section of all the sheets in the workbook, and the worksheet is the name you want as your file.


p.s. if there is a better way to do this, please feel free to update the code and post it here.

Cheers,

Thomas


Sub Macro3()
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Application.Goto Reference:="R1C1"
Application.Goto Reference:="R1C2"
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
ActiveCell.FormulaR1C1 = "=FIND(""]"",RC[-1])"
ActiveCell.FormulaR1C1 = "=LEN(RC[-1]-FIND(""]"",RC[-1]))"
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])-FIND(""]"",RC[-1])"
ActiveCell.FormulaR1C1 = "=RIGHT(LEN(RC[-1])-FIND(""]"",RC[-1]),RC[-1])"
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-FIND(""]"",RC[-1]))"
ActiveCell.FormulaR1C1 = _
"=""C:\""&""""&RIGHT(RC[-1],LEN(RC[-1])-FIND(""]"",RC[-1]))&""""&"".txt"""
Calculate
Application.Goto Reference:="R1C1"
Selection.Copy
Application.Goto Reference:="R1C1"
ActiveCell.Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="R1C1"
'End Sub

'File save with macro, pick the file name from named cell on spreadsheet

'Sub filenamer()
Application.DisplayAlerts = False
kname = Range("b1").Formula
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=kname, FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = False
'End Sub
'1629.html

'Sub Macro5()
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
ActiveSheet.Next.Select
Application.Run "Macro3"
End Sub