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?

 

[NickHK]

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

NickHK

 

[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]

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

 

[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

 

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

 

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

 

[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

 

[Chuckles123]

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

Application.DisplayAlerts = False
'Application.DisplayAlerts = False

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

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

Thanks for a response,
Chuckles12

 

[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