I'm not closing off Excel properly I think?

[stildawn]

Hi All



I have this code in Access, basically selects a Excel file, does some minor formatting then saves it for import into Access (separate code).

Sub FormattExcel()

SelectFile:
Set f = Application.FileDialog(1)
With f
    .AllowMultiSelect = False
    .Title = "Please select daily upload from NFE"
    .Show
    For i = 1 To .SelectedItems.Count
        filepath = .SelectedItems(i)
    Next i
End With


If filepath = vbNullString Then
    Answer = MsgBox("Do you want to select daily upload file again?", vbYesNo, "No Upload File Selected!")
    If Answer = vbYes Then
        GoTo SelectFile
    Else
        Exit Sub
    End If
Else
End If


Set objExcel = CreateObject("Excel.Application")


With objExcel
    .Workbooks.Open filepath
    file = Right(filepath, Len(filepath) - InStrRev(filepath, "\"))
    .Workbooks(file).Sheets(1).Range("G:H,K:K").Delete
    Lastrow = .Workbooks(file).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).NumberFormat = "dd/mm/yyyy"
    For Each rgCell In .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).Cells
        DateSplit = Split(Left(rgCell, 10), ".", 3)
        rgCell.Value = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
    Next rgCell
    
    For Each rgCell In .Workbooks(file).Sheets(1).Range("I2:I" & Lastrow).Cells
        If rgCell.Value = "X" Then
            rgCell.Value = -1
        Else
            rgCell.Value = 0
        End If
    Next rgCell
    
    .Application.DisplayAlerts = False
    .Workbooks(file).SaveAs "C:\Users\tbaker\Desktop\Merge\FB DB Saves\ImportTemp.xls", 56
    .Workbooks("ImportTemp.xls").Close False
    .Quit
End With


Set objExcel = Nothing


End Sub

This works, the issue is that after running, If I run again it errors out. I believe this is cause if I check Task Manager an instance of Excel is still running (even after closing or so I think). So I'm assuming that my code isn't closing off the instance of Excel correctly. I must be missing something?

Any ideas?

Thanks

[ItsMe]

Where is Sub FormattExcel(), is it in a Standard Module or a Form's Module?

Where do you declare objExcel and can you post that line here?

I am not an expert with interacting with Excel and I have stumbled across this before. I have had good success with closing out instances using Early Binding vs Late Binding.

[stildawn]

Its in a standard module, basically a form button directs to a procedure in this standard module called "TempUpload", this then steps through a bunch of procedures performing different things.

Here's the full code:

Option Compare Database

Sub TempUpload()


Call FormattExcel
Call DeleteTemp
Call RunImport
Call LogUsage


MsgBox "Daily Import has been successfully complete", vbInformation, "Import Complete"


End Sub


Sub FormattExcel()


SelectFile:
Set f = Application.FileDialog(1)
With f
    .AllowMultiSelect = False
    .Title = "Please select daily upload from NFE"
    .Show
    For i = 1 To .SelectedItems.Count
        filepath = .SelectedItems(i)
    Next i
End With


If filepath = vbNullString Then
    Answer = MsgBox("Do you want to select daily upload file again?", vbYesNo, "No Upload File Selected!")
    If Answer = vbYes Then
        GoTo SelectFile
    Else
        Exit Sub
    End If
Else
End If


Set objExcel = CreateObject("Excel.Application")


With objExcel
    .Workbooks.Open filepath
    file = Right(filepath, Len(filepath) - InStrRev(filepath, "\"))
    .Workbooks(file).Sheets(1).Range("G:H,K:K").Delete
    Lastrow = .Workbooks(file).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).NumberFormat = "dd/mm/yyyy"
    For Each rgCell In .Workbooks(file).Sheets(1).Range("F2:F" & Lastrow).Cells
        DateSplit = Split(Left(rgCell, 10), ".", 3)
        rgCell.Value = DateSerial(DateSplit(2), DateSplit(1), DateSplit(0))
    Next rgCell
    
    For Each rgCell In .Workbooks(file).Sheets(1).Range("I2:I" & Lastrow).Cells
        If rgCell.Value = "X" Then
            rgCell.Value = -1
        Else
            rgCell.Value = 0
        End If
    Next rgCell
    
    .Application.DisplayAlerts = False
    .Workbooks(file).SaveAs "C:\Users\tbaker\Desktop\Merge\FB DB Saves\ImportTemp.xls", 56
    .Workbooks("ImportTemp.xls").Close False
    .Quit
End With


Set objExcel = Nothing


End Sub


Sub DeleteTemp()


If ifTableExists("tbl_Import") Then
    DoCmd.DeleteObject acTable, "tbl_Import"
Else
End If


DoEvents


End Sub


Sub RunImport()


DoCmd.RunSavedImportExport "Import-ImportTemp"
CurrentDb.Execute "Update Query"
DoEvents
CurrentDb.Execute "Add Query"
DoEvents


End Sub


Sub LogUsage()


Set objFSO = CreateObject("Scripting.FileSystemObject")
outFile = "N:\National Share Drive\Excel Tools\VBA Tools\Counters\FB DB.txt"


Set objFile = objFSO.OpenTextFile(outFile, 8, True, 0)
objFile.WriteLine "1"
objFile.Close


End Sub


Public Function ifTableExists(tblName As String) As Boolean


ifTableExists = False
If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then
ifTableExists = True
End If


End Function

Um I havent declared objExcel, maybe it is as simple as doing that, I have only set it on this line:

Set objExcel = CreateObject("Excel.Application")

Might try that now

[stildawn]

Nope even adding in a Dim objExcel as Object

Its still in Task Mananger after running, and the next run will error out.

[stildawn]

Just tried:

Dim objExcel As Excel.ApplicationSet objExcel = New Excel.Application

No luck also.

It gets a run-time 462 error on the second run, I searched around the net and most of these errors are cause I'm referencing something that hasn't been referenced properly to the objExcel object. But I keep looking over my code and dont see any unquantified references.




Ohhhhhhhhhhhhhhhhhhhhhhhh wait. I just needed to put a dot in front of ("A" & Rows.Count), making it ("A" & .Rows.Count)

Problem solved after testing, thanks heaps for all your help again.

[pbaldy]

Thanks for posting your solution. More info for anybody searching later:

http://www.btabdevelopment.com/ts/excelinstance

[ItsMe]

Thanks for the link Paul!

Also, I like early binding so Intellisense is there for me.

[stildawn]

Yeah I was checking over looking for parts that the link talked about, just missed that one little dot in that line.