RockMechMark
09-28-2012, 01:48 PM
I am having a challenge finding the solution here. I cannot seem to open an ASCII file for the purpose of writing contents of some cells from several worksheets within one spreadsheet file.
In the following code (with irrelevant lines not shown), I have several sheets. For each, I want to read the contents of cells and write them line by line to an ASCII file. However, if the file already exists, I do not want to create that file, but instead, move on to the next sheet. In my test case, I start with the second sheet, but that file already exists. No file yet exists from data in the third sheet.
The problem comes in the Set f = fs.OpenTextFile(Fname) line. The error is that the object does not support the method.
I wonder if the statement Set fs = CreatObject("Scripting.FileSystemObject") is a problem, being used for multiple ASCII files? What is wrong here?
Here is the code, minus the irrelevant lines:
Sub InsertCoarseBorders()
'
' InsertCoarseBoarders Macro
'
'
Dim Fname As String, k As Integer, StepNum As String, kk As Integer
Dim FnameExists As Integer, MyStr As String, MyStr1 As String
Dim fs As Object, f As Object
Set fs = CreateObject("Scripting.FileSystemObject")
k = 2
Do While k < 4
kk = k + 134
If kk < 10 Then
StepNum = "00" & kk
Else
If kk < 100 Then
StepNum = "0" & kk
End If
If kk >= 100 Then
StepNum = kk
End If
End If
Fname = "D:\xxxxxx\yyyyyy\Coarse grids\MS_EC_" & StepNum
Fname = Fname & "Coarse.txt"
' MsgBox (Fname)
FnameExists = fs.FileExists(Fname)
If FnameExists = False Then
…
Set f = fs.CreatTextFile(Fname)
i = 1
j = 1
imax = 1370
jmax = 640
Do While i < imax
MyStr = ""
Do While j < jmax
MyStr1 = ActiveCell.Value
MyStr = MyStr & MyStr1
j = j + 1
Loop
f.WriteLine (MyStr)
i = i + 1
Loop
f.Close
End If
k = k + 1
Loop
End Sub
In the following code (with irrelevant lines not shown), I have several sheets. For each, I want to read the contents of cells and write them line by line to an ASCII file. However, if the file already exists, I do not want to create that file, but instead, move on to the next sheet. In my test case, I start with the second sheet, but that file already exists. No file yet exists from data in the third sheet.
The problem comes in the Set f = fs.OpenTextFile(Fname) line. The error is that the object does not support the method.
I wonder if the statement Set fs = CreatObject("Scripting.FileSystemObject") is a problem, being used for multiple ASCII files? What is wrong here?
Here is the code, minus the irrelevant lines:
Sub InsertCoarseBorders()
'
' InsertCoarseBoarders Macro
'
'
Dim Fname As String, k As Integer, StepNum As String, kk As Integer
Dim FnameExists As Integer, MyStr As String, MyStr1 As String
Dim fs As Object, f As Object
Set fs = CreateObject("Scripting.FileSystemObject")
k = 2
Do While k < 4
kk = k + 134
If kk < 10 Then
StepNum = "00" & kk
Else
If kk < 100 Then
StepNum = "0" & kk
End If
If kk >= 100 Then
StepNum = kk
End If
End If
Fname = "D:\xxxxxx\yyyyyy\Coarse grids\MS_EC_" & StepNum
Fname = Fname & "Coarse.txt"
' MsgBox (Fname)
FnameExists = fs.FileExists(Fname)
If FnameExists = False Then
…
Set f = fs.CreatTextFile(Fname)
i = 1
j = 1
imax = 1370
jmax = 640
Do While i < imax
MyStr = ""
Do While j < jmax
MyStr1 = ActiveCell.Value
MyStr = MyStr & MyStr1
j = j + 1
Loop
f.WriteLine (MyStr)
i = i + 1
Loop
f.Close
End If
k = k + 1
Loop
End Sub