Excel

[hughess7]

Hi all
I have some code which uses excel templates to insert query data in and email to customers. I can't seem to quite get the code right for cleaning the excel operation afterwards. If the user already had a copy of excel running, I want to close these new worksheets only but leave any other sheets open. It either closes all of excel, or seems to close the wrong spreadsheet. Please can someone help me with this? I'm including the code below for the lines which I think you need to see, I haven't included the complete code...

Thanks

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
Else
blnEXCEL = False
End If
Err.Clear
On Error GoTo 0

Set db = CurrentDB()

If DCount("*", "Excel PosCode Report") <> 0 Then
Set rst = db.OpenRecordset("Excel PosCode Report")

xlx.Visible = False
Set xlw = xlx.Workbooks.Open("c:\Reports\Excel PosCode Report.xls")

Set xls = xlw.Worksheets("Excel PosCode Report")

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set xlc = Nothing
Set xls = Nothing
'xlx.Quit
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
db.Close
Set db = Nothing

 

[boblarson]

Okay, first of all, it would be beneficial to see the entire code including the declarations.

Second, your code here needs fixing

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set xlc = Nothing
Set xls = Nothing
'xlx.Quit
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
db.Close
Set db = Nothing

Should be:

rst.Close
 
Set rst = Nothing
Set qdf = Nothing
 
xlw.Close True ' close the EXCEL file and save the new data
[B][COLOR=seagreen]' exits Excel if it was not originally open but returns to the user[/COLOR][/B]
[B][COLOR=seagreen]' if it was open when we got it.[/COLOR][/B] 
If blnEXCEL = True Then 
    xlx.Quit
    Set xlx = Nothing
Else
    xlx.UserControl = True
    xlx.Visible = True
End If
 
db.Close
Set db = Nothing

Also, how have you declared and set the db variable? Normally if db = CurrentDb you do not "close" it nor do you set it to nothing. It doesn't need that as you are currently in the database. If it is an external database, then it would be okay.

 

[hughess7]

Hi Bob, thanks! That helped a little, I can now see the problem I think - it leaves open a copy of the position workbook which I open earlier in the code and don't close. I think I need to know the syntax to close this specific worksheet. More of the code below, thanks for your help witht this!

On Error GoTo EmailTransfer_Error_Handler

Dim dist, blRet As Boolean, Qtr As Boolean
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim stDocName As String
Dim txtReportName As String
Dim txtEmail As String
Dim strPath As String, strPath2 As String, strPath3 As String
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim db As DAO.Database, rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Dim fname As String, Msg, Response, POSReport As Boolean
Dim fname2 As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

dist = Me![EC_Common_Country]

Set objOutlook = CreateObject("Outlook.application")
Set objEmail =objOutlook.CreateItemFromTemplate("C:\MME\Reports\Reports_Q.oft")

' True / False - first row of the worksheet to be a header row (show / hide the names of the fields)
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
Else
blnEXCEL = False
End If
Err.Clear
On Error GoTo 0

Set db = CurrentDB()

If DCount("*", "Excel PosCode Report") <> 0 Then
Set rst = db.OpenRecordset("Excel PosCode Report")

' export data to Excel
' True / False - workbook to be visible / hidden when the code is running
xlx.Visible = False
' Actual path and filename of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("c:\Reports\Excel PosCode Report.xls")

' Replace WorksheetName with the actual name of the worksheet in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("Excel PosCode Report")

' the cell reference into which the first data value is to be written
Set xlc = xls.Range("A2") ' this is the first cell into which data go

Set qdf = db.QueryDefs("Excel PosCode Report")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.name)
Next prm

If rst.RecordCount > 0 Then
xlc.CopyFromRecordset rst
fname = "c:\Reports\" & dist & " Position Code (Medium Group) Analysis.xls"
xlw.SaveAs FileName:=fname
End If
POSReport = True
'xlx.ActiveWorkbook.Close
Else
POSReport = False
End If

Set rst = db.OpenRecordset("Excel DAR Report")

' Actual path and filename of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("c:\Reports\Excel DAR Report.xls")

' Replace WorksheetName with the actual name of the worksheet in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("Excel DAR Report")

' the cell reference into which the first data value is to be written
Set xlc = xls.Range("A2") ' this is the first cell into which data go

Set qdf = db.QueryDefs("Excel DAR Report")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.name)
Next prm

If rst.RecordCount > 0 Then
xlc.CopyFromRecordset rst
fname2 = "c:\Reports\" & dist & " Dealer Analysis Report CE.xls"
xlw.SaveAs FileName:=fname2
End If

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set xlc = Nothing
Set xls = Nothing
'xlx.Quit
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then
xlx.Quit
Set xlx = Nothing
Else
xlx.UserControl = True
xlx.Visible = True
End If
Set xlx = Nothing

Exit_Here:
Set objOutlook = Nothing
Exit Sub

EmailTransfer_Error_Handler:
MsgBox Err & ": " & Err.DESCRIPTION
Resume Exit_Here
End Sub