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

 

[GaryStainburn]

Thanks Rory, I should have spotted that one.

It's working fine now. Thanks to everyone