Open File or Switch Between Windows if File is Open

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

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

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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Ricky Pang" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***

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 ***

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

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

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

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 ***