open and close excel from access vba

[Gregorys05]

Hi all,
i have the below code that work great first time through, but if you go to run a second time it errors in various places each time.
If i debug and close excel manually and re run, it works fine again.
I believe excel is still running in the processes, is there a way to not only close the application but to all so kill the process.

Thanks

Set xlApp = CreateObject("Excel.Application")

xlApp.Workbooks.Open myFileName, True, False
xlApp.Application.ScreenUpdating = False
xlApp.Visible = True
With xlApp
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    End With
Range("D2:D" & LastRow).NumberFormat = "0"
For Each cell In Range("D2:D" & LastRow)
Test = cell.Value
If cell.Value = "" Then
cell.FormulaR1C1 = "=R[-1]C"
Else
End If
Next cell
End With
xlApp.Application.ScreenUpdating = True
xlApp.ActiveWorkbook.Close (True)
xlApp.Quit
Set xlApp = Nothing

 

[xenou]

Hi,

You haven't tied the excel objects to the instance you created (ranges are not dotted). When you do this, a new instance of Excel is "auto-instantiated", leaving you with two - the one you opened and the one that was opened for you.

See this link:
Why Excel has a problem when using code in access

It's a matter of preference I guess but I would never use "ActiveSheet" in any code that opens an external workbook. If there is only one sheet, you can use Worksheets(1), and otherwise you should use the sheet name since there's no guarantee what the active sheet will be.

Example of revised code with properly dotted objects:

Dim xlApp As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim LastRow As Long
Dim Test
Dim myFileName
Dim cell As Range

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Application.ScreenUpdating = False
    xlApp.Visible = True
   
    Set wb = xlApp.Workbooks.Open(myFileName, True, False)
    Set ws = wb.Worksheets("Sheet1") 'EDIT TO ACTUAL SHEET NAME HERE
   
    With ws
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
        .Range("D2:D" & LastRow).NumberFormat = "0"
        For Each cell In .Range("D2:D" & LastRow)
            Test = cell.Value
            If cell.Value = "" Then
                cell.FormulaR1C1 = "=R[-1]C"
            End If
        Next cell
    End With

    xlApp.Application.ScreenUpdating = True
    wb.Save
    wb.Close False
   
    xlApp.Quit
    Set xlApp = Nothing

End Sub

HTH,
ξ

 

[Gregorys05]

That works a charm thank you.

Web page link was very helpful.

Thanks

 

[rsMREX99]

I am trying to close a workbook as Gregorys05 above, but when I compile your code the statements:

Dim wb As Workbook
Dim ws As Worksheet
Dim cell As Range

Each generate the 'user defined type not defined' error
Are you defining 'Workbook', etc. somewhere else in your project? or?
I'm using Win 7, Access 2007
Thanks!

 

[xenou]

Hi,
You have to set a reference to Excel for the code to compile. This is called "early binding". See example here: Set References Excel VBA

In general, write and test with early binding, but convert to late binding when it's working properly (so it will continue to work even if someone upgrades their version of Office).

For late-bound code you would write:

Dim wb As Object
Dim ws As Object
Dim cell As Object

Late bound code is more robust in this case, since if you are referencing Excel 2007 and then sometime upgrades to Excel 2010, the reference would no longer work.