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
|