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

GaryStainburn

GaryStainburn

New Member
Joined
Sep 14, 2010
Messages
13
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:

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:

Code: 
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Hi Gary

Do you have an On Error Resume Next statement somewhere in your code above what you showed? If so, you need to comment it out and run the code again and tell us what the error code is.
 
Upvote 0
Path doesn't have a trailing backslash:

Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & "Period_TB.xlsx"
 
Upvote 0
Richard,

I dont' have any 'on error' statements anywhere

Doofusboy,

The spreadsheet is in the same folder. I presume that the fact that this is a mapped network drive has no effect on this.

Andrew,

I've changed my code so that it matches what you have, but the behavior of the code is unchanged.
 
Upvote 0
I know it's a wild suggestion but are you sure that the code is actually trying to open the file ... perhaps the test for it already being open is failing - or being an old hand have you already proved that :-)
 
Upvote 0
If the code is failing, you should be getting an error of some kind, unless you do have an On Error Resume Next (or other handler) somewhere in the call stack - note it doesn't have to be in the sub that is failing, it can be in a sub that calls that sub (the error will be passed back up).
 
Upvote 0
I have checked by stepping through the code that the If statement works, and that when the workbook is not already open, it does call the Open() statement.

(If it is already open, stepping through the code jumps straight to the 'end if' clause.

I know that there is no 'on error' clause as prior to me starting work on this project there were no macros or functions defined.

(I know that there is no 'on error' clause anywhere in my code. Could it be elsewhere?)
 
Upvote 0
Can I suggest a random experiment .... try changing the code so that it tries to open a file you know doesn't exist in your folder and see what happens ..... it should prove that error handling is working properly if nothing else.

I tried that (in Excel 2003) and it gave an error, then I tried it for an xls file and it worked correctly ( could xlsx files be treated differently ? ).....

... next step I would do would be to put the full path and name in a single variable and try to open that (basically throwing loads of combinations against the overall code to see what is wrong).
 
Upvote 0
WaterGypsy said:
Can I suggest a random experiment .... try changing the code so that it tries to open a file you know doesn't exist in your folder and see what happens ..... it should prove that error handling is working properly if nothing else.

I tried that (in Excel 2003) and it gave an error, then I tried it for an xls file and it worked correctly ( could xlsx files be treated differently ? ).....

... next step I would do would be to put the full path and name in a single variable and try to open that (basically throwing loads of combinations against the overall code to see what is wrong).
Click to expand...

I changed the filename to one that definitely doesn't exist, and the code behaved exactly the same. The Open() statement finished silently, and the function exited at the Period assignment.

I then tried putting the full currect filename in workbook I'm trying to open

Code: 
Workbooks.Open "Z:\DATABASE\Period_TB.xlsx"

and it still behaved exactly the same.

So, the questions are
1) Why is it failing silently?
2) Why is it failing at all?

BTW, I'm using Excel 2007 on WinXP Sp3
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
302
Big_Al_fae_the_rigs
B
J
  • Question Question
VBA Script to Update Files in a folder, updating Links
Replies
1
Views
593
Micron
M
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
N
  • Solved
Import multiple csv to separate worksheets (initial code within)
Replies
6
Views
7K
NRGZ
N
M
  • Question Question
vba help - function is workbook open not working
2
Replies
10
Views
8K
GWteB
GWteB

Forum statistics

Threads
1,225,149
Messages
6,183,181
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top