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