I am using VBA to work with excel files. The code allows the user to select files, processes them and gives the req data from input files in Excel workbook. After ptocessing, the excel file will be set visible for the user and the objects are also set to Nothing. The tool works well the first time when the user clicks the button. The output is displayed in MetaSumm.xls and set visible. But the next time i click the button even after closing the MetaSumm.xls, i get error 1004, Cannot access MetaSumm.xls. I find that excel.exe is running in the background.
Pls help me out asap

Private Sub cmdMeta_Click()
Dim str1 As String
Dim file_name As String
Dim i As Integer
ExcelMacro = CurrentDir & "\" & "import_macro.bas"

If (GetImportFiles) Then
    'On Error GoTo Disp

    file_name = CurDir & "\" & txtName.Text
    WorkbookName = file_name
    If WkbkIsOpen = True Then
    lblStatus.Caption = ImportFile & " is open. Pls Close The File"
    Exit Sub
    End If
    Set xlapp = CreateObject("Excel.Application")
    Set xlBook = xlapp.Workbooks.Add
    xlapp.Visible = False
    xlapp.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=file_name
     
    Set xlmodule = xlBook.VBProject.VBComponents.Import(ExcelMacro)

For i = 0 To List2.ListCount - 1
LogFilePath = List2.List(i)
CallMacro1
Next
     'xlImportapp.Quit
     xlapp.Visible = True
     xlapp.DisplayAlerts = True
    xlapp.WindowState = xlMaximized
End If

    On Error GoTo 0
     Set xlBook = Nothing
     Set xlapp = Nothing
     Set xlmodule = Nothing
     Exit Sub
     
Disp:
    lblStatus.FontBold = False
    lblStatus.Caption = "Cannot Proceed"
    MsgBox "Unexpected error" & _
        Str$(Err.Number) & _
        vbCrLf & _
        Err.Description
    On Error GoTo 0
    xlapp.Quit
     Set xlBook = Nothing
     Set xlapp = Nothing


End Sub

Private Function GetImportFiles() As Boolean
Dim entries, parts As Variant
Dim file_name As String
Dim i As Integer
dlgImportFile.FileName = ""
    lblStatus.Caption = ""
    On Error Resume Next
    dlgImportFile.Filter = "log files(*.all)|*.all|"
    dlgImportFile.ShowOpen
    If Err.Number = cdlCancel Then
    lblStatus.Caption = "Please Select Log Files"
    GetImportFiles = False
        Exit Function
    ElseIf Err.Number <> 0 Then
        MsgBox "Error " & Format$(Err.Number) & _
            " selecting files." & vbCrLf & Err.Description
            GetImportFiles = False
            Exit Function
    End If
    dlgImportFile.InitDir = CurDir
    List2.Clear
    entries = Split(dlgImportFile.FileName, vbNullChar)

    ' See if there is more than one file.
    If UBound(entries, 1) = LBound(entries, 1) Then
        ' There is only one file name.
        List2.AddItem entries(LBound(entries, 1))
    Else
        ' Get the directory name.
        dir_name = entries(LBound(entries, 1))
        If Right$(dir_name, 1) <> "\" Then dir_name = dir_name & "\"

        ' Get the file names.
        For i = LBound(entries, 1) + 1 To UBound(entries, 1)
            List2.AddItem dir_name & entries(i)
            'MsgBox List2.List(i - 1)
            'List2.AddItem entries(i)
        Next i
    End If
           GetImportFiles = True
End Function

Private Sub CallMacro1()
ExcelMacro = CurrentDir & "\" & "import_macro.bas"
lblAction.Caption = LogFilePath

lblStatus.Caption = ""
lblStatus.FontBold = True
If Right(LogFilePath, 4) Like ".all" Then
   'Accept the file and take to language form
    lblStatus.Caption = "Meta Summary is in progress..."
Else
   lblStatus.Caption = "Please Select a log file"
   lblAction.Caption = ""
   Exit Sub
   End If
   'Determining workbook name and path
   parts = Split(lblAction.Caption, ".")
   If Right(lblAction.Caption, 4) Like ".all" Then
       WorkbookName = parts(0) & "_import.xls"
       ImportFile = parts(0)
    Else
          WorkbookName = ""
    End If
   
    lblAction.Caption = WorkbookName
    
    ' to check if  _import.xls for the selected .all(log) file is already open

    parts = Split(WorkbookName, Application.PathSeparator)
    ImportFile = parts(UBound(parts))

App.OleRequestPendingTimeout = 999999   'to avoid component pending request

    On Error GoTo Disp
     xlapp.Run "SummaryData", LogFilePath, List2.ListCount
     
    lblStatus.Caption = "Meta Summary completed"
    On Error GoTo 0
     
     Exit Sub
Disp:
    lblStatus.FontBold = False
    lblStatus.Caption = "Unable To Proceed"
    MsgBox "Unexpected error" & _
        Str$(Err.Number) & _
        vbCrLf & _
        Err.Description
    On Error GoTo 0
    xlapp.Quit
     Set xlBook = Nothing
     Set xlapp = Nothing
End Sub
This article has been dead for over six months. Start a new discussion instead.