HELP=>VB 'File Already Open' ERROR (run-time 55)

[tommo_blade]

Hello,
I am getting a 'File already open' (run-time 55) error message
when trying to execute a macro from within my Excel workbook, the
problem only occurrs after I have run an earlier macro which imports
Excel worksheets from other workbooks.

The macro that fails is trying to open a file handle for writing to an
external file, it then loops around all the worksheets and retrieves
some data before writing to the file, I am using 'FreeFile' to assign
the file handles so I cannot see what the problem is - as I say the
macro works OK before I import these extra worksheets via a different
macro.

here is the code from the failing macro:

Sub Generate_league()
Dim myTots As Integer
Dim myTeam As String
Dim myName As String

For x = 1 To Worksheets.Count
'MsgBox "TEAMSHEET:" & Worksheets(x).Name
If Left(Worksheets(x).Name, 2) = "FF" Then
'MsgBox "TOTAL SCORE:" & Worksheets(x).Cells(23, 2)
myTots = Worksheets(x).Cells(23, 2)
myTeam = Worksheets(x).Cells(3, 2)
myName = Worksheets(x).Cells(1, 2)
myWk1 = Worksheets(x).Cells(21, 6)
myWk2 = Worksheets(x).Cells(21, 8)
myWk3 = Worksheets(x).Cells(21, 10)
myWk4 = Worksheets(x).Cells(21, 12)
myWk5 = Worksheets(x).Cells(21, 14)
myWk6 = Worksheets(x).Cells(21, 16)
mydWk1 = Worksheets(x).Cells(21, 18)
mydWk2 = Worksheets(x).Cells(21, 20)
mydWk3 = Worksheets(x).Cells(21, 22)
mydWk4 = Worksheets(x).Cells(21, 24)
mydWk5 = Worksheets(x).Cells(21, 26)
mydWk6 = Worksheets(x).Cells(21, 28)

MyFiles = "F:\My Documents\Fantasy Football\Premier League\League
Tables\leagueTable_Control.txt"

file = FreeFile()

Open MyFiles For Output As file

Print #file, myName; "#"; myTeam; "#"; myTots; "#"; myWk1; "#"; myWk2;
"#"; myWk3; "#"; myWk4; "#"; myWk5; "#"; myWk6; "#"; mydWk1; "#";
mydWk2; "#"; mydWk3; "#"; mydWk4; "#"; mydWk5; "#"; mydWk6

End If
Next x
Close #file

End Sub




thanks in advance, Mark.

 

[Bob Phillips]

Either close the workbook in the earlier routine, or check before opening it

On Error Resume Next
Set TextFile = Workbooks("leagueTable_Control.txt")
On Error Goto 0
If TextFile Is Nothing Then

MyFiles = "F:\My Documents\Fantasy Football\Premier League\League
Tables\leagueTable_Control.txt"
file = FreeFile()
Open MyFiles For Output As file
End If

 

[tommo_blade]

I am not sure that this will work, i.e. in the earlier macro I have
added the line:

ActiveWorkbook.Close True

the problem is, that closes the workbook (not what I want), even when
I re-open the workbook I still get the error when executing the 2nd
macro that tries to write to the file.

The workbooks from which I imported the worksheets with the 1st macro
were all closed anyway, all that was left open was my master workbook
containing a bunch of worksheets that I had just imported, surely
closing down the workbook and re-opening would have closed any
previous file handles, it is as if these imported worksheets are the
problem.

stumped ?