Open File or Switch Between Windows if File is Open

[Ricky Pang]

Hello Experts,
My macro starts off with the following commands to open a file based on
the combined input of 2 cells called "filename" (ie. C:\My
Documents\)and "filelocation" (ie. Data.xls). How do I alter this so
that if the file is already opened, then switch to that opened workbook
(as I need to copy and paste the data back to the current worksheet)?

Dim fName As String
Dim Wkbk As Workbook
Dim curWks As Worksheet

Set curWks = ActiveSheet
fName = Worksheets("Sheet1").Range("filename").Value
Set Wkbk = Workbooks.Open(Range("filelocation") & fName,
UpdateLinks:=0)

Thanks in advance,
Ricky

 

[Arvi Laanemets]

Hi

Dim fName As String
Dim Wkbk As Workbook
Dim curWks As Worksheet

Set curWks = ActiveSheet
fName = [filename]
varFound=False
For Each w In Workbooks
If w.Name = fName Then
varFound=True
Exit For
End If
Next w
If varFound Then
....
Else
....
End If
....

 

[Kaak]

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 Su

 

[Ricky Pang]

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

 

[Ricky Pang]

Hi Arvi,
Thanks for coming to my aid. When the file is already opened, I got a
prompt notifying me that the file is already opened. If I choose to
open it, I would end up opening the same file twice and running into an
error. How do I switch to that opened file and start my copy and paste
without answering the prompt of opening the same file again?

I appreciate your help.

Thanks again,
Ricky

 

[Dave Peterson]

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.)

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

 

[Ricky Pang]

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