RWayneH
asked on
Is this File open?
Why does the following code think that the file is always open? I am having issues getting the Else part of the If to fire. Also how do I get the file closed and not save changes? I just want the file closed so I can use it. It is a .XML file.
Dim i As Integer
Dim bIsOpened
For i = 1 To Application.Workbooks.Count
bIsOpened = bIsOpened Or (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd")
Next i
If bIsOpened Then
MsgBox "Target save file is open, Closing file so process can use it.", vbOKOnly + vbInformation, "Cannot have: C:\_SAP\EXTRACTS\SALESORD\SALESORDERDOWNLOAD.xml open and run this process. Close it and try again."
Application.DisplayAlerts = False
Windows("SALESORDERDOWNLOAD.XML").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
ASKER
This is not working either, bIsOpened is still = True when I run this over, (after closing the file) it is still True. How do I reset the value of bIsOpened after it checks? I started with it opened. The code closed the workbook. Why does it still think that it open?
Can you post the remaining code of the routine?
ASKER
Dim i As Integer
Dim bIsOpened
For i = 1 To Application.Workbooks.Count
If (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd") Then
bIsOpened = True
Exit For
End If
Next i
If bIsOpened Then
MsgBox "Target save file is open, Closing file so process can use it.", vbOKOnly + vbInformation, "Cannot have: C:\_SAP\EXTRACTS\SALESORD\SALESORDERDOWNLOAD.xml open and run this process. Close it and try again."
Application.DisplayAlerts = False
Windows("SALESORDERDOWNLOAD.XML").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
GoTo 100 'Removed Exit Sub
Else
GoTo 100
End If
' For i = 1 To Application.Workbooks.Count
' bIsOpened = bIsOpened Or (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
' And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd")
' Next i
If bIsOpened Then
MsgBox "Target save file is open, Closing file so process can use it.", vbOKOnly + vbInformation, "Cannot have: C:\_SAP\EXTRACTS\SALESORD\SALESORDERDOWNLOAD.xml open and run this process. Close it and try again."
Application.DisplayAlerts = False
Windows("SALESORDERDOWNLOAD.XML").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
GoTo 100 'Removed Exit Sub
Else
GoTo 100
End If
ASKER
Lets try that again.
Dim i As Integer
Dim bIsOpened
For i = 1 To Application.Workbooks.Count
If (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd") Then
bIsOpened = True
Exit For
End If
Next i
' For i = 1 To Application.Workbooks.Count
' bIsOpened = bIsOpened Or (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
' And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd")
' Next i
If bIsOpened Then
MsgBox "Target save file is open, Closing file so process can use it.", vbOKOnly + vbInformation, "Cannot have: C:\_SAP\EXTRACTS\SALESORD\SALESORDERDOWNLOAD.xml open and run this process. Close it and try again."
Application.DisplayAlerts = False
Windows("SALESORDERDOWNLOAD.XML").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
GoTo 100 'Removed Exit Sub
Else
GoTo 100
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Setting bIsOpened to False after closing the workbook worked. Thanks.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for RWayneH's comment #a40409868
for the following reason:
Thanks for the help.
Accepted answer: 0 points for RWayneH's comment #a40409868
for the following reason:
Thanks for the help.
ASKER
I think I closed this question wrg. Thanks for the help
Open in new window