can't close Excel

[Guest]

I've been trying to get Excel to close, and I've read the postings in here.
Here's my code:

Sub GetWorkSheetsName(strpath As String)
Dim XLwb As Object
Dim XLFile As String
Dim XLSheet As String
Dim xlsheet_range As String
Dim XLRange As String
Dim strstrAllSheets As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer
Set xlapp = CreateObject("Excel.Application")
Set XLwb = xlapp.workbooks.Open(strpath)
xlapp.Visible = False
SheetCount = xlapp.activeworkbook.sheets.Count
For z = 1 To SheetCount
DoCmd.SetWarnings 0
XLSheet = xlapp.activeworkbook.sheets(z).Name
xlsheet_range = XLSheet & "!"
If XLSheet <> "sheet1" Then
DoCmd.OpenQuery "delete_sheet1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "sheet1",
strpath, False, xlsheet_range
DoCmd.OpenQuery "delete_wire"
DoCmd.OpenQuery "add_to_wires"
Set bb = CurrentDb.OpenRecordset("wires")
DoCmd.RunMacro "pop_wire"
bb.Edit
bb!worksheet = XLSheet
bb!spreadsheet = strpath
bb.Update
DoCmd.OpenQuery "add_to_wire_archive"
End If
Next z
xlapp.activeworkbook.Save
XLwb.Close
xlapp.Quit
Set xlapp = Nothing
Set XLwb = Nothing
End Sub

What am I doing wrong? TIA....

 

[RoyVidar]

I've been trying to get Excel to close, and I've read the postings in here.
Here's my code:

Sub GetWorkSheetsName(strpath As String)
Dim XLwb As Object
Dim XLFile As String
Dim XLSheet As String
Dim xlsheet_range As String
Dim XLRange As String
Dim strstrAllSheets As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer
Set xlapp = CreateObject("Excel.Application")
Set XLwb = xlapp.workbooks.Open(strpath)
xlapp.Visible = False
SheetCount = xlapp.activeworkbook.sheets.Count
For z = 1 To SheetCount
DoCmd.SetWarnings 0
XLSheet = xlapp.activeworkbook.sheets(z).Name
xlsheet_range = XLSheet & "!"
If XLSheet <> "sheet1" Then
DoCmd.OpenQuery "delete_sheet1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "sheet1",
strpath, False, xlsheet_range
DoCmd.OpenQuery "delete_wire"
DoCmd.OpenQuery "add_to_wires"
Set bb = CurrentDb.OpenRecordset("wires")
DoCmd.RunMacro "pop_wire"
bb.Edit
bb!worksheet = XLSheet
bb!spreadsheet = strpath
bb.Update
DoCmd.OpenQuery "add_to_wire_archive"
End If
Next z
xlapp.activeworkbook.Save
XLwb.Close
xlapp.Quit
Set xlapp = Nothing
Set XLwb = Nothing
End Sub

What am I doing wrong? TIA....

I think the problem is that you are opening the same file through both
automation and through the transferthingie.

If you need to do/loop something per each existing sheet, I think
I'd try fetching the sheet names first (for instance to an array?),
then close Excel, release the variables etc.

Then, after Excel is closed, loop the array, and do the
transferthingie.

 

[Guest]

That did the trick. Thanks!

I think the problem is that you are opening the same file through both
automation and through the transferthingie.

If you need to do/loop something per each existing sheet, I think
I'd try fetching the sheet names first (for instance to an array?),
then close Excel, release the variables etc.

Then, after Excel is closed, loop the array, and do the
transferthingie.