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

SliderJeff

Board Regular
Joined
Oct 5, 2016
Messages
63
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
Code:
@echo off
pushd %~dp0
rem Command Console Script - Feedback is in command window only
cscript script.vbs

script.vbs

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


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

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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,603
Try adding this immediately after the .Close in script.vbs:
Code:
xlApp.Quit
 

SliderJeff

Board Regular
Joined
Oct 5, 2016
Messages
63
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

Board Regular
Joined
Oct 5, 2016
Messages
63
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,603

ADVERTISEMENT

Try instead deleting this line in Script.xlsm:
Code:
wbScript.Close (False)
because Script.vbs closes Script.xlsm with:
Code:
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

Board Regular
Joined
Oct 5, 2016
Messages
63
John,

Thanks again. I actually ADDED that line,

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

Board Regular
Joined
Oct 5, 2016
Messages
63
I also had to remove the

Code:
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
 
Last edited:

SliderJeff

Board Regular
Joined
Oct 5, 2016
Messages
63
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top