Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP=VB 'File Already Open' ERROR (run-time 55)
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 SCO" & 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP=VB 'File Already Open' ERROR (run-time 55)
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 -- __________________________________ HTH Bob "tommo_blade" wrote in message ... 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 SCO" & 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP=VB 'File Already Open' ERROR (run-time 55)
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL:Can't open any file without error saying it's ALREADY open??? | Excel Worksheet Functions | |||
When i open excel file it takes time to open | Excel Discussion (Misc queries) | |||
Can Excel open a new application every time you open a file? | Setting up and Configuration of Excel | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel | |||
Desktop shortcut to a named Excel file - every time I open it adds a "1" to the file name - how to disable? | Setting up and Configuration of Excel |