Excel not fully closing with VBA code

[coretex99]

Hello,

I have some simple code that runs on a button click in Access. It Opens Excel and inputs a couple of values of textboxes.

The issue I am having is that somehow Excel is not closing properly once the button is clicked, causing it to error thereforeafter until I go into Task manger and kill the Excel Task. Am I missing something clear? Thank

Private Sub Export_Click()
 
    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    
    Set xls = CreateObject("Excel.Application")
    Set wkb = xls.Workbooks.Open("c:\users\x\desktop\logger\log.csv")
    Set wks = wkb.Worksheets("log")

With xls
    .Visible = False

  ActiveWorkbook.Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)

    Set wks = Nothing
    Set wkb = Nothing
    Set xls = Nothing
  
    .ActiveWorkbook.Close True
    
End With

 

[Fluff]

Move the Close line above where you set the variables to Nothing.

 

[coretex99]

Move the Close line above where you set the variables to Nothing.

Still the same unfortunatly

 

[Fluff]

How about

With xls
    .Visible = False

  ActiveWorkbook.Sheets("log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
   wkb.Close True
   xls.Quit
    Set wks = Nothing
    Set wkb = Nothing
    Set xls = Nothing
  
    
End With

 

[coretex99]

Nope, Compile Error Expected Function Or Variable on the wkb.close D:

This is so frustrating for something that should be so easy!!

 

[Fluff]

Not sure why you are getting that, as it works for me.
I very rarely use Access, so don't know much about it.

 

[RoryA]

You're not properly qualifying the Excel objects, which is why you end up with orphaned processes running. Try this:

Private Sub Export_Click()
 
    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
    
    Set xls = CreateObject("Excel.Application")
    Set wkb = xls.Workbooks.Open("c:\users\x\desktop\logger\log.csv")
    Set wks = wkb.Worksheets("log")

    xls.Visible = False
    
    wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
    wbk.Close True

    Set wks = Nothing
    Set wkb = Nothing
    Set xls = Nothing

End Sub

 

[coretex99]

You're not properly qualifying the Excel objects, which is why you end up with orphaned processes running. Try this:

Private Sub Export_Click()

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
   
    Set xls = CreateObject("Excel.Application")
    Set wkb = xls.Workbooks.Open("c:\users\x\desktop\logger\log.csv")
    Set wks = wkb.Worksheets("log")

    xls.Visible = False
   
    wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Me.txtProduct.Value, Me.txtQuantity.Value, Me.txtCountUp.Value)
    wbk.Close True

    Set wks = Nothing
    Set wkb = Nothing
    Set xls = Nothing

End Sub

Thanks for this, interestingly enough this now works, and runs each time, however it is still creating a Microsoft Excel process in the background, each time I click it it creates a new one, but it now does not error, so that's progress haha.

Clicking the button 5 times = 5 seperate Excel processes running

 

[RoryA]

Oops - you're missing a line that looks like:

xls.quit

 

[coretex99]

Oops - you're missing a line that looks like:

xls.quit

I was just about to say, managed to figure that out myself, works perfectly, thank you very much my dude!