workbooks.open() silently not working, advice on diagnosing required

[GaryStainburn]

Hi folks,

I'm an I.T. manager who's mostly used to unix and web development.
I've been given the job of sorting out our 'Management Accounts' which is a spreadsheet which produces a summary of a number of input sheets generated by external systems.

I've been at this a couple of days and am slowly coming up to speed, but need help, both with the code, and suggestions on how to better debug the code.

My current problem is ensuring that the input sheets are already open before recalculations are don on the main spreadsheet, otherwise I get #VALUE errors all over the display.

I've done this by checking in the functions that call data from the other spreadsheets and if necessary opening the other workbooks.

The code:

Function IsWorkbookOpen(wbname) As Boolean
  Dim WB As Workbook
  IsWorkbookOpen = False
  For Each WB In Workbooks
    If WB.Name = wbname Then
      IsWorkbookOpen = True
    End If
  Next WB
End Function

works fine. If I manually open the workbooks it returns true. If the workbooks are not open it returns false. (Quick thank you to everyone on here and elsewhere that have provided code such as this for me)

I then have in my functions the following code extract:

If Not IsWorkbookOpen("Period_TB.xlsx") Then
  Workbooks.Open (ThisWorkbook.Path & "Period_TB.xlsx")
End If
Period = Workbooks("Period_TB.xlsx").Sheets("RingwaysLeeds").Range("C1")

The Workbooks.Open silently fails, and then the function exits because the cell C1 isn't available.

I have tried various syntax for the Open statement, but they all appear to do the same, i.e. fail to open the spreadsheet, but drop down to the next statement.

Can anyone please tell me what I've done wrong, or point me in the direction of how I can better find out why the open fails.

If the Period_TB.xlsx workbook is already open, the assignment to Period works fine.

Thanks

 

[RoryA]

Can we see the full code?

 

[GaryStainburn]

Below is the full code of the two functions. As you can see, I've tried commented out the Open() statement and replaced with with err.raise.

Am I right in thinking that this should generate a run-time error message and exit the function?

If yes, then that's another problem, because it isn't. It does exit the code, but I don't get anything displayed.

Function nGetNL(SName, Nominals() As Variant)
  Dim CNumber As Long
  Dim ULimit As Long
  Dim NlResult As Double
  Dim Period As String
  Dim Sh
  ULimit = UBound(Nominals)
  CNumber = 3
  If UCase(Nominals(ULimit)) = "Y" Then
    CNumber = 4
    ULimit = ULimit - 1
  End If
  If Not IsWorkbookOpen("Period_TB.xlsx") Then
    Err.Raise 13, "Workbook Period_TB.xlsx needs to be open"
    ' Workbooks.Open "Z:\DATABASE\Period_TB.xlsx"
  End If
  Period = Workbooks("Period_TB.xlsx").Sheets("RingwaysLeeds").Range("C1")
 
  NlResult = 0
  For N = LBound(Nominals) To ULimit
    NlResult = NlResult + WorksheetFunction.VLookup(Nominals(N), Workbooks("Period_TB.xlsx").Sheets(SName).Range("A:G"), CNumber, True)
  Next N
  nGetNL = NlResult
End Function
 
Function IsWorkbookOpen(wbname) As Boolean
  Dim WB As Workbook
  IsWorkbookOpen = False
  For Each WB In Workbooks
    If WB.Name = wbname Then
      IsWorkbookOpen = True
    End If
  Next WB
End Function

 

[WaterGypsy]

I guess I'm going to have to bow out gracefully (if that's possible) at this stage ... if you can't force it to produce an error maybe it's something at the excel level and since you're using later version than me I'm out of my depth (what do you mean I've been out of my depth since I joined this thread) .......

... two parting shots ....

... one I didn't mean to try and tell you the basics but it's very easy to forget them, hope I didn't offend

... two ... if you can't get anything to report an error in this workbook have you tried to force an error in another workbook to see if there is something set at the environemnt level

Good luck

 

[Richard Schollar]

How are you calling these functions? When Rory asked for the full code he wanted this also.


EDIT: you aren't trying to call these functions from a worksheet cell are you? If so, that won't work - the functions need to be called from a worksheet sub if you are to modify the environment (eg by opening a workbook).

 

[GaryStainburn]

Thanks for the help and the good luck wish.
I saw nothing to take offence about. Taking me back to basics has proved that there is something fundamentally wrong, and possibly not with my code.

I will start with a completely blank workbook and try to see at which point it fails.

 

[Richard Schollar]

Hi Gary

Did you see my follow up post (immediately above your last)?

 

[GaryStainburn]

Hi Gary

Did you see my follow up post (immediately above your last)?

Hi Richard,

No I didn't see that post.

The code I posted was for a function I have defined and am indeed calling from a cell.

Presumably I will need a seperate set-up function, in which I open all depending workbooks and call that when the main workbook is opened.

Is this what you mean, and if so ho do I do it?

 

[Richard Schollar]

Yep that sounds sensible. You could open all dependent workbooks in the Workbook_Open event of the master workbook. You just need a list of all the files that need to be open (eg in a worksheet range), reference this and iterate through it in the workbook_open making sure each is open as required:

Private Sub Workbook_Open()
Dim i As Long
Dim vList As Variant
 
'assume your list of files to be opened is in range A1:A10 of sheet "List" - obviously amend as appropriate
 
vList = Sheets("List").Range("A1:A10").Value  'transfer filenames to a variant array
 
'now iterate thru the list opening if closed:
For i = 1 to UBound(vList,1)
  If Not IsWorkbookOpen(vList(i,1)) Then   'check each workbook in list
    Workbooks.Open ThisWorkbook.Path & "\" & vList(i)
  End If
Next i
 
Thisworkbook.activate
 
End Sub

Place the above in the ThisWorkbook code module of the master workbook (ie the one currently containing your code).

 

[GaryStainburn]

Place the above in the ThisWorkbook code module of the master workbook (ie the one currently containing your code).

Hi Richard,

Finally had chance to have a go at this.

I've used the code you posted above, just changing the sheet name and range.

However, when I saved and re-opened the workbook I got a subscript out of range error on the workbook.open() line.

I've had a look at the variables and I don't see why it got a subscript error.

vList contians the two names I gave in the range (B1:B2)
vList(1,1)='Period_TB.xlsm'
vList(2,1)='PriorYear Database\PriorYear.xlsm'
i=1

 

[RoryA]

Change:

Workbooks.Open ThisWorkbook.Path & "\" & vList(i)

to:

Workbooks.Open ThisWorkbook.Path & "\" & vList(i, 1)