execl to check if another excel file is open...

[ohboy!]

I need my one excel workbook to check if another one is open before it
copies across to it and if it is open to exit the copy sub and display
please try again later....can any help?



TIA

[Jim Thomlinson]

This code should be close...

Dim wbk As Workbook

On Error Resume Next
Set wbk = Workbooks("MyBook.xls")
On Error GoTo 0

If wbk Is Nothing Then MsgBox "Can't find it"

HTH

"ohboy!" wrote:

> I need my one excel workbook to check if another one is open before it
> copies across to it and if it is open to exit the copy sub and display
> please try again later....can any help?
>
>
>
> TIA
>
>
>

[ohboy!]

"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
news:04A519CA-F133-4831-96B0-B764141F1650@microsoft.com...
> This code should be close...
>
> Dim wbk As Workbook
>
> On Error Resume Next
> Set wbk = Workbooks("MyBook.xls")
> On Error GoTo 0
>
> If wbk Is Nothing Then MsgBox "Can't find it"
>
> HTH
>
> "ohboy!" wrote:
>
> > I need my one excel workbook to check if another one is open before it
> > copies across to it and if it is open to exit the copy sub and display
> > please try again later....can any help?
> >
> >
> >
> > TIA
> >
> >
Hi there - thanks for tip - did the following in the end:

Option Explicit
Option Compare Text

Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True

End Function


Sub openquery()

If MsgBox("This facility is only for transfering information into " _
+ "the Central Register repository database. " _
+ "Are you sure you want to continue?", vbQuestion + vbYesNo) = vbNo
Then
Exit Sub
End If

Dim wb As Workbook, strName As String, strPath As String
strName = "Book3.xls"
strPath = "C:\

If IsWbOpen("Book3.xls") Then
MsgBox "The Repository is being used try again in a moment."
Exit Sub
Else: Worksheets("Register").Select
ActiveSheet.Unprotect
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim idx As Long
Set bk1 = ActiveWorkbook
Set bk2 = Workbooks.Open("C:\Book3.xls")
Set sh1 = bk1.Worksheets("Register")

On Error Resume Next
Set sh2 = bk2.Worksheets("WPP")
On Error GoTo 0
If Not sh2 Is Nothing Then
idx = sh2.Index
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
If idx > 1 Then
sh1.Copy after:=bk2.Sheets(idx - 1)
Else
sh1.Copy before:=bk2.Sheets(2)
End If
Else
sh1.Copy after:=bk2.Worksheets(bk2.Worksheets.Count)
End If
ActiveSheet.Name = "WPP"
ActiveWorkbook.Save
ActiveWorkbook.Close

End If


End Sub

[Yong Peng]

Hi Jim,
If the file is open in another instance,your code won't work.
Can you tell me how to deal with it?
Thanks
Yong

"Jim Thomlinson" wrote:

> This code should be close...
>
> Dim wbk As Workbook
>
> On Error Resume Next
> Set wbk = Workbooks("MyBook.xls")
> On Error GoTo 0
>
> If wbk Is Nothing Then MsgBox "Can't find it"
>
> HTH
>
> "ohboy!" wrote:
>
> > I need my one excel workbook to check if another one is open before it
> > copies across to it and if it is open to exit the copy sub and display
> > please try again later....can any help?
> >
> >
> >
> > TIA
> >
> >
> >

[Ivan F Moala]

Have a look here for this ..........

http://www.xcelfiles.com/IsFileOpen.html