Require VBA code update to "release resources"

[skydivetom]

Hello:

I started using the sub routine "ExportValueMappingIntoExcel" (see below VBA). This function works GREAT!

However, since using the function, I observed that it takes a noticeably longer time when opening up the ACCDB file. Specifically, once the function has been executed and I need to close/re-open the database, it takes almost ~20 seconds to open up the ACCDB (vs. usually within 1-2 seconds).

My question:
- Based on the existing VBA code, is there any process that keeps on running (in the background) upon its execution and, thus, "eats up" some resources even when the ACCDB was closed?
- If so, how should the VBA be modified to release those resources once the XLSX has been created?

Option Explicit


Public Sub ExportValueMappingIntoExcel()


'-----------------------------------------------------------------------------------------------------------------------------------------
' Procedure : ExportValueMappingIntoExcel
' Author    : OnlineSource
' URL       : https://www.msaccesstips.com/2022/09/exporting-all-access-tables-into-excel.html
' Created   : 02/06/2024
' Modified  : 07/17/2024
' Purpose   : Export Access query into Excel spreadsheet.
'-----------------------------------------------------------------------------------------------------------------------------------------
    
    'Declare variables
    Dim db As Database
    Dim xlsFileLoc As String
    Dim xlsName As String
    Dim xlsPath As String
    Dim Tbl As TableDef
    Dim tblName As String
    Dim wrkBook As Excel.Workbook
    
    On Error GoTo Export2Excel_Err
    
    xlsFileLoc = CurrentProject.path & "\"
    xlsName = "DataExport.xlsx"
    
    xlsPath = xlsFileLoc & xlsName
        
    If Len(Dir(xlsPath)) > 0 Then
        Kill xlsPath
    End If
    
        Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
            
    Set db = CurrentDb
        
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", xlsPath, True
        
    On Error GoTo Export2Excel_Err
    
    MsgBox "Query successfully exported to folder/file:" & vbCr & vbCr & xlsPath, vbInformation, "Export Status"
       
       
    Set wrkBook = Nothing
    Set db = Nothing
    
Export2Excel_Exit:
    Exit Sub
    
Export2Excel_Err:
    MsgBox Err & " : " & Err.Description, , "Export2Excel()"
    Resume Export2Excel_Exit
    
End Sub

[ranman256]

why use the wrkBook part at all? Just use Transferspreadsheet, then open the excel file.

[skydivetom]

Thank you, ranman256.

I removed those 3 lines... seems like that did the "trick". ACCDB opens up within 1-2 secs again. Cheers.