Solved

Check if workbook is open and then close

Posted on 2010-08-25
22
532 Views
Last Modified: 2013-11-27
Ok, after reviewing several samples on-line, the attached theoriecally should work.  I want to check to see if the workbook is open, if it, then save and close.  But I keep getting a subscript out of range error message.
Public Sub FileClose(strReportDate As String)

Dim strPath As String

Dim wbkName As String

Dim wbk As Workbook

wbkName = "Intl_Index_" & strReportDate & ".xls"

strPath = DLookup("Directory", "tblDirectory", "Type = 'strPath' ")

    

    On Error Resume Next

    Set wbk = Workbooks("Intl_Index_" & strReportDate & ".xls")

     

    If wbk Is Nothing Then

        'Do nothing

    Else

        wbk.Close

    End If



End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 14
  • 6
  • 2
22 Comments
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33526834
First off, I'm not sure what you're doing with strPath, as it doesn't get used. Unless you have some future plan for it, you'd be best served removing it from the procedure to reduce clutter.
Secondly, the remaining code should work perfectly fine, assuming the value of strReportDate is correct. I would double-check the name of the workbook and the value of strReportDate after it is passed to your Sub to make sure the value is what you would expect.
0
 

Author Comment

by:ssmith94015
ID: 33526848
strPath is, if the file is opened, the directory that the file will be saved to.  I think I may have gotten so frustrated and simply left that part out.  When I get to the close part, I was thinking the path has to be put in there somewhere.  It seems every time I have do to this in ACCESS i have problems.
0
 

Author Comment

by:ssmith94015
ID: 33526854
Yes, tested that there were no typos, first thing I looked for.  But it simply will not recognized the name.  Which is irritating as I have used this pattern throught various databases and no problems.
0
 

Author Comment

by:ssmith94015
ID: 33526858
BTW, this is a procedure in ACCESS 2003 trying to control EXCEL.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33530823
I always use the BrowseForFolder fucntion found here :
http://www.vbaexpress.com/kb/getarticle.php?kb_id=284
See code below.....it works but still not quite sure what you're trying to accomplish with the folder......

Option Explicit



Public Sub FileClose(strReportDate As String)

Dim strPath As String

Dim wbkName As String

Dim wbk As Workbook

wbkName = "Intl_Index_" & strReportDate & ".xls"

strPath = BrowseForFolder()

      

    On Error Resume Next

    Set wbk = Workbooks(wbkName)

       

    If wbk Is Nothing Then

        'Do nothing

    Else

        wbk.Close

    End If

  

End Sub





 

Function BrowseForFolder(Optional OpenAt As Variant) As Variant

     'Function purpose:  To Browser for a user selected folder.

     'If the "OpenAt" path is provided, open the browser at that directory

     'NOTE:  If invalid, it will open at the Desktop level

     

    Dim ShellApp As Object

     

     'Create a file browser window at the default folder

    Set ShellApp = CreateObject("Shell.Application"). _

    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     

     'Set the folder to that selected.  (On error in case cancelled)

    On Error Resume Next

    BrowseForFolder = ShellApp.self.Path

    On Error GoTo 0

     

     'Destroy the Shell Application

    Set ShellApp = Nothing

     

     'Check for invalid or non-entries and send to the Invalid error

     'handler if found

     'Valid selections can begin L: (where L is a letter) or

     '\\ (as in \\servername\sharename.  All others are invalid

    Select Case Mid(BrowseForFolder, 2, 1)

    Case Is = ":"

        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid

    Case Is = "\"

        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid

    Case Else

        GoTo Invalid

    End Select

     

    Exit Function

     

Invalid:

     'If it was determined that the selection was invalid, set to False

    BrowseForFolder = False

     

End Function

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 33532982
MW, will try your suggestion.  Basically, I simply want to check if the workbook is open and if it is, then close it, saving it as well.  Otherwise, if it is not open, simply do nothing.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33533373
right but what is the purpose of selecting a saving location?  If the workbook is open, and if you're checking it with a file path, then it is already saved somewhere.
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33533563
The short answer to your original question is that your code works, assuming the workbook name is correct. In fact, I tried your code as is using my own workbook name. The longer answer, of course, is there's something else going on.
Where is the VBA code you're running? Is it in Access or Excel? Based on your code, I would assume its in Excel, and If it is in Excel, how are you invoking the procedure from Access?
0
 

Author Comment

by:ssmith94015
ID: 33537297
The code is in ACCESS 2003.  So, MW, I don't need to worry about the path if the workbook is already opened, that is good so I only need to check to see if it is open, if it is, then simply close it.  Yes, several others have tried the code and it works for them - it is in a procedure in ACCESS 2003 and I think I am losing some hair.......
0
 

Author Comment

by:ssmith94015
ID: 33537373
I have returned to this, but every time I step through the code with the workbook open, it simply refuses to see that the workbok is open.  Just skips over the code.  I did try the procedure in an Excel workbook and it works perfectly.  However, I need this to work from ACCESS 2003.  Library references are set so I know it is not that.
0
 

Author Comment

by:ssmith94015
ID: 33537392
HHMMMMMmmmm, should I be getting the Excel object?  HOwever, the user could have more than one Excel application opened so depending on only one instnace being opened to check won't work.  Perhaps I should cycle thorugh all the open excel (first checking to see if Excel is opened, becaue it if is not, then checking for an open workbooks is moot).
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Expert Comment

by:yesthatbob
ID: 33537514
Yes, you need to reference Excel. I'm surprised you're not bombing on the line:
Set wbk = Workbooks(wbkName)  
Access wouldn't have a Workbooks collection, so it should raise an error.
0
 

Author Comment

by:ssmith94015
ID: 33537569
Yes, I had expected an error, but nothing is thrown to the error handler, it just skips the line as though all is fine, simply won't see the workbook.
0
 

Author Comment

by:ssmith94015
ID: 33537586
Well, at least tracked down why I did not get an error, there is a Resume Next statement so it simply did exactly what I was telling it to.  NOW I get a subscript out of range error.
0
 
LVL 4

Accepted Solution

by:
yesthatbob earned 500 total points
ID: 33537650
Now that I better understand the problem, try this:

Dim excelApp As Excel.Application

Dim wkb As WorkBook

  

Set excelApp = GetObject(, "Excel.Application")

For Each wkb In excelApp.Workbooks

    If wkb.Name = "Intl_Index_" & strReportDate & ".xls" Then

        wkb.Close

    End If

Next

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 33537804
This is what I now have and still refuses to see the open workbook!  I am getting too frustrated to deal with this, I have a message box to warn the user to close the file it is open, but I did not want to have to do that, but the only thing I can think is there is some security in the background that will not permit this activity.  I even tried hard-coding the file name rather than leaving it dyanmic, but still did not work.
Public Sub FileClose(strReportDate As String)

On Error GoTo ErrorHandler

Dim objExcel    As Excel.Application

Dim strPath     As String

Dim wbkName     As String

Dim wbk         As Excel.Workbook

wbkName = "Intl_Index_" & strReportDate & ".xls"

 

Set objExcel = GetObject(, "Excel.Application")

For Each wbk In objExcel.Workbooks

    If wbk.Name = "Intl_Index_0710.xls" Then

        wbk.Close

    End If

Next



Set objExcel = Nothing

Set wbk = Nothing



Exit_ErrorHandler:

    Exit Sub

ErrorHandler:

        If Err.Number = 429 Then

            Resume Next

        Else

            MsgBox Err.Number & " Description: " & Err.Description & " Source: FileClose"

            Resume Exit_ErrorHandler

        End If

End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33537857
Are you seeing any errors? The code I posted I actually wrote and tested in Access; it closed an open workbook just as expected. This is Excel 2003, right? We're not looking for an xlsx file instead of an xls file, right?
0
 

Author Comment

by:ssmith94015
ID: 33537893
Yes, Excel 2003 and ACCESS 2003.  What is strange is I have another procedures that works fine.  It creates an Excel file from a template, manipulates as I want and then saves it.  Actually I have several procedures that work on various stages of the report production massaging data and creating/destroying Excel workbooks, but this one little function and all I want is to check if the workbook is open or not, simply refuses to cooperate.
0
 

Author Comment

by:ssmith94015
ID: 33537895
I keep getting the subscript out of range error.
0
 

Author Comment

by:ssmith94015
ID: 33537922
I am going home for the day, I need a drink......
0
 

Author Comment

by:ssmith94015
ID: 33537945
OK, NOW IT WORKS.  Don't ask me why, but for some reason, it has decided to cooperate and finally do what I want.
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33543566
Sounds like the drink did the trick. ;-) Glad to hear you got it solved.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now