0

I've tried to scrabble together several other pieces of VBA to achieve this, but am a beginner with the language so have had no luck. I would like to use VBA to achieve the following:

  • Run the below on load:
  • Check whether another workbook is currently open. This workbook is specified by a named range (input_sheet_location) which contains the entire network location of said workbook.
  • If it is not open, ask the user whether it is okay to open it.
  • If that load fails (i.e. it can't be found/is restricted/the named range is empty), exit the sub with an error message and allow the user to continue.
  • If it is open, proceed without prompt.

I am 99% sure all of these things are achievable; I simply do not have the knowhow required to string them all together. As such, the only piece of the puzzle I'm truly lacking is that second bullet point above; anything else, I can (hopefully) figure out.

Thanks for your time!

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
seegoon
  • 563
  • 1
  • 8
  • 15
  • For the 2nd bullet point, see if this helps? http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba/9374481#9374481 – Siddharth Rout May 18 '12 at 08:58
  • that link should get you past 2nd bullet point. If you get stuck on the other stuff, let us know. I recently did a project with all sorts of error checking and trapping related to this stuff. – Scott Holtzman May 18 '12 at 14:22

1 Answers1

0

I managed to figure it; thanks for the response.

It goes like this:

Sub IsInputSheetOpen()
Dim Ret

Ret = IsWorkBookOpen(range("input_sheet_location_nobrackets"))

If Ret = True Then
    MsgBox "Input sheet is open."
Else
    MsgBox "Input sheet is not open. Auto output won't work without it."
End If
End Sub

Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0:    IsWorkBookOpen = False
Case 70:   IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

The key thing was to format my file location within the named range (input_sheet_location_nobrackets) without the square brackets you need in order to use it as a reference. I'm sure the above is quite messy, but it seems to work!

seegoon
  • 563
  • 1
  • 8
  • 15