Close an Excel file if is found it open.

[maperalia]

I have a file to archive data (see below). However, when the file that I
suppose to archive the data (color.xls) is open my program crashes. I would
like to know if the program can recognize the following:
If the color.xls file is open then close it. So the program can be run
without problem.

Thanks in advance.
Maperalia.




'********************START PROGRAM************************
'CREATE DATABASE
Dim sr As Range
Dim dr As Range
Dim dwb As Workbook
Dim Lr As Long
Dim hl As String

'Where:
'Lr = Last Row
'dr = destination Range
'dwb = destination WorkBook
'sr = source range
'***************************************************
'***************************************************
'OPEN THE FILE TO ARCHIVE INFORMATION
Application.ScreenUpdating = False

If bIsBookOpen("color.xls") Then
Set dwb = Workbooks("color.xls")
Else

'***************************************************
'REMOVE READ ONLY
SetAttr "C:\Test\Pants\color.xls", vbNormal
'***************************************************
Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
End If
'***************************************************************
'WRITE THE DATABASE
UserForm1.Repaint
Application.ScreenUpdating = False
UserForm1.Repaint
Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
Sheets("Sheet1").Select


Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
sr.Copy
dr.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False

Sheets("Sheet1").Select
Range("E2").Select

'**************************************************************
dwb.Close True
Application.ScreenUpdating = False

'***********************************************************
'SAVE AS AS READ ONLY
SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
'**********************************************************

'******************END PROGRAM*****************************

[Zack Barresse]

Hi ther eMaperalia,

Add this function to a standard module ...

Function bIsBookOpen(wbName as String) as boolean
On Error Resume Next
bIsBookOpen = Len(Workbooks(wbName).Name)
End Function

This will return you a True/False boolean if the book is open or not. Is
this what you want?


--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)



"maperalia" <[email protected]> wrote in message
news:[email protected]...
>I have a file to archive data (see below). However, when the file that I
> suppose to archive the data (color.xls) is open my program crashes. I
> would
> like to know if the program can recognize the following:
> If the color.xls file is open then close it. So the program can be run
> without problem.
>
> Thanks in advance.
> Maperalia.
>
>
>
>
> '********************START PROGRAM************************
> 'CREATE DATABASE
> Dim sr As Range
> Dim dr As Range
> Dim dwb As Workbook
> Dim Lr As Long
> Dim hl As String
>
> 'Where:
> 'Lr = Last Row
> 'dr = destination Range
> 'dwb = destination WorkBook
> 'sr = source range
> '***************************************************
> '***************************************************
> 'OPEN THE FILE TO ARCHIVE INFORMATION
> Application.ScreenUpdating = False
>
> If bIsBookOpen("color.xls") Then
> Set dwb = Workbooks("color.xls")
> Else
>
> '***************************************************
> 'REMOVE READ ONLY
> SetAttr "C:\Test\Pants\color.xls", vbNormal
> '***************************************************
> Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> End If
> '***************************************************************
> 'WRITE THE DATABASE
> UserForm1.Repaint
> Application.ScreenUpdating = False
> UserForm1.Repaint
> Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> Sheets("Sheet1").Select
>
>
> Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> sr.Copy
> dr.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
>
> Sheets("Sheet1").Select
> Range("E2").Select
>
> '**************************************************************
> dwb.Close True
> Application.ScreenUpdating = False
>
> '***********************************************************
> 'SAVE AS AS READ ONLY
> SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> '**********************************************************
>
> '******************END PROGRAM*****************************
>

[NickHK]

maperalia,
Would the WB be open in another instance of Excel ?
Open by someone else ?

NickHK

"maperalia" <[email protected]> wrote in message
news:[email protected]...
> I have a file to archive data (see below). However, when the file that I
> suppose to archive the data (color.xls) is open my program crashes. I
would
> like to know if the program can recognize the following:
> If the color.xls file is open then close it. So the program can be run
> without problem.
>
> Thanks in advance.
> Maperalia.
>
>
>
>
> '********************START PROGRAM************************
> 'CREATE DATABASE
> Dim sr As Range
> Dim dr As Range
> Dim dwb As Workbook
> Dim Lr As Long
> Dim hl As String
>
> 'Where:
> 'Lr = Last Row
> 'dr = destination Range
> 'dwb = destination WorkBook
> 'sr = source range
> '***************************************************
> '***************************************************
> 'OPEN THE FILE TO ARCHIVE INFORMATION
> Application.ScreenUpdating = False
>
> If bIsBookOpen("color.xls") Then
> Set dwb = Workbooks("color.xls")
> Else
>
> '***************************************************
> 'REMOVE READ ONLY
> SetAttr "C:\Test\Pants\color.xls", vbNormal
> '***************************************************
> Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> End If
> '***************************************************************
> 'WRITE THE DATABASE
> UserForm1.Repaint
> Application.ScreenUpdating = False
> UserForm1.Repaint
> Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> Sheets("Sheet1").Select
>
>
> Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> sr.Copy
> dr.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
>
> Sheets("Sheet1").Select
> Range("E2").Select
>
> '**************************************************************
> dwb.Close True
> Application.ScreenUpdating = False
>
> '***********************************************************
> 'SAVE AS AS READ ONLY
> SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> '**********************************************************
>
> '******************END PROGRAM*****************************
>

[AA2e72E]

The file can be open as follows:

1. By any Excel session on the same PC that checks its availability
2. By an Excel session on another PC.

For scenario 1: if file is open, this will close the session without saving
the workbook
Sub CheckFile()
On Error Resume Next
Set xl = GetObject(, "c:\aa.xls") 'fully qualified file name
If xl Is Nothing Then
Else
xl.ActiveWorkbook.Saved = True
xl.ActiveWorkbook.Close
End If
Set xl = Nothing
End Sub

For scenario 2: you can check whether the file is open, if yes, your session
cannot open it.

Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As
String, ByVal iReadWrite As Long) As Long
Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long)
As Long

Function IsFileOpen(ByVal FileName As String) As Boolean
FileHwnd = lOpen(FileName, &H10)
If FileHwnd = -1 Then
IsFileOpen = True
Else
lClose (FileHwnd)
End If
End Function

IsFileOpen returns TRUE if file is open, else it is FALSE


"maperalia" wrote:

> I have a file to archive data (see below). However, when the file that I
> suppose to archive the data (color.xls) is open my program crashes. I would
> like to know if the program can recognize the following:
> If the color.xls file is open then close it. So the program can be run
> without problem.
>
> Thanks in advance.
> Maperalia.
>
>
>
>
> '********************START PROGRAM************************
> 'CREATE DATABASE
> Dim sr As Range
> Dim dr As Range
> Dim dwb As Workbook
> Dim Lr As Long
> Dim hl As String
>
> 'Where:
> 'Lr = Last Row
> 'dr = destination Range
> 'dwb = destination WorkBook
> 'sr = source range
> '***************************************************
> '***************************************************
> 'OPEN THE FILE TO ARCHIVE INFORMATION
> Application.ScreenUpdating = False
>
> If bIsBookOpen("color.xls") Then
> Set dwb = Workbooks("color.xls")
> Else
>
> '***************************************************
> 'REMOVE READ ONLY
> SetAttr "C:\Test\Pants\color.xls", vbNormal
> '***************************************************
> Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> End If
> '***************************************************************
> 'WRITE THE DATABASE
> UserForm1.Repaint
> Application.ScreenUpdating = False
> UserForm1.Repaint
> Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> Sheets("Sheet1").Select
>
>
> Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> sr.Copy
> dr.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
>
> Sheets("Sheet1").Select
> Range("E2").Select
>
> '**************************************************************
> dwb.Close True
> Application.ScreenUpdating = False
>
> '***********************************************************
> 'SAVE AS AS READ ONLY
> SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> '**********************************************************
>
> '******************END PROGRAM*****************************
>

[maperalia]

Geltlemen;
Thanks for your quick response and advice.
I wonder if I can run the macro without open and close the files involve in
the program. Some of the file that I have to open and close have more than 50
MB that make it difficlut time consuming when is running.

Kind regards.
Maperalia

"AA2e72E" wrote:

> The file can be open as follows:
>
> 1. By any Excel session on the same PC that checks its availability
> 2. By an Excel session on another PC.
>
> For scenario 1: if file is open, this will close the session without saving
> the workbook
> Sub CheckFile()
> On Error Resume Next
> Set xl = GetObject(, "c:\aa.xls") 'fully qualified file name
> If xl Is Nothing Then
> Else
> xl.ActiveWorkbook.Saved = True
> xl.ActiveWorkbook.Close
> End If
> Set xl = Nothing
> End Sub
>
> For scenario 2: you can check whether the file is open, if yes, your session
> cannot open it.
>
> Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As
> String, ByVal iReadWrite As Long) As Long
> Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long)
> As Long
>
> Function IsFileOpen(ByVal FileName As String) As Boolean
> FileHwnd = lOpen(FileName, &H10)
> If FileHwnd = -1 Then
> IsFileOpen = True
> Else
> lClose (FileHwnd)
> End If
> End Function
>
> IsFileOpen returns TRUE if file is open, else it is FALSE
>
>
> "maperalia" wrote:
>
> > I have a file to archive data (see below). However, when the file that I
> > suppose to archive the data (color.xls) is open my program crashes. I would
> > like to know if the program can recognize the following:
> > If the color.xls file is open then close it. So the program can be run
> > without problem.
> >
> > Thanks in advance.
> > Maperalia.
> >
> >
> >
> >
> > '********************START PROGRAM************************
> > 'CREATE DATABASE
> > Dim sr As Range
> > Dim dr As Range
> > Dim dwb As Workbook
> > Dim Lr As Long
> > Dim hl As String
> >
> > 'Where:
> > 'Lr = Last Row
> > 'dr = destination Range
> > 'dwb = destination WorkBook
> > 'sr = source range
> > '***************************************************
> > '***************************************************
> > 'OPEN THE FILE TO ARCHIVE INFORMATION
> > Application.ScreenUpdating = False
> >
> > If bIsBookOpen("color.xls") Then
> > Set dwb = Workbooks("color.xls")
> > Else
> >
> > '***************************************************
> > 'REMOVE READ ONLY
> > SetAttr "C:\Test\Pants\color.xls", vbNormal
> > '***************************************************
> > Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> > End If
> > '***************************************************************
> > 'WRITE THE DATABASE
> > UserForm1.Repaint
> > Application.ScreenUpdating = False
> > UserForm1.Repaint
> > Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> > Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> > Sheets("Sheet1").Select
> >
> >
> > Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> > sr.Copy
> > dr.PasteSpecial xlPasteValues, , False, False
> > Application.CutCopyMode = False
> >
> > Sheets("Sheet1").Select
> > Range("E2").Select
> >
> > '**************************************************************
> > dwb.Close True
> > Application.ScreenUpdating = False
> >
> > '***********************************************************
> > 'SAVE AS AS READ ONLY
> > SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> > '**********************************************************
> >
> > '******************END PROGRAM*****************************
> >

[NickHK]

maperalia,
If you are going to use automation, then you have no choice; you need to
open/execute/close.
If this is really is a database, as your comments suggest, use a DB app.

NickHK

"maperalia" <[email protected]> wrote in message
news:[email protected]...
> Geltlemen;
> Thanks for your quick response and advice.
> I wonder if I can run the macro without open and close the files involve
in
> the program. Some of the file that I have to open and close have more than
50
> MB that make it difficlut time consuming when is running.
>
> Kind regards.
> Maperalia
>
> "AA2e72E" wrote:
>
> > The file can be open as follows:
> >
> > 1. By any Excel session on the same PC that checks its availability
> > 2. By an Excel session on another PC.
> >
> > For scenario 1: if file is open, this will close the session without
saving
> > the workbook
> > Sub CheckFile()
> > On Error Resume Next
> > Set xl = GetObject(, "c:\aa.xls") 'fully qualified file name
> > If xl Is Nothing Then
> > Else
> > xl.ActiveWorkbook.Saved = True
> > xl.ActiveWorkbook.Close
> > End If
> > Set xl = Nothing
> > End Sub
> >
> > For scenario 2: you can check whether the file is open, if yes, your
session
> > cannot open it.
> >
> > Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName
As
> > String, ByVal iReadWrite As Long) As Long
> > Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As
Long)
> > As Long
> >
> > Function IsFileOpen(ByVal FileName As String) As Boolean
> > FileHwnd = lOpen(FileName, &H10)
> > If FileHwnd = -1 Then
> > IsFileOpen = True
> > Else
> > lClose (FileHwnd)
> > End If
> > End Function
> >
> > IsFileOpen returns TRUE if file is open, else it is FALSE
> >
> >
> > "maperalia" wrote:
> >
> > > I have a file to archive data (see below). However, when the file that
I
> > > suppose to archive the data (color.xls) is open my program crashes. I
would
> > > like to know if the program can recognize the following:
> > > If the color.xls file is open then close it. So the program can be run
> > > without problem.
> > >
> > > Thanks in advance.
> > > Maperalia.
> > >
> > >
> > >
> > >
> > > '********************START PROGRAM************************
> > > 'CREATE DATABASE
> > > Dim sr As Range
> > > Dim dr As Range
> > > Dim dwb As Workbook
> > > Dim Lr As Long
> > > Dim hl As String
> > >
> > > 'Where:
> > > 'Lr = Last Row
> > > 'dr = destination Range
> > > 'dwb = destination WorkBook
> > > 'sr = source range
> > > '***************************************************
> > > '***************************************************
> > > 'OPEN THE FILE TO ARCHIVE INFORMATION
> > > Application.ScreenUpdating = False
> > >
> > > If bIsBookOpen("color.xls") Then
> > > Set dwb = Workbooks("color.xls")
> > > Else
> > >
> > > '***************************************************
> > > 'REMOVE READ ONLY
> > > SetAttr "C:\Test\Pants\color.xls", vbNormal
> > > '***************************************************
> > > Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> > > End If
> > > '***************************************************************
> > > 'WRITE THE DATABASE
> > > UserForm1.Repaint
> > > Application.ScreenUpdating = False
> > > UserForm1.Repaint
> > > Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> > > Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> > > Sheets("Sheet1").Select
> > >
> > >
> > > Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> > > sr.Copy
> > > dr.PasteSpecial xlPasteValues, , False, False
> > > Application.CutCopyMode = False
> > >
> > > Sheets("Sheet1").Select
> > > Range("E2").Select
> > >
> > > '**************************************************************
> > > dwb.Close True
> > > Application.ScreenUpdating = False
> > >
> > > '***********************************************************
> > > 'SAVE AS AS READ ONLY
> > > SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> > > '**********************************************************
> > >
> > > '******************END PROGRAM*****************************
> > >

[tony h]

My first thought is why does it crash? If you can resolve that without checking whether the other workbook is open then you have a non-issue.

A bit more information about the nature of the crash erro codes, the piece of code that is executing, the line on which the crash occurs.

regards

[Zack Barresse]

I also wonder about the rest of your code. You have not fully posted it
(doesn't appear so) and these lines bring questions to my mind ...

Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
What is LastRow? I'm assuming it's a function you've put together?

Sheets("Sheet1").Select
No real need to select, not sure why you have it here.

sr.Copy
dr.PasteSpecial xlPasteValues, , False, False
Instead of copy/pastevalues, why not just set the value via VBA?

Sheets("Sheet1").Select
Select, again, is not needed.

Range("E2").Select
Again..


Just some questions you may want to ask yourself about your code and it's
efficiency.

--
Regards,
Zack Barresse, aka firefytr (MVP: Excel)



"maperalia" <[email protected]> wrote in message
news:[email protected]...
>I have a file to archive data (see below). However, when the file that I
> suppose to archive the data (color.xls) is open my program crashes. I
> would
> like to know if the program can recognize the following:
> If the color.xls file is open then close it. So the program can be run
> without problem.
>
> Thanks in advance.
> Maperalia.
>
>
>
>
> '********************START PROGRAM************************
> 'CREATE DATABASE
> Dim sr As Range
> Dim dr As Range
> Dim dwb As Workbook
> Dim Lr As Long
> Dim hl As String
>
> 'Where:
> 'Lr = Last Row
> 'dr = destination Range
> 'dwb = destination WorkBook
> 'sr = source range
> '***************************************************
> '***************************************************
> 'OPEN THE FILE TO ARCHIVE INFORMATION
> Application.ScreenUpdating = False
>
> If bIsBookOpen("color.xls") Then
> Set dwb = Workbooks("color.xls")
> Else
>
> '***************************************************
> 'REMOVE READ ONLY
> SetAttr "C:\Test\Pants\color.xls", vbNormal
> '***************************************************
> Set dwb = Workbooks.Open("C:\Test\Pants\color.xls ")
> End If
> '***************************************************************
> 'WRITE THE DATABASE
> UserForm1.Repaint
> Application.ScreenUpdating = False
> UserForm1.Repaint
> Lr = LastRow(dwb.Worksheets("Sheet1")) + 1
> Set sr = ThisWorkbook.Worksheets("DEFAULTS").Range("U2:AB2")
> Sheets("Sheet1").Select
>
>
> Set dr = dwb.Worksheets("Sheet1").Range("A" & Lr)
> sr.Copy
> dr.PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
>
> Sheets("Sheet1").Select
> Range("E2").Select
>
> '**************************************************************
> dwb.Close True
> Application.ScreenUpdating = False
>
> '***********************************************************
> 'SAVE AS AS READ ONLY
> SetAttr " C:\Test\Pants\color.xls ", vbReadOnly
> '**********************************************************
>
> '******************END PROGRAM*****************************
>

[Chuckles123]

I need to better understand the default when Code asks an .xlS file to open on a shared network.

It may be my imagination, but it seems I am getting inconsistent results whan an user has a shared network file open on his/her network and such file is being opened on either my desktop or on an Autosys server. I want to make changes in such file and send out e-mails based on the results, but I do not want to save any changes in such file (using neither the same filename nor any revised filename).

Is below correct, for the two fact situations?

Fact Situation 1 -- an user has such shared network file open, but such file is not involved in a macro that has crashed. My macro would open the file as 'read only' and my macro would run, and shut Excel down, without any errors.

Fact situation 2 -- an user has such shared network file open, and such file is involved in a macro that has crashed. My macro would not run, unless such error is trapped.

Thanks for a response,
Chuckles123

[Zack Barresse]

The function posted earlier will not be effective for a shared file opened
over the network. For that, check out:
http://www.xcelfiles.com/IsFileOpen.html

HTH

--
Regards,
Zack Barresse, aka firefytr



"Chuckles123" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to better understand the default when Code asks an .xlS file to
> open on a shared network.
>
> It may be my imagination, but it seems I am getting inconsistent
> results whan an user has a shared network file open on his/her network
> and such file is being opened on either my desktop or on an Autosys
> server. I want to make changes in such file and send out e-mails based
> on the results, but I do not want to save any changes in such file
> (using neither the same filename nor any revised filename).
>
> Is below correct, for the two fact situations?
>
> Fact Situation 1 -- an user has such shared network file open, but such
> file is not involved in a macro that has crashed. My macro would open
> the file as 'read only' and my macro would run, and shut Excel down,
> without any errors.
>
> Fact situation 2 -- an user has such shared network file open, and such
> file is involved in a macro that has crashed. My macro would not run,
> unless such error is trapped.
>
> Thanks for a response,
> Chuckles123
>
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile:
> http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=534809
>

[Chuckles123]

Your posted link was very educational -- thanks.

However, please review my two fact situations above: I think the first one results in Excel VBA ignoring whether the file of interest is open by another network user or not.

Obviously, the second fact situation would require error trapping to report the error.

Thanks for a response,
Chuckles123

[Zack Barresse]

There shouldn't be a difference in the two. If the file is open and seen
open by your station, it will prompt to open at read only. Whether or not
it errors out is highly subjective and I wouldn't feel comfortable giving a
concrete answer there. If a "macro crashed" there are many things involved
which are complex and dynamic. Best thing I can tell you is to test it all.
There is no test like actually doing it.

--
Regards,
Zack Barresse, aka firefytr


"Chuckles123" <[email protected]>
wrote in message > Fact Situation 1 -- an user has such shared network file
open, but such
> file is not involved in a macro that has crashed. My macro would open
> the file as 'read only' and my macro would run, and shut Excel down,
> without any errors.
>
> Fact situation 2 -- an user has such shared network file open, and such
> file is involved in a macro that has crashed. My macro would not run,
> unless such error is trapped.

[Chuckles123]

I have thoroughly tested Fact Situation 1, with each of the following statements, separately, in my code:

Application.DisplayAlerts = False
'Application.DisplayAlerts = False

In both cases, the file to be opened by the macro was, firstly, opened by another user on our network; in both cases, the file was opened by the macro as 'Read Only' with no pop-ups.

I will also test Fact Situation 2; I guess it is not important for the macro to "crash" on the other user's machine - if the other user's macro is being "stepped thru" and such code opens the file of interest and such user "does nuthin" to allow for my testing <-- that should work as well. Do you agree?

Thanks for a response,
Chuckles123

[Chuckles123]

RE: 2 FACT SITUATIONS IN MY POST OF APR 27

VBA works differently than Excel 2003 in this regard: VBA makes the assumption that you want to open a file as 'Read Only' if that file should be currently open on another user's machine <-- this is true even if VBA has already processed an "Application.DisplayAlerts = True" (this is the default) statement. The above also applies if another user is running a macro that has opened that same file.

Chuckles123