[AccessD] Excel Not Closing
John Colby
jwcolby at gmail.com
Tue Apr 11 06:37:51 CDT 2023
Rocky, one thing to think about is to have a formatted spreadsheet already
saved. Any required pages, any headers, cells formatted with pretty
colors, cells already set with formulas etc. Then copy the format to a new
workbook, open it and just push the data. That's how I handled it when I
was automating excel.
On Mon, Apr 10, 2023 at 8:48 PM Rocky Smolin <rockysmolin2 at gmail.com> wrote:
> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
John W. Colby
Colby Consulting
More information about the AccessD
mailing list