Solved

Check if workbook is open and then close

Posted on 2010-08-25
22
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

751 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