[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

Check if workbook is open and then close

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
ssmith94015
Asked:
ssmith94015
  • 14
  • 6
  • 2
1 Solution
 
yesthatbobCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ssmith94015Author Commented:
BTW, this is a procedure in ACCESS 2003 trying to control EXCEL.
0
 
MWGainesJRCommented:
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
 
ssmith94015Author Commented:
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
 
MWGainesJRCommented:
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
 
yesthatbobCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
yesthatbobCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
yesthatbobCommented:
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
 
ssmith94015Author Commented:
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
 
yesthatbobCommented:
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
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
I keep getting the subscript out of range error.
0
 
ssmith94015Author Commented:
I am going home for the day, I need a drink......
0
 
ssmith94015Author Commented:
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
 
yesthatbobCommented:
Sounds like the drink did the trick. ;-) Glad to hear you got it solved.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 14
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now