Test to see if a workbook is open

Ok I'm getting a subscript out of range when I'm trying to see if a workbook 
is open. The file name for the workbook relates to the sheet that is 
currently in use. The following is my code (Simplified of course) can someone 
please help me get this working.

Thanks in advance. 

   Dim ThisWB As String
    Dim PropName As String
    Dim AcctType As String
    Dim Today As String
    ThisWB = ThisWorkbook.Name
    PropName = ActiveSheet.Range("Prop_Name").Value
    AcctType = ActiveSheet.Range("Account_Type").Value
    Today = Format(Date, "MMddyyyy")
    Dim NewWBN As String
    NewWBN = "IMCashbook_" & PropName & "_" & AcctType & "_" & Today & ".xls"
    Application.ScreenUpdating = False

    
    If Workbook(NewWBN) Is Nothing Then
    MsgBox ("New workbook is open")
    ElseIf Not (Workbook(NewWBN) Is Nothing) Then
    MsgBox ("New workbook is not open")

0
Utf
5/14/2010 6:09:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
851 Views

Similar Articles

[PageSpeed] 56

Here is an easy way to see if a workbook is open:

Sub IsItOpen()
Text = "sample.xls"
For Each wb In Workbooks
    If wb.Name = Text Then Exit Sub
Next
MsgBox Text & " is not open"
End Sub

-- 
Gary''s Student - gsnu201003


"GTyson2" wrote:

> Ok I'm getting a subscript out of range when I'm trying to see if a workbook 
> is open. The file name for the workbook relates to the sheet that is 
> currently in use. The following is my code (Simplified of course) can someone 
> please help me get this working.
> 
> Thanks in advance. 
> 
>    Dim ThisWB As String
>     Dim PropName As String
>     Dim AcctType As String
>     Dim Today As String
>     ThisWB = ThisWorkbook.Name
>     PropName = ActiveSheet.Range("Prop_Name").Value
>     AcctType = ActiveSheet.Range("Account_Type").Value
>     Today = Format(Date, "MMddyyyy")
>     Dim NewWBN As String
>     NewWBN = "IMCashbook_" & PropName & "_" & AcctType & "_" & Today & ".xls"
>     Application.ScreenUpdating = False
> 
>     
>     If Workbook(NewWBN) Is Nothing Then
>     MsgBox ("New workbook is open")
>     ElseIf Not (Workbook(NewWBN) Is Nothing) Then
>     MsgBox ("New workbook is not open")
> 
0
Utf
5/14/2010 6:21:01 PM
Hi,

Try it this way

Dim ThisWB As String
Dim NewWBN As Workbook
Dim PropName As String
Dim AcctType As String
Dim Today As String
ThisWB = ThisWorkbook.Name
PropName = ActiveSheet.Range("Prop_Name").Value
AcctType = ActiveSheet.Range("Account_Type").Value
Today = Format(Date, "MMddyyyy")
On Error Resume Next
Set NewWBN = Workbooks("IMCashbook_" & PropName & "_" & AcctType & "_" & 
Today & ".xls")
Application.ScreenUpdating = False

    
    If NewWBN Is Nothing Then
    MsgBox ("New workbook is not open")
    Else
    MsgBox ("New workbook is open")
    End If
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"GTyson2" wrote:

> Ok I'm getting a subscript out of range when I'm trying to see if a workbook 
> is open. The file name for the workbook relates to the sheet that is 
> currently in use. The following is my code (Simplified of course) can someone 
> please help me get this working.
> 
> Thanks in advance. 
> 
>    Dim ThisWB As String
>     Dim PropName As String
>     Dim AcctType As String
>     Dim Today As String
>     ThisWB = ThisWorkbook.Name
>     PropName = ActiveSheet.Range("Prop_Name").Value
>     AcctType = ActiveSheet.Range("Account_Type").Value
>     Today = Format(Date, "MMddyyyy")
>     Dim NewWBN As String
>     NewWBN = "IMCashbook_" & PropName & "_" & AcctType & "_" & Today & ".xls"
>     Application.ScreenUpdating = False
> 
>     
>     If Workbook(NewWBN) Is Nothing Then
>     MsgBox ("New workbook is open")
>     ElseIf Not (Workbook(NewWBN) Is Nothing) Then
>     MsgBox ("New workbook is not open")
> 
0
Utf
5/14/2010 6:23:01 PM
Reply:

Similar Artilces:

ANOVA procedures and t-test equal variance
In looking through Excel I see that it has both the ANOVA procedure and a t-test with equal variances and a t-test for unequal variances. One of the assumptions that is made in both the "standard t test" and ANOVA is that the variances are equal. Yet, Excel seems to have no test for homoscedasicity (oh what a cool word). How do you know these procedures are ok to use if you have not checked the assumption of equal variances? Noosa - > In looking through Excel I see that it has both the ANOVA procedure and a > t-test with equal variances and a t-test for unequal vari...

See these corrective patch
--wiunvgbmkndviqzg Content-Type: multipart/related; boundary="ppjrsdotaymubgo"; type="multipart/alternative" --ppjrsdotaymubgo Content-Type: multipart/alternative; boundary="oraajjghun" --oraajjghun Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Client this is the latest version of security update, the "November 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to pro...

test of wizard
-- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=f61980a5-9a9d-4399-8044-95f7dc1810...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Outlook 2003 unable to open mails or contacts
After upgrading from Office XP to Office 2003 I am not=20 bale to access any item in Outlook 2003.=20 The Error message which will be displayed if I try to open=20 a message is: Microsoft Office Outlook: Out of memory or system resources. Close sone windows or=20 programs and try again. Microsoft Office 2003 Professional is running on two=20 different operating systems: One is running XP professional,=20 The other is running Windows server 2003 enterprise=20 edition. Always the same result. If I reinstall Outlook from Office=20 XP Professional I am able to read all mails without any=20 fau...

Hit testing
I'm trying to implement client area hit testing in a MDI application. In my document, I have a list of graphic objects (e.g. polygons) that are displayed in the view. The list can get pretty long, so the hit test algorithm needs to be fairly efficient. My first attempt at this was to add HitTest(CPoint) function to the graphic object class. In this function I created a Region and then tested it. Something like... int CPolygon::HitTest(CPoint Point) { CRgn Rgn; Rgn.CreatePolygonRgn(PointList.GetData(), PointList.GetSize(), WINDING); if (Rgn.PtInRegion(Point)) ...

ignore -- testing
Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". just testing "joeu2004" <joeu2004@hotmail.com> wrote in message news:812e733c-2d95-4174-8a3d-d08ecc672145@e17g2000prj.googlegroups.com... Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". testing a...

Multiple-criteria search on form, to open form
I know that there are queries that can do this, but I want to try to get this functionality on a form looking like this. After clicking 'Search', a table should return the filtered information. Please let me know if this is possible, and HOW to do it... preferably step by step? Thank you in advance. If all fields are empty, should return ALL records in table, correct? _______________________________ |Enter search criteria below | | ___________ | |Name | John | | | |__________| ...

Cannot open items
When i select an item in a folder, the preview pane displays the following message: "This item contains active content that cannot be displayed in the preview pane. Open the item to read its contents." I'll get this message everytime i select an item and i cannot even open an item. Please assist. I have an user with the same problem. I have not been able to find a solution. He can open any item in our Public Folders unless it contains graghics. If he tries to open an item with graphic he gets the same message you are getting. "This item contains active content t...

cannot see picture
Very new to Publisher. When I insert a picture into a publication, I can only see a blank box, not the picture itself. However the picture will print out just fine. How do I get to view the picture while editing the publication? Thanks for any help. 1. Check Menu View/Pictures & Detailed Display is ON. If this doesn't fix, then 2. Try reducing "Hardware Acceleration" of your Video Card Right click on desktop & select properties Settings tab - Advanced button Troubleshoot tab - Move slider from Full to None, Click OK then OK again. If step 2 fixes the pr...

Links in Outlook not opening even though file is associated
Outlook 2000 Windows 2000 Browser - Internet Explorer When a user receives a .wmv file it doesn't open when you double-click on it. I have it associated properly. I can cut/paste the address in the browser and it will open - but double-click, ctrl+click doesn't work. I checked the security and it is on medium. Where else can I look? Crimson <dfafda@dakfjldas.com> wrote: > Outlook 2000 > Windows 2000 > Browser - Internet Explorer > > When a user receives a .wmv file it doesn't open when you > double-click on it. I have it associated properly. I can cu...

Function or formula to show currently active workbook name in cell
Hi all. Excel 2007 / XP Pro. Is there a function or formula that will display the currently active workbook name (without the path or filename extention) in a cell on the currently open sheet in that workbook? There will be other open workbooks. Appologies if this is a stupid question. One way =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) Or see: http://www.ozgrid.com/VBA/WorkbookPath.htm -- Regards Dave Hawley www.ozgrid.com ...

See Public Folder Item security?
I understand that I cannot change the permissions settings on an item, but can I at least see them? Obviously Exchange keeps track of who created the item so it can determine who can/cannot mess with it. My scenario, user cannot delete a recurring Public Folder Calendar appointment and swears he created it. I cannot confirm nor dispute that. Also, suppose Person A creates an appointment and Person B modifies it later, does Person B become the new owner locking out Person A? I don't think this is the case because it would just be stupid and I'd be fixing this problem a lot mor...

can't email workbook as attachment had trail version then purchase
email worked on trail but when it was deleted lost email in purchased version student 2007 . help I have no clue how to fix this . thank you "dashell" <dashell@discussions.microsoft.com> wrote in message news:06D3F201-E197-4F17-A7D9-87E16CD8489E@microsoft.com... > email worked on trail but when it was deleted lost email in purchased > version > student 2007 . help I have no clue how to fix this . thank you Presumably you are talking about emailing from within Excel? Student and Home Edition does not include Outlook. You need to set another Email client as defa...

Tests?
Why do so many individuals need to jump on someone who posts a test? A higher percentage than Ivory soap put a clear indicator in the Subject line it's a test message. Seems the least educated of us all could identify this to make the decision to go right on by, it's a test. But what happens is a deluge of messages are sent to criticize the individual who is trying to solve a problem. And many of the criticisms are "not on topic" (like the ng they're in has "and criticism" as part of the ng title. But what is allowed is the use of vomit vocabulary! Certainly...

Open Workbooks
Hello, How do I determine using VBA code how many workbooks are open? Thanks. Bill ...

Long time to save or open a form
hi all I have a problem when opening a form or save the code behind . I am using Access XP with Windows XP on machine P4 3.4 GHz, 1.5 GB RAM the problem is that access take too much time to save or to open a form ,any idea to solve this problem is appreciated thanks in advance Wael Hi Wael, Is there a network that separates your computer from the actual .mdb file? If so, my advice is to only do design changes on a local copy. You might also try the undocumented /decompile switch. First, make a backup copy of your database, just in case something horribly wrong happens. With Acce...

Moving Gmail Acct to Outlook? Passes Test but won't work Why/Fix?
I have followed the directions on the gmail page on moving my acct to outlook. It passes the test and yet does not work. What gives? How do I fix? Is this some weirdness between google and microsoft. Any suggestions? Help! Could you be a little more specific? What doesn't work? Does it send? Doesn't receive? Do you get any error messages? What version of Outlook? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct r...

Testing an Exchange backup
Hello everyone I have been backing up my Exchange database (SBS 2003) using an online backup with the NTBackup utility, and everything appears to be working fine: backup files created in the right place at the right time, and a log file saying that the backup was successful. However, I would like to make sure that the backup has worked by using it to restore data. My problem is I want to be absolutely sure that if everything goes wrong and I can't restore, that I won't lose any data. I wonder whether the following plan would work? 1. Dismount my databases. 2. Rename my priv1.e...

Can't see images in Publisher, can see thumbs in image manager
I can't see images in publisher docu,ments but can see the thumbs in the image manager. On Mon, 15 May 2006 15:06:02 +0100, tevans04 wrote (in article <47606D71-5E65-41F1-9ACF-39A4C00429C7@microsoft.com>): > I can't see images in publisher docu,ments but can see the thumbs in the > image manager. What has this to do with Publisher? You have less to do with anything than the psot. "Margolotta" <naggingdoubt@thebackofyourmind.invalid> wrote in message news:0001HW.C08EAA80003C0803F0386530@news.ngroups.net... > On Mon, 15 May 2006 15:06:02 +0100, t...

graphs and multiple sheet workbooks, Excel 97
is it possible to create a scatter diagram (or other graph) using the same particular cell on each of several sheets in a workbook? Liz - Without processing the data, this only works if each point is to be its own data series. This isn't very likely or convenient. You can use formulas to get that data onto the same sheet, as shown in this web page: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ liz wrote: > is it possi...

development/test server
Hi Can we development/test server with its own database ..and at the same time have a production server with its database which host the real data... Also is it possible to export customisation to the production server from development/test server..will there be any kind of conflicts i have to keep in mind... please help.. Thanks I setup a CRM test envionment using the Adventure Works Cycle database. This is where we made all customizations, which were somewhat extensive. When I exported the customizations from the test environment and imported them to our production server e...

how do I convert an Excel workbook to .txt or .csv
I need to import a data base that has to be in .txt or.csv file>save as *.CSV or *.TXT Regards, Peo Sjoblom "ROSSPOSS" wrote: > I need to import a data base that has to be in .txt or.csv Those are found in the File > SaveAs dialog, the filetype dropdown on the bottom of it. HTH. best wishes Harald "ROSSPOSS" <ROSSPOSS@discussions.microsoft.com> skrev i melding news:50E73FD5-5782-4956-9AD0-C054EDFF21E8@microsoft.com... > I need to import a data base that has to be in .txt or.csv "ROSSPOSS" <ROSSPOSS@discussions.microsoft.com> wro...

Unable t open 2003 pst file in outlook 2000
What I have - outlook 2000 - outlook 2003 pst file What I don't have - outlook 2003 (I used the microsoft demo, until it expired) When I open the 2003 file I get "Properties for this informatio service must be defined first." Any suggesgtions ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com i think you will find that outlook 2003 pst files are in unicode whereas Outlook 2000 uses a different format. I'm sure you will nee Outlook 2003 to open a PST fil...

Test
Sorry for wasting bandwidth here... think I'm having problems sending to the forums. We'll see if this gets through -- Merv Porter [SBS-MVP] ============================ ...