By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,673 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,673 IT Pros & Developers. It's quick & easy.

Check if Workbook is open and close if it is

P: 8
Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.
May 23 '07 #1
Share this Question
Share on Google+
13 Replies

JConsulting
Expert 100+
P: 603
Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.
How about something easy

Expand|Select|Wrap|Line Numbers
  1. Function fFileOpen(strFile As String) As Boolean
  2. On Error Resume Next
  3.  
  4.     Dim intFile As Integer
  5.     If Dir(strFile) = "" Then Exit Function
  6.  
  7.     intFile = FreeFile()
  8.     Open strFile For Input Lock Read As intFile
  9.     Close intFile
  10.  
  11.     If err <> 0 Then
  12.         fFileOpen = True
  13.     End If
  14.  
  15. End Function
  16.  
May 23 '07 #2

JConsulting
Expert 100+
P: 603
Looking for VBscript that will check to see if a specific Excel work book is open, by passing a variable name to check for which is the full file name with path. If it is open, it closes it.

About the close part.

Unless you are the person/program with the excel file open, you cannot close it.
May 23 '07 #3

P: 8
[JConsulting]

When I run code, I get a Run Time error '70'; Permission denied when I actually have a file open on code line which reads:

Open strFile For Input Lock Read As intFile

For some reason, won't "resume" as code tells it to on error. Odd

About the close part, only I have the file open so it is my file.
May 23 '07 #4

JConsulting
Expert 100+
P: 603
[JConsulting]

When I run code, I get a Run Time error '70'; Permission denied when I actually have a file open on code line which reads:

Open strFile For Input Lock Read As intFile

For some reason, won't "resume" as code tells it to on error. Odd

About the close part, only I have the file open so it is my file.
how are you running the code?
May 23 '07 #5

P: 8
how are you running the code?
Pasted your code in a Module.

Running code from a form, on click, right now just asking to display status

MsgBox fFileOpen(fname) ' where fname includes path & filename

Is that what you were asking?
May 23 '07 #6

JConsulting
Expert 100+
P: 603
Pasted your code in a Module.

Running code from a form, on click, right now just asking to display status

MsgBox fFileOpen(fname) ' where fname includes path & filename

Is that what you were asking?
you're doing everything right...can you capture the error?

if err.number = 77 then
fFileOpen = true
end if
May 23 '07 #7

P: 8
[quote=JConsulting]you're doing everything right...can you capture the error?

Won't let me...seems to get here

Open strFile For Input Lock Read As intFile

and get stuck and will go no further, won't execute the "on error" code at all; tried turning off warnings and still won't execute next line of code.
May 23 '07 #8

JConsulting
Expert 100+
P: 603
you're doing everything right...can you capture the error?

if err.number = 77 then
fFileOpen = true
end if

Here is an option capturing the errors

Expand|Select|Wrap|Line Numbers
  1. Sub TestExcel(strFile)as boolean
  2.     Dim intFreeFile As Integer
  3.  
  4.     On Error Resume Next
  5.     intFreeFile = FreeFile
  6.     Open strFile For Input Lock Read As #intFreeFile
  7.     Select Case Err.Number
  8.         Case 70
  9.             MsgBox "This file is already in use"
  10.                  TestExcel = true
  11.         Case 0
  12.             Close #intFreeFile
  13.         Case Else
  14.             '   Some other error
  15.     End Select
  16.  
  17. End Sub
  18.  
May 23 '07 #9

JConsulting
Expert 100+
P: 603
Here is an option capturing the errors

Expand|Select|Wrap|Line Numbers
  1. Sub TestExcel(strFile)as boolean
  2.     Dim intFreeFile As Integer
  3.  
  4.     On Error Resume Next
  5.     intFreeFile = FreeFile
  6.     Open strFile For Input Lock Read As #intFreeFile
  7.     Select Case Err.Number
  8.         Case 70
  9.             MsgBox "This file is already in use"
  10.                  TestExcel = true
  11.         Case 0
  12.             Close #intFreeFile
  13.         Case Else
  14.             '   Some other error
  15.     End Select
  16.  
  17. End Sub
  18.  

and another if that fails to work for you

Expand|Select|Wrap|Line Numbers
  1. Private Function IsFileAlreadyOpen(Filename As String) As Boolean
  2. '   Returns TRUE if the workbook is open
  3.   Dim x As Workbook
  4.   On Error Resume Next
  5.   Set x = Workbooks("c:\test.xls")
  6.   If Err = 0 Then IsFileAlreadyOpen = True _
  7.       Else IsFileAlreadyOpen = False
  8. End Function
  9.  
May 23 '07 #10

P: 8
and another if that fails to work for you

Expand|Select|Wrap|Line Numbers
  1. Private Function IsFileAlreadyOpen(Filename As String) As Boolean
  2. '   Returns TRUE if the workbook is open
  3.   Dim x As Workbook
  4.   On Error Resume Next
  5.   Set x = Workbooks("c:\test.xls")
  6.   If Err = 0 Then IsFileAlreadyOpen = True _
  7.       Else IsFileAlreadyOpen = False
  8. End Function
  9.  
Pasted your code with the following in a new module and it worked:

Expand|Select|Wrap|Line Numbers
  1. Private Declare Function lopen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
  2. Private Declare Function GetLastError Lib "kernel32" () As Long
  3. Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
Now how do I close a file if it is found open by this code?
May 24 '07 #11

JConsulting
Expert 100+
P: 603
Pasted your code with the following in a new module and it worked:

Private Declare Function lopen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function GetLastError Lib "kernel32" () As Long
Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long

Now how do I close a file if it is found open by this code?
Expand|Select|Wrap|Line Numbers
  1. Dim xlBook As Excel.Workbook, xlApp As Excel.Application
  2. Set xlBook = GetObject("Your Excel File Name")
  3. Set xlApp = xlBook.Parent
  4. xlBook.Save
  5. xlBook.Saved = True
  6. xlBook.Close
  7. Set xlBook = Nothing
  8. xlApp.Quit
  9. Set xlApp = Nothing
  10.  
May 24 '07 #12

P: 8
Expand|Select|Wrap|Line Numbers
  1. Dim xlBook As Excel.Workbook, xlApp As Excel.Application
  2. Set xlBook = GetObject("Your Excel File Name")
  3. Set xlApp = xlBook.Parent
  4. xlBook.Save
  5. xlBook.Saved = True
  6. xlBook.Close
  7. Set xlBook = Nothing
  8. xlApp.Quit
  9. Set xlApp = Nothing
  10.  
Thanks a bunch for all your help!
May 24 '07 #13

JConsulting
Expert 100+
P: 603
Thanks a bunch for all your help!
Happy to help. Let us know if you need any more help.
J
May 24 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.