Proper way tp close VBA, VBS, and BAT file to prevent, "<filename>.xlsm is locked for editing" issue?

[SliderJeff]

Hey gang,

So I've noticed that I am evidently doing something incorrectly when I am invoking a particular bit of VBA code within a source.XLSM file. Here's my program file flow.

script.BAT calls script.VBS which calls "ProcessCVS" subroutine within script.XLSM.

Here are the pertinent open/close/save lines of each of these files.

script.BAT

@echo off
pushd %~dp0
rem Command Console Script - Feedback is in command window only
cscript script.vbs

script.vbs

Option Explicit
On Error Resume Next
DrillDataScripting
Sub DrillDataScripting() 
  Dim xlApp 
  Dim xlBook 
  
  Dim WshShell, strCurDir
  
  Set WshShell = CreateObject("WScript.Shell")
  strCurDir    = WshShell.CurrentDirectory
  Set WshShell = Nothing
  Set xlApp = CreateObject("Excel.Application") 
  
' Make Excel visible through the Application object
  xlApp.Application.Visible = True  
  
  Set xlBook = xlApp.Workbooks.Open(strCurDir & "\Script.xlsm")
  xlApp.Run "ProcessCSV"

' Close the XLSM file with VBA code in it and don't save changes
  xlBook.Close(False)
  
  Set xlApp = Nothing 
  Set xlBook = Nothing 

End Sub

script.xlsm

Sub ProcessDrillDataCSV()
Dim wbScript As Workbook
' Variable to hold source Workbook file
Dim wbOpen As Workbook
' Variable to hold destination output Workbook file for all processing
Dim wbNew As Workbook

' Turn on multithreading
    Application.MultiThreadedCalculation.Enabled = True

    Set wbScript = ActiveWorkbook
    
' Change to the appropriate directory with the files
    ChDir strPath
' List all of the CSV files in the current path folder
    strExtension = Dir("*.csv")
    
' Create the new workbook to store all of the statistical info and charts
    Set wbNew = Workbooks.Add(1)
    
'Change Path, Name and File Format to macro-enabled XLSM Excel workbook
    wbNew.SaveAs fileName:=Format(DateTime.Now, "yyyyMMdd hh-mm-ss") & " Output Dashboard", FileFormat:=xlOpenXMLWorkbookMacroEnabled
 
       
' Main loop to process files
    Do While strExtension <> ""
        Set wbOpen = Workbooks.Open(strPath & "\" & strExtension)
        
        With wbOpen
            Call formatDateAndResizeColumns
            Call FindExtremeValues(fileCount, Sheets(1).Name, wbNew, wbOpen, wbScript)
            Call FindStatValues(fileCount, wbOpen.Sheets(1).Name, wbNew, wbOpen, wbScript)
            .Close SaveChanges:=False
        End With
               
' Go to next CSV file in list
        strExtension = Dir
    
    Loop
        
' Make the "All Stats" sheet the active sheet
    Sheets("All Stats").Activate
    
' Set selection to cell A1 so that the upper left corner of the sheet is set to view
    Range("A1").Select
                
' Re-enable alerts
    Application.DisplayAlerts = True
            
' Save the file as it's currently opened name
    wbNew.Save
            
' Close the script file, itself, without saving
    wbScript.Close (False)

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    On Error GoTo 0
End Sub

I thought I had covered all my bases as far as closing the various different XLSM files, BAT files, etc... by pointing things to "Nothing" and such, but apparently not. Can anyone see where I may have missed closing something correctly?

What I can tell you from my observations is that the macro code is clearly closing my script.xlsm file, as it disappears from the VBE one I single step past the

wbScript.Close (False)

line of code. I also see the protect file with the "~$..." naming convention disappear from the working directory that all of the files are located within.

I'd appreciate any help you can provide.

Thanks,
Jeff

 

[John_w]

Try adding this immediately after the .Close in script.vbs:

xlApp.Quit

 

[SliderJeff]

Thanks, John. Won't that close Excel, though? I actually need it to remain open so that the user can view the output file created as a result of all the code being run. I'd also like them to be able to open the source.XLSM file which was (supposedly) closed inside the macro itself with the "wbScript.Close(False)" line of code. I will try what you suggest, though. Thanks again!

Regards,
Jeff

 

[SliderJeff]

Bummer. In answer to my question to John, no it doesn't close Excel. Unfortunately, I'm still getting the same "file in use" issue even after adding that line in VBS file if I try to load the script.xlsm file into Excel using File->Open or double-clicking on the file name from an Explorer window.

Jeff

 

[John_w]

Try instead deleting this line in Script.xlsm:

wbScript.Close (False)

because Script.vbs closes Script.xlsm with:

xlBook.Close(False)

Unless it's part of a bigger batch file, you don't need Script.bat; you can run Script.vbs directly.

 

[SliderJeff]

John,

Thanks again. I actually ADDED that line,

wbScript.Close (False)

hoping that it would solve my locked file issue, originally, but I guess it's not helping at all, so I'll remove it again as you suggest.

I was not aware a user could just double-click on a VBS file to run it, so I can remove the BAT file. I'm not sure that will impact the locked file problem, but at least it's one less file to deliver to my end users. Thanks again!

Regards,
Jeff

 

[SliderJeff]

I also had to remove the

xlApp.Quit

line from the VBS, since that was closing Excel and I need it to remain open. Unfortunately, none of this affects the locked file weirdness.

Thanks,
Jeff

 

[SliderJeff]

Also, FWIW, the file that is showing as locked by Excel is NOT on a network share and there is no visible "locked file" with the "~$" prefix located on my local hard drive folder housing the file in question. I'm just not sure what the heck Excel thinks is going on since I could swear that everything was closed properly.

Jeff