Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't save cell values to text file
Const SaveTheseCells As String = "M4:O4;M6:O6;M8:O8;M10:O10;M12:O12"
Sub SaveToFile() Dim MySheet As Worksheet For Each MySheet In ActiveWorkbook.Worksheets Select Case MySheet.CodeName Case "SpecialSheet": Dim SheetToFile As Worksheet Set SheetToFile = MySheet End Select Next MySheet Set MySheet = Nothing Dim FileName As String FileName = "C:\test.txt" FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, "start" SheetToFile.Range(SaveTheseCells).Select Dim cell As Range 'This does not save the values of M4, N4, O4, etc. 'What is wrong with this? For Each cell in Selection.Cells Print #FileNum, cell.Text Next cell Print #FileNum, "end" Close #FileNum Set SheetToFile = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't save cell values to text file
I think you have a couple of problems.
SaveTheseCells needs to be delimited by commas. And if the SpecialSheet isn't the activesheet, then you'll have trouble selecting a range on that sheet. But this worked ok for me: Option Explicit Const SaveTheseCells As String = "M4:O4,M6:O6,M8:O8,M10:O10,M12:O12" Sub SaveToFile() Dim MySheet As Worksheet Dim FileNum As Long Dim myRng As Range For Each MySheet In ActiveWorkbook.Worksheets Select Case MySheet.CodeName Case "SpecialSheet": Dim SheetToFile As Worksheet Set SheetToFile = MySheet Exit For End Select Next MySheet Set MySheet = Nothing Dim FileName As String FileName = "C:\test.txt" FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, "start" Set myRng = SheetToFile.Range(SaveTheseCells) Dim cell As Range 'This does not save the values of M4, N4, O4, etc. 'What is wrong with this? For Each cell In myRng.Cells Print #FileNum, cell.Text Next cell Print #FileNum, "end" Close #FileNum Set SheetToFile = Nothing End Sub ====== Just a curiosity question... I try to put all my Dim's at the top of the code. I find it easier to locate them later if I need to--and it makes it easier not to duplicate the declaration. Is there a reason you sprinkle your Dim's throughout the code? Mika wrote: Const SaveTheseCells As String = "M4:O4;M6:O6;M8:O8;M10:O10;M12:O12" Sub SaveToFile() Dim MySheet As Worksheet For Each MySheet In ActiveWorkbook.Worksheets Select Case MySheet.CodeName Case "SpecialSheet": Dim SheetToFile As Worksheet Set SheetToFile = MySheet End Select Next MySheet Set MySheet = Nothing Dim FileName As String FileName = "C:\test.txt" FileNum = FreeFile Open FileName For Output As #FileNum Print #FileNum, "start" SheetToFile.Range(SaveTheseCells).Select Dim cell As Range 'This does not save the values of M4, N4, O4, etc. 'What is wrong with this? For Each cell in Selection.Cells Print #FileNum, cell.Text Next cell Print #FileNum, "end" Close #FileNum Set SheetToFile = Nothing End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't save cell values to text file
"Dave Peterson" wrote in message
... I think you have a couple of problems. But this worked ok for me: Thanks, Dave! Mika |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAVE & SEND A FILE BASED ON TEXT IN A CELL VIA MACRO | New Users to Excel | |||
Save file as text from cell | Excel Discussion (Misc queries) | |||
save into file while user is entering values in cell | Excel Programming | |||
Save excel file with filename = cell text | Excel Programming | |||
Save As - Multiple Sheets fails to save as text file | Excel Programming |