[AccessD] Excel Not Closing
Rocky Smolin
rockysmolin2 at gmail.com
Mon Apr 10 19:48:23 CDT 2023
Ok - Here it is.
TIA
Rocky
' Object variables for Automation stuff
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLWS As Excel.Worksheet
and the sub:
Private Sub cmdToExcel_Click()
strSQL = "SELECT DISTINCTROW tblProperty.fldPropertyName,
tblUser.fldUserInitials, tblWho.fldWho, tblWhat.fldWhat,
tblLocation.fldLocation, " _
& "tblTasks.fldTaskDescription, tblTasks.fldTasksMaintenance,
tblTasks.fldTasksAppraisal, tblTasks.fldTasksCustom, " _
& "qryTaskSubTaskInProcess.fldTaskSubTaskStepDescription,
qryTaskSubTaskInProcess.fldtaskSubTaskUrgencyText " _
& "FROM (tblTasks LEFT JOIN tblProperty ON tblTasks.fldPropertyID =
tblProperty.fldPropertyID) " _
& "LEFT JOIN ((((qryTaskSubTaskInProcess LEFT JOIN tblWho ON
qryTaskSubTaskInProcess.fldTaskSubTaskWhoID = tblWho.fldWhoID) " _
& "LEFT JOIN tblWhat ON qryTaskSubTaskInProcess.fldTaskSubTaskWhatID =
tblWhat.fldWhatID) " _
& "LEFT JOIN tblLocation ON
qryTaskSubTaskInProcess.fldTaskSubTaskLocationID =
tblLocation.fldLocationID) " _
& "LEFT JOIN tblUser ON
qryTaskSubTaskInProcess.fldTaskSubTaskStepPersonID = tblUser.fldUserID) ON
tblTasks.fldTaskID = qryTaskSubTaskInProcess.fldTaskID "
Set db = CurrentDb
Set rsTaskExport = db.OpenRecordset(strSQL)
strfilter = Me.Filter
strfilter = Replace(strfilter, "fldUserInitials", "tblUser.FldUserInitials")
If Nz(Me.Filter, "") <> "" Then strSQL = strSQL & " WHERE " & strfilter
If Nz(Me.OrderBy, "") <> "" Then strSQL = strSQL & " ORDER BY " & Me.OrderBy
strSQL = strSQL & ";"
Me.txtSQL = strSQL
strFileName = gstrBackEndPath & "\" & "Task_List_" & Format(Now, "MM-DD-YY
HH_MM_SS") & ".xlsx"
' Create Spreadsheet
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Add
Set objXLWS = objXLBook.Sheets(1)
intI = 1
' Headings
objXLWS.Cells(intI, 1) = "Entity"
objXLWS.Cells(intI, 2) = "IP"
objXLWS.Cells(intI, 3) = "Who"
objXLWS.Cells(intI, 4) = "What"
objXLWS.Cells(intI, 5) = "Where"
objXLWS.Cells(intI, 6) = "Task Description"
objXLWS.Cells(intI, 7) = "First In Process Sub Task Description"
objXLWS.Cells(intI, 8) = "First In Process Sub Task Urgency"
objXLWS.Cells(intI, 9) = "Maintenance"
objXLWS.Cells(intI, 10) = "Appraisal"
objXLWS.Cells(intI, 11) = "Custom"
intI = intI + 1
Do While rsTaskExport.EOF = False
Me.txtRowNumber = intI
intI = intI + 1
objXLWS.Cells(intI, 1) = rsTaskExport!fldPropertyName
objXLWS.Cells(intI, 2) = rsTaskExport!fldUserInitials
objXLWS.Cells(intI, 3) = rsTaskExport!fldWho
objXLWS.Cells(intI, 4) = rsTaskExport!fldWhat
objXLWS.Cells(intI, 5) = rsTaskExport!fldLocation
objXLWS.Cells(intI, 6) = rsTaskExport!fldTaskDescription
objXLWS.Cells(intI, 7) = rsTaskExport!fldTaskSubTaskStepDescription
objXLWS.Cells(intI, 8) = rsTaskExport!fldtaskSubTaskUrgencyText
objXLWS.Cells(intI, 9) = rsTaskExport!fldTasksMaintenance
objXLWS.Cells(intI, 10) = rsTaskExport!fldTasksAppraisal
objXLWS.Cells(intI, 11) = rsTaskExport!fldTasksCustom
rsTaskExport.MoveNext
Loop
objXLApp.Cells.Select
objXLApp.Cells.EntireColumn.AutoFit
With objXLApp.Selection.Font
.Name = "Calibri"
.Size = 12
End With
objXLApp.Rows("1:1").Select
objXLApp.Selection.Font.FontStyle = "Bold"
Set objXLWS = Nothing
objXLBook.SaveAs strFileName
objXLBook.Close True
Set objXLBook = Nothing
objXLApp.Quit
Set objXLApp = Nothing
MsgBox "All Displayed Tasks List Exported to: " & vbCrLf & vbCrLf &
strFileName
Application.FollowHyperlink strFileName
End Sub
On Mon, Apr 10, 2023 at 3:10 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:
> Yep, the whole block would be useful.
>
> A couple of snippets from 12 year old answers I posted to similar
> questions;
>
> .HPageBreaks.Add Before:=Cells(lRow + 1, 6)
> There's your unqualified reference. Change it to:
> .HPageBreaks.Add Before:=ExcelwBook.sheets(sSheet).Cells(lRow + 1, 6).
>
> and
>
> oxlCht.SetSourceData Source:=Sheets("Data").Range("B3:C28"), PlotBy:= _
> xlColumns
> Sheets need to be qualified. Off the top of my head, try:
> oxlCht.SetSourceData Source:=oxlWkb.Sheets("Data").Range("B3:C28"),
> PlotBy:= _
> xlColumns
>
>
>
> On 10 Apr 2023 at 14:59, Rocky Smolin wrote:
>
> > Stuart:
> >
> > Not sure what this means:
> >
> > <quote> When you write code to use an Excel object, method, or
> > > property, you should always precede the call with the appropriate
> > > object variable. If you do not, Visual Basic establishes its own
> > > reference to Excel. </quote>
> >
> > Am I not doing that?
> >
> > I could post the whole snip if that would help?
> >
> > Rocky
> >
> >
> >
> >
> > On Mon, Apr 10, 2023 at 1:59PM Stuart McLachlan
> > <stuart at lexacorp.com.pg> wrote:
> >
> > > Searching through my archives, it's a long time sionce this one came
> > > up, but it used to be fairly regular (the last time was in Jun
> > > 2014):
> > >
> > > Here's what I said in 2009:
> > >
> > > On 14 Nov 2009 at 8:29, Stuart McLachlan wrote:
> > >
> > > > Third time I've answered this on the list this year.
> > > >
> > > > See:
> > > > TrasnferSpreadsheet failing on 18 March 2009
> > > > and
> > > > Access to Excel Automation works first time on 13 July 2009:
> > > > in the archives.
> > > >
> > > > The old "unqualified reference" strikes again :-)
> > > > See http://support.microsoft.com/kb/319832
> > > >
> > > > <quote> When you write code to use an Excel object, method, or
> > > > property, you should always precede the call with the appropriate
> > > > object variable. If you do not, Visual Basic establishes its own
> > > > reference to Excel. </quote>
> > > >
> > > >
> > >
> > >
> > > On 10 Apr 2023 at 10:55, Rocky Smolin wrote:
> > >
> > > > Dear List:
> > > >
> > > > I have an app which exports data to an Excel spreadsheet. It is
> > > > not amenable to using TransferSpreadsheet because of formatting
> > > > problems, etc. So I use the brute force method and it works well.
> > > >
> > > > Problem is that at the end of the process the user wants the app
> > > > to open the spreadsheet.
> > > >
> > > > So I use Application.FollowHyperlink strFileName. Problem is that
> > > > the spreadsheet opens blank.
> > > >
> > > > When I go to the folder and open the spreadsheet it's there, all
> > > > perfect. So I put a breakpoint in at the statement
> > > > Application.FollowHyperlink strFileName and looked at the Task
> > > > Manager and sure enough there was an instance of Microsoft Excel
> > > > still there in the list of background processes.
> > > >
> > > > If I end that Excel process and then let the app execute the
> > > > Follow Hyperlink it opens just fine. So it's that leftover
> > > > instance of Excel that's hosing up the works.
> > > >
> > > > I set the objects like so:
> > > >
> > > > Set objXLApp = New Excel.Application
> > > > Set objXLBook = objXLApp.Workbooks.Add
> > > > Set objXLWS = objXLBook.Sheets(1)
> > > >
> > > > and at the end close up like so:
> > > >
> > > > objXLBook.SaveAs strFileName
> > > > objXLBook.Close True
> > > >
> > > > Set objXLWS = Nothing
> > > > Set objXLBook = Nothing
> > > >
> > > > objXLApp.Quit
> > > > Set objXLApp = Nothing
> > > >
> > > > How do I get rid of that instance of Excel in the background
> > > > processes?
> > > >
> > > >
> > > > MTIA
> > > >
> > > > Rocky
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
More information about the AccessD
mailing list