Open File or Switch Between Windows if File is Open
 

Hi Kaak,
I've tried your code (bottom of this message) and I got a "Compile
error: Else Without If" error message with the highlighter stopping at
[Else: MsgBox FileName & " is not open"].

If the file is already opened, how do I activate (go to) it, copy and
paste to the current worksheet? If the file isn't already opened, then
my original code would do the trick. I need a workaround for when the
target file is already opened. Otherwise, I would have to close it
first before triggering my original macro.

Thanks in advance again,
Ricky


Sub CheckForFile()

Dim FileName As String
Dim x As Workbook

FileName = "budget.XLS"

On Error Resume Next
Set x = Workbooks(FileName)

If Err = 0 Then MsgBox FileName & " is open"
Else: MsgBox FileName & " is not open"
End If

On Error GoTo 0

End Sub




*** Sent via Developersdex http://www.developersdex.com ***

Open File or Switch Between Windows if File is Open
 

You changed Kaak's code.

Option Explicit

Sub CheckForFile()

Dim FileName As String
Dim x As Workbook

FileName = "BUDGET.XLS"

On Error Resume Next
Set x = Workbooks(FileName)

If Err = 0 Then
MsgBox FileName & " is open."
Else
MsgBox FileName & " is not open."
End If

On Error GoTo 0

End Sub

(I added indents and changed quote marks, and it worked as-is for me.)



Ricky Pang wrote:

Hi Kaak,
I've tried your code (bottom of this message) and I got a "Compile
error: Else Without If" error message with the highlighter stopping at
[Else: MsgBox FileName & " is not open"].

If the file is already opened, how do I activate (go to) it, copy and
paste to the current worksheet? If the file isn't already opened, then
my original code would do the trick. I need a workaround for when the
target file is already opened. Otherwise, I would have to close it
first before triggering my original macro.

Thanks in advance again,
Ricky

Sub CheckForFile()

Dim FileName As String
Dim x As Workbook

FileName = "budget.XLS"

On Error Resume Next
Set x = Workbooks(FileName)

If Err = 0 Then MsgBox FileName & " is open"
Else: MsgBox FileName & " is not open"
End If

On Error GoTo 0

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Open File or Switch Between Windows if File is Open
 

Hi Dave,
You've come to my aid yet again. Yes, Kaak's code works great. I had
the If...Then... part on the same line which caused the macro to error.
Now that I understand this a bit more, I've tested Avri's code also and
with much success.

Thank-you Dave, Kaak and Avri! All worked very nicely.

Ricky


*** Sent via Developersdex http://www.developersdex.com ***